MySQL按日期分组，取指定数据计算veter平均值的实现方法

要实现对所有日期计算跳过datum_aladin降序第一条后的3条数据的veter平均值，可以借助MySQL的窗口函数ROW_NUMBER()来标记每个日期分组内的行顺序，再筛选目标行后计算平均值。

完整查询语句 #

SELECT 
    datum,
    AVG(veter) AS avg_veter
FROM (
    SELECT 
        datum,
        veter,
        ROW_NUMBER() OVER (
            PARTITION BY datum, spot 
            ORDER BY datum_aladin DESC
        ) AS row_num
    FROM napovedt
    WHERE spot = '19' -- 保留原查询中的站点筛选条件
) AS ranked_data
WHERE row_num BETWEEN 2 AND 4 -- 跳过每组第1条，取后续3条数据
GROUP BY datum
ORDER BY datum;

语句说明 #

1. 子查询ranked_data：

   • 通过PARTITION BY datum, spot将数据按日期和站点分组，确保每个分组内的排序独立进行
   • ORDER BY datum_aladin DESC让每个分组内的数据按datum_aladin降序排列
   • ROW_NUMBER()为分组内的每行分配唯一序号，降序后的第一条数据序号为1，后续依次递增

2. 外层查询：

   • 筛选row_num在2到4之间的行，精准匹配「跳过第一条、取接下来3条」的需求
   • 按datum分组后，用AVG(veter)计算每个日期的目标平均值

验证示例 #

针对你提供的2023-01-30日期数据，子查询会为目标三条数据分配行号2、3、4，外层查询计算出的平均值为：(4.0 + 4.0 + 4.5)/3 = 4.1667（默认保留四位小数）。

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