以下是BigQuery SQL
#standardSQLSELECt 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
您可以在问题中使用虚拟数据进行测试
#standardSQLWITH `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
