问题描述
对不起,不好的话题……我需要计算运行总计,但需要在某种条件下重置总计(当预期达到= 0时).我有这张桌子:
Sorry bad topic…I need to calculate a running total but need to reset the total on a condition (when expected reached = 0).I have this table:
Date, Registrations, Expected Registrations, Expected reached
2020-03-01, 5, 4,1
2020-03-02, 7, 5,1
2020-03-03, 8, 6,1
2020-03-04, 2, 5,0
2020-03-05, 5, 4,1
2020-03-06, 7, 5,1
2020-03-07, 8, 6,1
2020-03-08, 2, 5,0
具有累计总数的预期结果-条件是,当预期达到"<>时,应计算0累计总数.如果预期达到" = 0,则运行总计应从0开始:
Expected result with running total - the condition is that while "Expected Reached" <> 0 running total should be calculated. If "Expected Reached" = 0 the running total should start over from 0:
Date, Registrations, Expected Registrations, Expected Reached, Running Total
2020-03-01, 5, 4,1, 1
2020-03-02, 7, 5,1, 2
2020-03-03, 8, 6,1, 3
2020-03-04, 2, 5,0, 0
2020-03-05, 5, 4,1, 1
2020-03-06, 7, 5,1, 2
2020-03-07, 8, 6,1, 3
2020-03-08, 2, 5,0, 0
我不知道如何对窗口函数进行分区以实现此目的.也许我必须在之前创建中间计算,但不确定.有什么建议吗?
I don't know how to partition my window function to do this. Maybe I have to create an intermediate calculation before but Im unsure. Any suggestions?
edit2:删除了我的即时问题".
edit2: removed my "on the fly question".
推荐答案
以下是BigQuery标准SQL
Below is for BigQuery Standard SQL
#standardSQL
SELECT * EXCEPT(grp),
SUM(Expected_reached) OVER(PARTITION BY grp ORDER BY `date`) Running_Total
FROM (
SELECT *, COUNTIF(Expected_reached = 0) OVER(ORDER BY `date`) grp
FROM `project.dataset.table`
)
这篇关于使用BigQuery中的条件计算运行总计的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!