基于日期范围的MySQL表JOIN与GROUP聚合实现方法问询

核心思路是先为表A的每一行定义对应的日期区间，再关联表B聚合计算区间内的val_B平均值，具体步骤如下：

1. 为表A生成日期区间 #

通过窗口函数LEAD()，按uid分组、dt排序，获取每个行的下一个日期next_dt；对于每个uid的最后一行（无后续日期），用一个极大值（如'9999-12-31'）填充，确保能包含B表中所有大于该dt的日期数据。

示例预处理SQL（以MySQL为例，需先统一日期格式）：

WITH A_with_range AS (
    SELECT 
        uid,
        dt,
        val_A,
        LEAD(dt) OVER (PARTITION BY uid ORDER BY STR_TO_DATE(dt, '%d/%m/%Y')) AS next_dt
    FROM A
),
A_final_range AS (
    SELECT 
        uid,
        dt,
        val_A,
        COALESCE(next_dt, '9999-12-31') AS next_dt
    FROM A_with_range
)

2. 关联表B计算平均值 #

将处理好区间的表A与表B关联，筛选出B表中日期落在对应区间内的数据，按表A的行分组计算val_B的平均值；若无匹配数据，平均值设为0。

完整SQL（包含上述CTE）：

WITH A_with_range AS (
    SELECT 
        uid,
        dt,
        val_A,
        LEAD(dt) OVER (PARTITION BY uid ORDER BY STR_TO_DATE(dt, '%d/%m/%Y')) AS next_dt
    FROM A
),
A_final_range AS (
    SELECT 
        uid,
        dt,
        val_A,
        COALESCE(next_dt, '9999-12-31') AS next_dt
    FROM A_with_range
)
SELECT 
    a.uid,
    a.dt,
    a.val_A,
    COALESCE(AVG(b.val_B), 0) AS val_C
FROM A_final_range a
LEFT JOIN B b 
    ON a.uid = b.uid
    AND STR_TO_DATE(b.date, '%d/%m/%Y') >= STR_TO_DATE(a.dt, '%d/%m/%Y')
    AND STR_TO_DATE(b.date, '%d/%m/%Y') < STR_TO_DATE(a.next_dt, '%d/%m/%Y')
GROUP BY a.uid, a.dt, a.val_A
ORDER BY a.uid, STR_TO_DATE(a.dt, '%d/%m/%Y');

关键说明 #

• 日期格式转换：原表日期为dd/mm/yyyy格式，需用STR_TO_DATE（MySQL）、TO_DATE（PostgreSQL/Oracle）转换为数据库可识别的日期类型，保证日期比较逻辑正确。
• LEAD函数：用于获取每个uid下一行的dt，实现区间划分；最后一行用极大值填充，确保B表中超出A表最大dt的数据能被聚合到最后一行。
• COALESCE函数：处理空值场景，将无匹配数据时的平均值设为0，将最后一行的next_dt设为极大值。

适配补充场景（表B2） #

上述SQL可直接适配表B2的场景：最后一行的next_dt被设为'9999-12-31'，B2中uid=100的10/09/2012、11/09/2012数据会被纳入dt=08/09/2012的区间，计算平均值(4+2)/2=3，与期望输出一致。

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