在BigQuery中按Row_Number计算Created_at连续差值的技术求助

核心思路 #

针对相同id分组，基于Row_Number的顺序，使用窗口函数获取当前行的下一行Created_at值，再计算当前行与下一行的时间差值。

不同数据库实现示例 #

1. MySQL 8.0+ / PostgreSQL

如果你的表中已经存在row_number字段：

SELECT 
    id,
    row_number,
    created_at,
    -- 按需求计算当前行created_at减去下一行的差值
    created_at - LEAD(created_at) OVER (PARTITION BY id ORDER BY row_number) AS time_diff
FROM your_table;

如果需要动态生成row_number（按created_at升序排序）：

WITH ranked_data AS (
    SELECT 
        id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at) AS row_number
    FROM your_table
)
SELECT 
    id,
    row_number,
    created_at,
    created_at - LEAD(created_at) OVER (PARTITION BY id ORDER BY row_number) AS time_diff
FROM ranked_data;

注：PostgreSQL中若created_at为timestamp类型，差值会返回interval格式（如01:30:00表示1小时30分钟）；MySQL中日期时间类型直接相减会得到秒数差（取决于具体类型）。若需要绝对值，可包裹ABS()函数。

2. SQL Server

SQL Server推荐使用DATEDIFF()指定时间单位，更灵活可控：

SELECT 
    id,
    row_number,
    created_at,
    -- 计算当前行与下一行的秒数差，可替换为minute/hour/day等单位
    DATEDIFF(second, LEAD(created_at) OVER (PARTITION BY id ORDER BY row_number), created_at) AS time_diff_seconds
FROM your_table;

若需动态生成row_number：

WITH ranked_data AS (
    SELECT 
        id,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at) AS row_number
    FROM your_table
)
SELECT 
    id,
    row_number,
    created_at,
    DATEDIFF(second, LEAD(created_at) OVER (PARTITION BY id ORDER BY row_number), created_at) AS time_diff_seconds
FROM ranked_data;

内容的提问来源于stack exchange，提问作者Tạ Huy