BigQuery:如何使用上下文相同的数据折叠某些行

下面是BigQuery标准SQL

#standardSQL
SELECT * EXCEPT(first) FROM (
  SELECT *,  
    state <> IFNULL(LAG(state) OVER(PARTITION BY id ORDER BY ts), state + 1) first
  FROM `project.dataset.table`
)
WHERE first

您可以使用问题中的虚拟数据来测试和玩上面的游戏(我添加了几行来演示与其他两个已经给出的答案的区别)

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, 1 state, TIMESTAMP('2018-08-16 01:25:00') ts UNION ALL
  SELECT 1, 1, '2018-08-16 02:15:00' UNION ALL
  SELECT 1, 3, '2018-08-16 02:16:00' UNION ALL
  SELECT 1, 1, '2018-08-16 02:17:00' UNION ALL
  SELECT 1, 3, '2018-08-16 03:11:00' UNION ALL
  SELECT 1, 3, '2018-08-16 03:14:12' 
)
SELECT * EXCEPT(first) FROM (
  SELECT *,  
    state <> IFNULL(LAG(state) OVER(PARTITION BY id ORDER BY ts), state + 1) first
  FROM `project.dataset.table`
)
WHERE first
-- ORDER BY id, ts

Row id  state   ts   
1   1   1       2018-08-16 01:25:00 UTC  
2   1   3       2018-08-16 02:16:00 UTC  
3   1   1       2018-08-16 02:17:00 UTC  
4   1   3       2018-08-16 03:11:00 UTC    

请注意:其他两个答案将给你以下(我真的怀疑是什么,你会期望)

Row id  state   ts   
1   1   1       2018-08-16 01:25:00 UTC  
2   1   3       2018-08-16 02:16:00 UTC  

一个简单的小组也应该这么做吗?

SELECT id, state, min(ts) ts
FROM %YOUR_TABLE_NAME%
GROUP BY id, state

   #standardSql 
   SELECT id, state, ts 
    FROM (
     SELECT *, RANK() OVER(PARTITION BY id, state ORDER BY ts ASC) rank 
     FROM %YOUR_TABLE_NAME%
     )
    WHERE rank=1

PARTITION BY id, state 意思是 id, state 是唯一的标识符和 ORDER BY ts ASC 意味着rank()函数将按时间戳(最早-更高)对行进行排序