计算在大型查询中某个事件“淎”之前和之后的事件数，直到遇到另一个事件“淎”。

计算在大型查询中某个事件“淎”之前和之后的事件数，直到遇到另一个事件“淎”。

以下是BigQuery标准SQL

#standardSQLWITH 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

您可以使用下面的示例中的虚拟数据来测试/播放上面的内容

#standardSQLWITH `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