Presto统计布尔列状态转换次数及按ID获取首末次值的查询方法

前置说明 #

• 假设原始表名为 event_log，包含三列：Id（分组ID字段）、timestamp（可排序的时间戳字段）、fact（布尔类型值字段）
• 用到Presto原生特性：min_by/max_by 聚合函数、lag() 窗口函数、条件聚合逻辑

完整查询代码 #

WITH event_with_prev AS (
    SELECT
        Id,
        timestamp,
        fact,
        -- 按ID分组、时间升序排序，取当前行的上一行fact值
        LAG(fact) OVER (PARTITION BY Id ORDER BY timestamp ASC) AS prev_fact
    FROM event_log
)
SELECT
    Id,
    -- 首个fact值及对应时间
    MIN_BY(fact, timestamp) AS first_fact,
    MIN(timestamp) AS first_fact_time,
    -- 末次fact值及对应时间
    MAX_BY(fact, timestamp) AS last_fact,
    MAX(timestamp) AS last_fact_time,
    -- 统计True/False切换次数
    SUM(CASE WHEN prev_fact IS NOT NULL AND prev_fact != fact THEN 1 ELSE 0 END) AS fact_switch_count,
    -- 统计总记录数
    COUNT(*) AS total_record_count
FROM event_with_prev
GROUP BY Id
ORDER BY Id;

逻辑说明 #

• 首尾取值：min_by(fact, timestamp) 会返回同一ID下最小时间戳对应的fact值，也就是最早的记录值；max_by同理返回最晚时间对应的fact值，比嵌套窗口函数的写法更简洁高效
• 切换次数统计：先用LAG()窗口函数关联同ID下前一条记录的fact值，再通过条件聚合统计前后值不同的次数，自动忽略首条无前值的记录，不会产生误算
• 总记录数直接按ID分组计数即可

样例验证 #

输入样例：

Id	timestamp	fact
1	2023-01-01 00:00:00	true
1	2023-01-01 00:01:00	true
1	2023-01-01 00:02:00	false
1	2023-01-01 00:03:00	true

输出结果：

Id	first_fact	first_fact_time	last_fact	last_fact_time	fact_switch_count	total_record_count
1	true	2023-01-01 00:00:00	true	2023-01-01 00:03:00	2	4

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