Distinct with Aggregation and Analytical functions

Question

I'm just curious, is there a way to do this in Postgres?

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

Using DISTINCT causes an error: DISTINCT is not implemented for window functions

Similar: http://stackoverflow.com/a/27566842/330315 – Mar 17 '20 at 20:37 — , Mar 17 '20 at 20:37

score 0 · Accepted Answer · answered Mar 17 '20 at 21:36

This should do the trick:

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);

In the inner query, all duplicates of column_a will get is_new = FALSE, so these duplicates are not counted in the outer query.

WOW!, thanks for the help. Seems to be working as expected. – edavis Mar 18 '20 at 12:03 — edavis, Mar 18 '20 at 12:03

Distinct with Aggregation and Analytical functions

1 Answers1