SUM内窗函数不同

Question

我有一个类似的数据结构:

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

这是我目前得到的查询

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

这是我想要达到的结果:

count_stuff_EXPECTEDtbody> <<tr>3838383838

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

也许这不是最好的答案，但我认为它达到了你的目标。

首先，我用自己的表数据做一个INNER JOIN，以获得每一行的值将是总和(每个their_id的新记录)。然后我用GROUP BY来丢弃"重复的";行，最后得到和

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;