DISTINCT WITH聚集和分析函数 [英] Distinct with Aggregation and Analytical functions

问题描述

我只是好奇，有没有办法在Postgres中做到这一点？

SUM(DISTINCT column_a) OVER(PARTITION BY column_b, column_c)

使用DISTINCT会导致错误：未为窗口函数实现DISTINCT

推荐答案

这应该会起作用：

SELECT column_a,
       column_b,
       column_c,
       sum(column_a) FILTER (WHERE is_new) OVER w
FROM (SELECT column_a,
             column_b,
             column_c,
             column_a IS DISTINCT FROM lag(column_a) OVER w AS is_new
      FROM atable
      WINDOW w AS (PARTITION BY column_b, column_c ORDER BY column_a)
     ) AS q
WINDOW w AS (PARTITION BY column_b, column_c ORDER BY column_a);

在内部查询中，column_a的所有重复项都将得到is_new = FALSE，因此这些重复项不会计入外部查询。

这篇关于DISTINCT WITH聚集和分析函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！