postgresql - 按组使用 case 语句运行总和

Question

我想计算运行总和，但我的代码不起作用，有些想法为什么？

select id, color, time,  (sum(case when question = 1 then 1 else 0 end) OVER (PARTITION BY id ORDER BY time rows) + 
 sum(case when suggestion='True' then 1 else 0 end)  OVER (PARTITION BY id ORDER BY time) + 
sum(case when proposal= 'True' then 1 else 0 end)  OVER (PARTITION BY id ORDER BY time)) as s
from table 

这是示例数据

id       color   time   question    suggestion    proposal 
1         pink    14:00    0          True         False
1         red     15:00    0          False        False
1         blue    13:00    0           False       False
2         green   11:00    0          True         False
2         orange  15:00    1           False       False

结果:

id       color   time      s
1         pink    14:00    1         
1         red     15:00    1         
1         blue    13:00    0           
2         green   11:00    1          
2         orange  15:00    2        

Answer 1

您需要使用 SUM()如果将所有 boolean 表达式转换为整数，则窗口函数仅一次:

SELECT id, color, time,
       SUM((question = 1)::int + suggestion::int + proposal::int) OVER (PARTITION BY id ORDER BY time) s
FROM tablename
ORDER BY id, time;

见 demo .