Redshift SQL按最小日期筛选行及日期范围分组解决方案求助

问题背景 #

需要从包含30+列的大数据集中实现两个需求：

1. 提取每个account对应每日最小execution_datetime的全量行数据
2. 支持后续按指定日期范围分组，提取该范围内每个账户的最早交易记录

初始数据示例：

原SQL问题分析 #

原SQL返回所有记录的原因：

select omd.*
 from db1 as omd
 inner join (
              select id, min(execution_datetime)
              from db1
              group by execution_date,account,id
            ) as omd1 
                on omd.id = omd1.id; 

• 子查询按execution_date,account,id分组，但id（应为execution_id）是唯一交易标识，分组后每条记录都会被保留，关联后自然返回全量数据
• 未按account+execution_date的正确维度聚合寻找最小时间

解决方案一：提取每日最早交易记录 #

使用ROW_NUMBER()窗口函数，按account和execution_date分组，对execution_datetime升序排序，取排序值为1的记录（即当日最早交易）：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY account, execution_date 
            ORDER BY execution_datetime ASC
        ) AS rn
    FROM db1
    -- 如需筛选单日期，添加WHERE条件：
    -- WHERE execution_date = '2023-04-03'
) t
WHERE rn = 1;

返回结果：

解决方案二：提取指定日期范围内的最早交易记录 #

调整窗口函数的分组维度为account，同时通过WHERE限制日期范围，取该范围内每个账户的最早交易：

SELECT *
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY account 
            ORDER BY execution_datetime ASC
        ) AS rn
    FROM db1
    WHERE execution_date BETWEEN '2023-04-03' AND '2023-04-04'
) t
WHERE rn = 1;

返回结果：

方案优势 #

• 窗口函数无需关联全表，性能更优（尤其适合30+列的大数据集）
• 仅需调整PARTITION BY的分组维度和WHERE的日期条件，即可快速切换两种场景
• 自动保留全量列数据，无需手动指定所有字段

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