如何在BigQuery中计算同一标识符对应的起止时间差？

针对你的需求，以下两种SQL写法分别实现原表新增时间差列和扁平化单行展示的效果，直接替换表名即可使用：

1. 原表形式（新增ElapsedTime列） #

这种写法会保留原筛选后的所有记录，同时为每条记录添加对应的时间差：

WITH filtered_data AS (
  -- 筛选出Start和Finished状态的记录，替换成你的实际表名
  SELECT 
    Row,
    MyDate,
    StateTransition,
    MyIdentifier
  FROM your_table_name
  WHERE StateTransition IN ('Start', 'Finished')
)
SELECT
  *,
  -- 按MyIdentifier分组，分别取出Finished和Start时间，计算天数差
  TIMESTAMP_DIFF(
    MAX(CASE WHEN StateTransition = 'Finished' THEN MyDate END) OVER (PARTITION BY MyIdentifier),
    MAX(CASE WHEN StateTransition = 'Start' THEN MyDate END) OVER (PARTITION BY MyIdentifier),
    DAY
  ) AS ElapsedTime
FROM filtered_data
ORDER BY MyIdentifier, MyDate;

2. 扁平化单行形式 #

这种写法将同一个MyIdentifier的Start/Finished记录合并为一行，直接展示时间差：

WITH filtered_data AS (
  SELECT 
    MyDate,
    StateTransition,
    MyIdentifier
  FROM your_table_name
  WHERE StateTransition IN ('Start', 'Finished')
)
SELECT
  MyIdentifier,
  MAX(CASE WHEN StateTransition = 'Start' THEN MyDate END) AS StartTime,
  MAX(CASE WHEN StateTransition = 'Finished' THEN MyDate END) AS FinishedTime,
  TIMESTAMP_DIFF(
    MAX(CASE WHEN StateTransition = 'Finished' THEN MyDate END),
    MAX(CASE WHEN StateTransition = 'Start' THEN MyDate END),
    DAY
  ) AS ElapsedDays
FROM filtered_data
GROUP BY MyIdentifier;

说明 #

• 替换your_table_name为你的实际数据表名称
• TIMESTAMP_DIFF的第三个参数DAY可按需替换为HOUR/MINUTE等单位，获取不同粒度的时间差
• 确保每个MyIdentifier确实只有一条Start和一条Finished记录，否则MAX函数会取最新的对应状态时间

内容的提问来源于stack exchange，提问作者Evan R