Search code examples
postgresqldistinctwindow-functions

Distinct with Aggregation and Analytical functions

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

Solution

  • 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.