如何用函数计算启动日起30天内各name_id的平均数值？

没问题，我帮你梳理下实现思路和具体的SQL语句，针对几千条记录完全适用~

核心思路 #

我们需要先通过name_id关联两张表，筛选出table1中日期在对应startup_date到startup_date+30天范围内的记录，计算这些记录的value平均值，最后将结果更新（或新增）到table2的Avg_value列中。

第一步：先确认table2是否有Avg_value列 #

如果还没有这个列，需要先新增（根据你的数值精度需求调整类型）：

-- MySQL/MariaDB/PostgreSQL
ALTER TABLE table2 ADD COLUMN Avg_value DECIMAL(10,2);

-- SQL Server
ALTER TABLE table2 ADD Avg_value DECIMAL(10,2);

针对不同数据库的具体SQL语句 #

1. MySQL/MariaDB 版本 #

基础更新（仅更新有对应数据的name_id）

UPDATE table2 t2
JOIN (
    SELECT 
        t1.name_id,
        AVG(t1.value) AS avg_val
    FROM table1 t1
    JOIN table2 t2 ON t1.name_id = t2.id
    WHERE t1.date BETWEEN t2.startup_date AND DATE_ADD(t2.startup_date, INTERVAL 30 DAY)
    GROUP BY t1.name_id
) AS avg_data ON t2.id = avg_data.name_id
SET t2.Avg_value = avg_data.avg_val;

处理无数据的情况（将无数据的name_id的Avg_value设为0或NULL）

如果有些name_id在table1里没有30天内的记录，想统一设置默认值，可以用LEFT JOIN：

UPDATE table2 t2
LEFT JOIN (
    SELECT 
        t1.name_id,
        AVG(t1.value) AS avg_val
    FROM table1 t1
    JOIN table2 t2 ON t1.name_id = t2.id
    WHERE t1.date BETWEEN t2.startup_date AND DATE_ADD(t2.startup_date, INTERVAL 30 DAY)
    GROUP BY t1.name_id
) AS avg_data ON t2.id = avg_data.name_id
SET t2.Avg_value = COALESCE(avg_data.avg_val, 0); -- 这里0可以换成NULL，按需调整

2. PostgreSQL 版本 #

基础更新

UPDATE table2 t2
SET Avg_value = avg_data.avg_val
FROM (
    SELECT 
        t1.name_id,
        AVG(t1.value) AS avg_val
    FROM table1 t1
    JOIN table2 t2 ON t1.name_id = t2.id
    WHERE t1.date BETWEEN t2.startup_date AND t2.startup_date + INTERVAL '30 days'
    GROUP BY t1.name_id
) AS avg_data
WHERE t2.id = avg_data.name_id;

处理无数据的情况

UPDATE table2 t2
SET Avg_value = COALESCE(avg_data.avg_val, 0)
FROM (
    SELECT 
        t2.id AS name_id,
        AVG(t1.value) AS avg_val
    FROM table2 t2
    LEFT JOIN table1 t1 ON t1.name_id = t2.id 
        AND t1.date BETWEEN t2.startup_date AND t2.startup_date + INTERVAL '30 days'
    GROUP BY t2.id
) AS avg_data
WHERE t2.id = avg_data.name_id;

3. SQL Server 版本 #

基础更新

UPDATE t2
SET t2.Avg_value = avg_data.avg_val
FROM table2 t2
JOIN (
    SELECT 
        t1.name_id,
        AVG(t1.value) AS avg_val
    FROM table1 t1
    JOIN table2 t2 ON t1.name_id = t2.id
    WHERE t1.date BETWEEN t2.startup_date AND DATEADD(DAY, 30, t2.startup_date)
    GROUP BY t1.name_id
) AS avg_data ON t2.id = avg_data.name_id;

处理无数据的情况

UPDATE t2
SET t2.Avg_value = ISNULL(avg_data.avg_val, 0)
FROM table2 t2
LEFT JOIN (
    SELECT 
        t1.name_id,
        AVG(t1.value) AS avg_val
    FROM table1 t1
    JOIN table2 t2 ON t1.name_id = t2.id
    WHERE t1.date BETWEEN t2.startup_date AND DATEADD(DAY, 30, t2.startup_date)
    GROUP BY t1.name_id
) AS avg_data ON t2.id = avg_data.name_id;

注意事项 #

• 确保table1的name_id和table2的id是关联的主键/外键，且数据类型一致，否则会出现关联错误。
• 几千条记录的规模下，这些语句执行效率足够；如果后续数据量大幅增长，可以考虑给table1的name_id和date字段添加联合索引，提升查询速度。

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