如何合并两日期列并按人员聚合获取指定区间最早购车日期

问题背景 #

现有car_table表，包含person、color、date1、date2、aux、car_type字段，每条记录的date1和date2仅有一个非空（代表该记录的购车日期）。需求为：

• 获取指定时间区间（示例：2022-01-20 至 2022-01-30）内，每个人员的最早购车日期及对应car_type
• 若该区间内无购车记录，日期与car_type显示为null

用户已有初步SQL逻辑，但不清楚如何合并date1和date2为单一日期列用于排序与筛选，寻求更优实现方式。

核心思路：合并日期列 #

因为每条记录的date1和date2仅有一个非空，使用COALESCE(date1, date2)可以直接合并两列，得到有效的购车日期值——该函数会返回第一个非空的参数，完美适配当前表结构。

解决方案一：ROW_NUMBER() 排名法 #

通过CTE（公共表表达式）拆分逻辑，确保所有人员都被返回，同时筛选区间内的最早记录：

WITH all_persons AS (
    -- 先获取所有唯一人员，保证无记录的人员也能出现在结果中
    SELECT DISTINCT person FROM car_table
),
filtered_ranked_records AS (
    SELECT 
        person,
        COALESCE(date1, date2) AS purchase_date,
        car_type,
        -- 按人员分组，按购车日期升序排名，排名1即为最早记录
        ROW_NUMBER() OVER (PARTITION BY person ORDER BY COALESCE(date1, date2) ASC) AS rn
    FROM car_table
    -- 筛选指定时间区间内的记录
    WHERE COALESCE(date1, date2) BETWEEN '2022-01-20' AND '2022-01-30'
)
SELECT 
    ap.person,
    frr.purchase_date,
    frr.car_type
FROM all_persons ap
-- 左连接确保无记录的人员返回null
LEFT JOIN filtered_ranked_records frr 
    ON ap.person = frr.person 
    AND frr.rn = 1;

解决方案二：MIN() 聚合关联法 #

先聚合得到每个人员的最早购车日期，再关联回原表获取对应car_type：

WITH all_persons AS (
    SELECT DISTINCT person FROM car_table
),
person_earliest_date AS (
    SELECT 
        person,
        MIN(COALESCE(date1, date2)) AS earliest_purchase_date
    FROM car_table
    WHERE COALESCE(date1, date2) BETWEEN '2022-01-20' AND '2022-01-30'
    GROUP BY person
)
SELECT 
    ap.person,
    ped.earliest_purchase_date,
    ct.car_type
FROM all_persons ap
LEFT JOIN person_earliest_date ped ON ap.person = ped.person
-- 关联回原表获取对应日期的car_type
LEFT JOIN car_table ct 
    ON ap.person = ct.person 
    AND COALESCE(ct.date1, ct.date2) = ped.earliest_purchase_date;

注意：如果同一人员在同一天有多条购车记录，此方法会返回多条结果，可结合ROW_NUMBER()或数据库特有语法（如PostgreSQL的DISTINCT ON）去重。

对原SQL的修改建议 #

将原SQL中的date替换为COALESCE(date1, date2)，并添加时间区间筛选，同时结合all_persons CTE做左连接，即可满足需求：

SELECT ap.person, sub.purchase_date, sub.car_type 
FROM (
    SELECT DISTINCT person FROM car_table
) ap
LEFT JOIN (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY person ORDER BY COALESCE(date1, date2) ASC) AS rn, 
        person, 
        COALESCE(date1, date2) AS purchase_date, 
        car_type
    FROM car_table
    WHERE COALESCE(date1, date2) BETWEEN '2022-01-20' AND '2022-01-30'
) sub ON ap.person = sub.person AND sub.rn = 1;

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