PrestoSQL实现追踪列值变化并取对应最小timestamp的最优查询

原始数据表 #

期望查询结果 #

需求说明 #

需追踪column 1的数值变化，提取每个连续相同值分组对应的最小timestamp。由于数据量庞大，要求使用尽量少用CTE/子查询的PrestoSQL高效方案。

PrestoSQL查询方案 #

SELECT
  column_1,
  MIN(timestamp) AS timestamp
FROM (
  SELECT
    column_1,
    timestamp,
    SUM(CASE WHEN column_1 = LAG(column_1) OVER (ORDER BY timestamp) THEN 0 ELSE 1 END)
      OVER (ORDER BY timestamp) AS group_id
  FROM your_table
) t
GROUP BY column_1, group_id
ORDER BY MIN(timestamp);

方案说明 #

1. 分组标记：通过LAG(column_1) OVER (ORDER BY timestamp)获取当前行的上一行column_1值，用CASE语句判断是否发生值变化，再通过SUM() OVER()累加生成连续相同值的分组ID。
2. 聚合取最小时间戳：按column_1和分组ID聚合，提取每组的最小timestamp，最后按时间戳排序保证结果顺序正确。

该方案仅使用一层子查询，依赖Presto对窗口函数的高效优化，适合处理大规模数据集。

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