我有一个包含日期，事件和用户的表.有一个名为"A"的事件.我想找出一个特定事件在SQL Bigquery中的事件"A"之前和之后发生了多少次.事件A可能会出现多次.但是它应该只对事件进行计数，直到在条件之前和之后都遇到另一个事件A为止. 例如
I have a table containing date and events and users. There is event named 'A'. I want to find out how many time a particular event occurred before and after event 'A' in SQL Bigquery. The event A might appear multiple times. But it should count events only till it encounters another event A in both before and after condition. for Example,
User Date Events 123 2018-02-14 X.Y.A 123 2018-02-12 X.Y.B 134 2018-02-10 Y.Z.A 123 2018-02-11 A 123 2018-02-01 X.Y.Z 134 2018-02-05 X.Y.B 134 2018-02-04 A 123 2018-02-13 A输出将是这样的.
User Event Before After 123 A 1 1 123 A 0 1 134 A 0 1其他条件保持不变.
这个问题是我上一个问题的扩展.
This question is an extension of my previous question.
请参见如何在SQL Bigquery中计算特定事件在另一个事件之前的数目?以获取详细信息.
See How to count number of a particular event before another event in SQL Bigquery? for details.
我必须计算的事件包含一个特定的前缀.意味着我必须检查以(X.Y.然后是一些事件名称)开头的事件.因此，X.Y.SomeEvent是我必须为其设置计数器的事件.有什么建议吗?
The event that I have to count contains a particular prefix. Means I have to check events that start with ( X.Y.then some event name). So, X.Y.SomeEvent are the events that I have to set the counter for. Any Suggestions?
推荐答案以下是BigQuery标准SQL
Below is for BigQuery Standard SQL
#standardSQL WITH grps AS ( SELECT user, dt, event, COUNTIF(event = 'A') OVER(PARTITION BY user ORDER BY dt) grp FROM `project.dataset.events` ) SELECT dt, user, event, before, after FROM ( SELECT dt, user, event, COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY grp RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING ) before, COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY grp RANGE BETWEEN CURRENT ROW AND CURRENT ROW) after FROM grps ) WHERE event = 'A' -- ORDER BY user您可以使用下面示例中的虚拟数据来进行测试/播放
you can test/play with above using dummy data from your example like below
#standardSQL WITH `project.dataset.events` AS ( SELECT 123 user, '2018-02-14' dt, 'X.Y.A' event UNION ALL SELECT 123, '2018-02-13', 'A' UNION ALL SELECT 123, '2018-02-12', 'X.Y.B' UNION ALL SELECT 123, '2018-02-11', 'A' UNION ALL SELECT 123, '2018-02-01', 'X.Y.Z' UNION ALL SELECT 134, '2018-02-10', 'Y.Z.A' UNION ALL SELECT 134, '2018-02-05', 'X.Y.B' UNION ALL SELECT 134, '2018-02-04', 'A' ), grps AS ( SELECT user, dt, event, COUNTIF(event = 'A') OVER(PARTITION BY user ORDER BY dt) grp FROM `project.dataset.events` ) SELECT dt, user, event, before, after FROM ( SELECT dt, user, event, COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY grp RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING ) before, COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY grp RANGE BETWEEN CURRENT ROW AND CURRENT ROW) after FROM grps ) WHERE event = 'A' ORDER BY user结果为
Row dt user event before after 1 2018-02-11 123 A 1 1 2 2018-02-13 123 A 1 1 3 2018-02-04 134 A 0 1更多推荐
计算事件"A"之前和之后的事件数量.直到另一个事件"A"大查询中遇到的问题?
发布评论