如何在Presto(Trillo)数据库中筛选重叠记录的最新条目

要搞定这个问题，关键是先把同一维度（loc_id、prod_id、line_id）下的重叠时间区间归为一组，再在每组里保留upd_dt最新的记录。直接用rank()失效是因为没先处理重叠区间的分组逻辑，下面是可行的SQL方案：

实现思路 #

1. 按loc_id、prod_id、line_id分组，每个组内按start_dt排序，方便识别连续的重叠区间。
2. 用窗口函数生成重叠组ID：如果当前记录的start_dt <= 前一条记录的end_dt，说明属于同一重叠组；否则开启新组。
3. 在每个（维度组 + 重叠组ID）的分组中，筛选出upd_dt最大的记录。

完整SQL代码 #

WITH grouped_overlaps AS (
    SELECT 
        *,
        -- 生成重叠组ID：同一维度下，重叠区间共享同一个ID
        SUM(CASE WHEN start_dt <= LAG(end_dt) OVER (PARTITION BY loc_id, prod_id, line_id ORDER BY start_dt) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY loc_id, prod_id, line_id ORDER BY start_dt) AS overlap_group_id
    FROM your_table_name
),
latest_records AS (
    SELECT 
        *,
        -- 标记每组中upd_dt最新的记录
        ROW_NUMBER() OVER (PARTITION BY loc_id, prod_id, line_id, overlap_group_id ORDER BY upd_dt DESC) AS rn
    FROM grouped_overlaps
)
SELECT 
    loc_id, prod_id, line_id, qty, start_dt, end_dt, upd_dt
FROM latest_records
WHERE rn = 1;

代码说明 #

• grouped_overlaps：通过LAG(end_dt)获取前一条记录的结束时间，判断当前记录是否与前一条重叠。用SUM()累加生成组ID，不重叠时加1，生成新的组ID。
• latest_records：在每个重叠组内，按upd_dt倒序生成行号，最新的记录行号为1。
• 最终筛选行号为1的记录，就是每个重叠组的最新记录，以及无重叠的独立记录。

自定义调整 #

• 如果业务上认为“时间衔接（前一条end_dt等于后一条start_dt）”不算重叠，把start_dt <= LAG(end_dt)改成start_dt < LAG(end_dt)即可。
• 确保start_dt和end_dt是Presto支持的日期/时间类型（比如DATE、TIMESTAMP），避免比较错误。

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