如何在SQL Bigquery中在另一个事件之前计算一个特定事件的数目？

一尘不染

如何在SQL Bigquery中在另一个事件之前计算一个特定事件的数目？

sql

我有一个包含日期，事件和用户的表。有一个名为“ A”的事件。我想找出特定事件在Sql Bigquery中的事件“ A”之前和之后发生了多少次。例如，

   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

输出将是这样的

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

我必须计算的事件包含一个特定的前缀。意味着我必须检查以（XYthen一些事件名称）开头的事件。因此，XYSomeEvent是我必须为其设置计数器的事件。有什么建议？

阅读 173

2021-05-16

共1个答案

一尘不染

以下是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

您可以在问题中使用虚拟数据进行测试

#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

2021-05-16