SQL技术问询：如何为Table A添加对应Table B日期范围的平均值列？

问题描述 #

我有两张SQL表：

表A（TABLE A） #

表B（TABLE B） #

需要给表A添加一列average_value，值为对应obj_id下，表B中date落在表A对应行start_date与end_date范围内的value的平均值，预期结果如下：

解决方案 #

可以用关联子查询来实现需求，SQL语句如下：

SELECT 
    a.obj_id,
    a.start_date,
    a.end_date,
    (
        SELECT AVG(b.value)
        FROM TABLE_B b
        WHERE b.obj_id = a.obj_id
          AND b.date BETWEEN a.start_date AND a.end_date
    ) AS average_value
FROM TABLE_A a;

如果需要永久给表A添加这一列，可以分两步操作：

-- 第一步：添加列
ALTER TABLE TABLE_A ADD COLUMN average_value DECIMAL(10,2);

-- 第二步：更新列值
UPDATE TABLE_A a
SET average_value = (
    SELECT AVG(b.value)
    FROM TABLE_B b
    WHERE b.obj_id = a.obj_id
      AND b.date BETWEEN a.start_date AND a.end_date
);

补充说明 #

• 关联子查询会针对表A的每一行，在表B中筛选出同obj_id且日期在start_date和end_date之间的记录，计算其value的平均值。
• 如果某一行没有符合条件的表B记录，average_value会返回NULL，若需要默认值，可使用COALESCE函数，例如COALESCE(AVG(b.value), 0)将NULL转为0。

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