如何在SQL BigQuery中的另一个事件之前统计特定事件的数量？ [英] How to count number of a particular event before another event in SQL Bigquery?

问题描述

我有一个包含日期和事件以及用户的表格。有一个名为'A'的事件。我想知道在Sql BigQuery中事件'A'之前和之后发生了多少次特定事件。例如，用户日期事件
123 2018-02-13 XYA
123 2018-02- 12 XYB
2018-02-10 YZA
123 2018-02-11 A
123 2018-02-01 XYZ
134 2018-02-05 XYB
134 2018-02-04 A

输出结果如下所示

 之前的用户事件
 123 A 1 3 $ b $ 134 A 0 1 
  

我必须计算的事件包含特定的前缀。意味着我必须检查以（X.Y.然后某个事件名称）开头的事件。所以，X.Y.SomeEvent是我必须反击的事件。任何建议？

解决方案

下面是针对BigQuery的SQL

  #standardSQL 
 SELECT用户，事件，之前，之后，之后，$ b $ FROM（
 SELECT用户，事件，
 COUNTIF（事件LIKE'XY％'）OVER 
 COUNTIF（事件LIKE'XY％'）OVER（用户按ORDER BY dt行在1跟随和无约束跟随之后）之前的分隔符（用户按ORDER BY dt行在无约束先行和1 PRECEDING之间） b FROM`project.dataset.events` 
）
 WHERE event ='A'
  -  ORDER BY用户
  

您可以在您的问题中使用虚拟数据测试它

  #standardSQL 
 WITH`project.dataset.events` AS（
 SELECT 123用户，'2018-02-13'dt，'XYA'事件UNION ALL 
 SELECT 123，'2018-02-12' ，'XYB'UNION ALL 
 SELECT 123，'2018-02-11'，'A'UNION ALL 
 SELECT 134，'2018-02-10'，'YZA'UNION ALL 
 SEL ECT 134，'2018-02-05'，'XYB'UNION ALL 
 SELECT 134，'2018-02-04'，'A'UNION ALL 
 SELECT 123，'2018-02-01 '，'XYZ'
）
选择用户，事件，之前和之后，
 FROM（
 SELECT用户，事件，
 COUNTIF（事件LIKE'XY％' ）之后
 COUNTIF（事件LIKE'XY％'）OVER（用户按ORDER BY dtROWS在1跟随和无约束跟随之间进行划分）之后的OVER（PARTITION BY用户ORDER BY dt行在无约束先行和1 PRECEDING之间） b $ b FROM`project.dataset.events` 
）
 WHERE event ='A'
 ORDER BY用户
  

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. for Example,

   User           Date             Events
    123          2018-02-13            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

The Output would be something like this

User       Event    Before   After
123          A      1        3
134          A      0        1

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 counter for. Any Suggestions?

解决方案

below is for BigQuery SQL

#standardSQL
SELECT user, event, before, after 
FROM (
  SELECT user, event, 
    COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) before,
    COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) after
  FROM `project.dataset.events`
)
WHERE event = 'A'
-- ORDER BY user

you can test it with dummy data in your question

#standardSQL
WITH `project.dataset.events` AS (
  SELECT 123 user, '2018-02-13' dt, 'X.Y.A' event UNION ALL
  SELECT 123, '2018-02-12', 'X.Y.B' UNION ALL
  SELECT 123, '2018-02-11', 'A' 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' UNION ALL
  SELECT 123, '2018-02-01', 'X.Y.Z' 
)
SELECT user, event, before, after 
FROM (
  SELECT user, event, 
    COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) before,
    COUNTIF(event LIKE 'X.Y.%') OVER(PARTITION BY user ORDER BY dt ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) after
  FROM `project.dataset.events`
)
WHERE event = 'A'
ORDER BY user

这篇关于如何在SQL BigQuery中的另一个事件之前统计特定事件的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！