本文介绍了计数在SQL中特定事件之前和之后的事件数量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含日期和事件的表.有一个名为"A"的事件.我想找出在Sql Bigquery中事件"A"之前和之后发生了多少事件.例如
I have a table containing date and events. There is event named 'A'. I want to find out how many events occurred before and after event 'A' in Sql Bigquery. for Example,
User Date Events 123 2018-02-13 D 123 2018-02-12 B 123 2018-02-10 C 123 2018-02-11 A 123 2018-02-01 X答案会是这样的.
User Event Before After 123 A 2 2我尝试了很多查询,但是没有用.任何想法,如何解决这个问题?
I have tried many queries but its not working. Any Idea, how to solve this problem?
推荐答案以下是针对BigQuery标准SQL的
below is for BigQuery Standard SQL
#standardSQL WITH `project.dataset.events` AS ( SELECT 123 user, '2018-02-13' dt, 'D' event UNION ALL SELECT 123, '2018-02-12', 'B' UNION ALL SELECT 123, '2018-02-11', 'A' UNION ALL SELECT 123, '2018-02-10', 'C' UNION ALL SELECT 123, '2018-02-01', 'X' ) SELECT user, event, before, after FROM ( SELECT user, event, COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) before, COUNT(1) OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) after FROM `project.dataset.events` ) WHERE event = 'A'更多推荐
计数在SQL中特定事件之前和之后的事件数量?
发布评论