时间戳

首页 > 解决方案 > 运行所有先前行 BigQuery 的总计

sql - 运行所有先前行 BigQuery 的总计

问题描述

我有一个 BigQuery 表，如下所示：

ID  SessionNumber  CountOfAction   Category
 1       1              1            B
 1       2              3            A
 1       3              1            A
 1       4              4            B
 1       5              5            B

我正在尝试获取 CountofAction 的所有先前行的总和，其中 category = A。最终输出应该是

 ID  SessionNumber  CountOfAction
 1       1              0   --no previous rows have countofAction for category = A
 1       2              0   --no previous rows have countofAction for category = A
 1       3              3   --previous row (Row 2) has countofAction = 3 for category = A
 1       4              4   --previous rows (Row 2 and 3) have countofAction = 3 and 1 for category = A
 1       5              4   --previous rows (Row 2 and 3) have countofAction = 3 and 1 for category = A

下面是我写的查询，但它没有给我想要的输出

 select 
 ID,
 SessionNumber ,
 SUM(CountofAction)  OVER(Partition by clieIDntid ORDER BY SessionNumber ROWS BETWEEN UNBOUNDED 
 PRECEDING AND 1 PRECEDING)as CumulativeCountofAction
 From TAble1 where category = 'A'

我真的很感激这方面的任何帮助！提前致谢

标签： sqlgroup-bygoogle-bigquerysumwindow-functions

解决方案

category在where子句中过滤会驱逐不出现 ' 的(id, sessionNumber)元组，这不是您想要的。category 'A

相反，您可以使用聚合和条件sum()：

select
    id,
    sessionNumber,
    sum(sum(if(category = 'A', countOfAction, 0))) over(
        partition by id 
        order by sessionNumber
        rows between unbounded preceding and 1 preceding
    ) CumulativeCountofAction
from mytable t
group by id, sessionNumber
order by id, sessionNumber

推荐阅读