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