在BigQuery中按组和标志运行计数

Question

我有一个表，如下所示：

Row | Fullvisitorid | Visitid | New_Session_Flag 
1   | A             | 111     | 1
2   | A             | 120     | 0
3   | A             | 128     | 0
4   | A             | 133     | 0
5   | A             | 745     | 1
6   | A             | 777     | 0
7   | B             | 388     | 1
8   | B             | 401     | 0
9   | B             | 420     | 0
10  | B             | 777     | 1
11  | B             | 784     | 0
12  | B             | 791     | 0
13  | B             | 900     | 1  
14  | B             | 904     | 0  

我想做的是，如果它是fullvisitorid的第一行，那么将字段标记为1，否则使用上面的行作为值，但如果new_session_flag=1，那么使用上面的一行加1，我在下面寻找的输出示例：

Row | Fullvisitorid | Visitid | New_Session_Flag | Rank_Session_Order
1   | A             | 111     | 1                | 1
2   | A             | 120     | 0                | 1
3   | A             | 128     | 0                | 1
4   | A             | 133     | 0                | 1
5   | A             | 745     | 1                | 2
6   | A             | 777     | 0                | 2
7   | B             | 388     | 1                | 1
8   | B             | 401     | 0                | 1
9   | B             | 420     | 0                | 1
10  | B             | 777     | 1                | 2
11  | B             | 784     | 0                | 2
12  | B             | 791     | 0                | 2
13  | B             | 900     | 1                | 3
14  | B             | 904     | 0                | 3

正如你所看到的：

• 第1行是1，因为这是第一次出现完整的A

• 第2行是1，因为这不是第一次出现fullvisitorid A并且new_session_flag<gt；1，因此它使用上面的行(即1(

• 第5行是2，因为这不是第一次出现完全访问ID A，并且new_session_Flag=1，因此它使用上面的行(即1(加1

• 第7行是1，因为这是第一次完整的B出现

  等等。

我相信这可以通过SAS中的retain语句来实现，但在Google BigQquery中有等效语句吗？

希望以上内容有意义，如果没有，请告诉我。

提前感谢

Answer 1

下面是BigQuery标准SQL

#standardSQL
SELECT *,
COUNTIF(New_Session_Flag = 1) OVER(PARTITION BY Fullvisitorid  ORDER BY Visitid) Rank_Session_Order
FROM `project.dataset.table`

Answer 2

Mikhail Berlyant使用条件窗口计数得出的答案是正确且有效的。我之所以回答，是因为我发现窗口求和更简单(在大型数据集上可能更高效(：

select
t.*,
sum(new_session_flag) over(partition by fullvisitorid order by visid_id) rank_session_order
from mytable t

这是因为new_session_flag仅包含0s和1s；因此对CCD_ 5s进行计数实际上相当于对所有值求和。