SUM distinct inside window function

Question

I have a data structure similar to this:

CREATE TABLE some_table (
    dude_id INTEGER, 
    main_date TIMESTAMP, 
    how_many INTEGER, 
    how_much NUMERIC(5,2), 
    their_ids INTEGER[]
)

This is the query I've got so far

SELECT
    dude_id,
    main_date,
    how_many,
    how_much,
    their_ids,
    SUM(how_many) OVER (PARTITION BY dude_id ORDER BY main_date) AS count_stuff_WRONG,
    SUM(how_much) OVER (PARTITION BY dude_id ORDER BY main_date) AS cumulative_sum_WRONG
FROM some_table

This is the result I'm trying to achieve:

dude_id	main_date	how_many	how_much	their_ids	count_stuff_EXPECTED	cumulative_sum_EXPECTED	count_stuff_WRONG	cumulative_sum_WRONG
38	2019-06-14	1	6	373	1	6	1	6
38	2019-07-15	1	7	374	2	13 (6+7)	2	13 (6+7)
38	2019-07-16	1	8	375	3	21 (6+7+8)	3	21 (6+7+8)
38	2020-06-14	1	16	373	3	31 (7+8+16)	4	37 (6+7+8+16)
38	2020-07-15	1	17	374	3	41 (8+16+17)	5	54 (6+7+8+16+17)
38	2020-07-16	1	18	375	3	51 (16+17+18)	6	72 (6+7+8+16+17+18)

Columns count_stuff_EXPECTED and cumulative_sum_EXPECTED are what I'm trying to get, columns count_stuff_WRONG and cumulative_sum_WRONG are the ones my current query is returning.

In other words, I want to get cumulative values for each main_date but without counting/summing multiple times the same their_ids. So on row 4, for example, the window partition has their_ids {373} more than once, so it should be considered only the most recent one (row 4) and not consider the first occurrence (row 1)

NOTE: there's no need to show on the query how the sum was calculated, I just put it in there in parentheses for clarity.

I tried using

SUM(DISTINCT how_many) over (PARTITION BY dude_id ORDER BY main_date) as count_stuff

but got

ERROR: DISTINCT is not implemented for window functions

SQL Fiddle: http://sqlfiddle.com/#!17/44850/2

user_15424227 · Answer

Maybe it is not the best answer, but I think it get your goal.

First I make an INNER JOIN of table data with itself to get witch values will be sum for each row (newer record for each their_ids). Then I GROUP BY to discard "duplicated" rows, I finally I get the sum.

WITH sq1 AS (SELECT d1.dude_id, d1.main_date, d1.how_many, d1.how_much, d1.their_ids,
                    d2.dude_id AS d2_dude_id, d2.main_date AS d2_main_date, d2.their_ids AS d2_their_ids,
                   FIRST_VALUE(d2.how_much) OVER (PARTITION BY d1.dude_id, d1.main_date, d1.their_ids, d2.their_ids ORDER BY d2.main_date DESC) AS how_much_to_sum
             FROM data d1
             INNER JOIN data d2 ON d1.dude_id = d2.dude_id AND d1.main_date >= d2.main_date),
            
     sq2 AS (SELECT dude_id, main_date, how_many, how_much, their_ids, d2_dude_id, d2_their_ids, MIN(how_much_to_sum) AS how_much_to_sum
             FROM sq1
             GROUP BY dude_id, main_date, how_many, how_much, their_ids, d2_dude_id, d2_their_ids)
             
select
      dude_id,
      main_date,
      how_many,
      how_much,
      their_ids,
      SUM(how_many) AS count_stuff,
      SUM(how_much_to_sum) AS cumulative_sum
      
from sq2
GROUP BY dude_id, main_date, how_many, how_much, their_ids
ORDER BY main_date;

SUM distinct inside window function

1 Answers