MySQL如何基于日出时间动态分组计算日均风速平均值？

我有一张每隔几分钟插入风速数据的data表，希望按每日特定时间窗口计算平均风速。目前可通过以下SQL语句实现固定时段计算：

select ROUND(avg(data.wind),1) wind  
FROM data 
WHERE station in(109) 
&&  hour(data.datum)>=7 
&& hour(data.datum)<= 8 
group by month(data.datum), day(data.datum)

但需求是基于日出时间设置动态时间窗口（日出前1小时至日出后1小时），已找到计算日出时间的MySQL函数SunRiseSet，调用方式如下：

select SunRiseSet(yyyy-mm-dd, 45.299, 13.571, 'nautical', 'rise');

例如每日时间窗口为：

day1 7:20-8:20
day2 7:21-8:21
day3 7:23-8:23
etc.

请问能否通过单条SQL语句实现该需求？

可以用单条SQL实现，核心思路为每条数据匹配对应日期的日出时间，判断数据时间是否落在「日出前1小时至日出后1小时」的窗口内，最后按日期分组计算平均风速。

基础版本SQL（兼容多数MySQL版本） #

SELECT
    DATE(d.datum) AS record_date,
    ROUND(AVG(d.wind), 1) AS avg_wind
FROM data d
JOIN (
    -- 提取data表中所有有数据的日期，并计算对应日出时间
    SELECT
        DATE(datum) AS day_date,
        SunRiseSet(DATE(datum), 45.299, 13.571, 'nautical', 'rise') AS sunrise_time
    FROM data
    WHERE station IN (109)
    GROUP BY DATE(datum)
) daily_sunrise ON DATE(d.datum) = daily_sunrise.day_date
WHERE
    d.station IN (109)
    AND d.datum >= daily_sunrise.sunrise_time - INTERVAL 1 HOUR
    AND d.datum <= daily_sunrise.sunrise_time + INTERVAL 1 HOUR
GROUP BY DATE(d.datum)
ORDER BY record_date;

说明 #

1. 子查询daily_sunrise先获取所有有数据的日期，调用SunRiseSet计算每日日出时间；
2. 主表与子查询按日期关联，得到每条数据对应日期的日出时间；
3. 用INTERVAL关键字计算动态窗口的起止时间，筛选符合条件的风速数据；
4. 按日期分组计算平均风速，结果保留1位小数。

MySQL 8.0+ 优化写法（CTE） #

如果你的MySQL版本支持CTE（8.0及以上），可以用更清晰的写法：

WITH daily_sunrise AS (
    SELECT
        DATE(datum) AS day_date,
        SunRiseSet(DATE(datum), 45.299, 13.571, 'nautical', 'rise') AS sunrise_time
    FROM data
    WHERE station IN (109)
    GROUP BY DATE(datum)
)
SELECT
    DATE(d.datum) AS record_date,
    ROUND(AVG(d.wind), 1) AS avg_wind
FROM data d
JOIN daily_sunrise ON DATE(d.datum) = daily_sunrise.day_date
WHERE
    d.station IN (109)
    AND d.datum BETWEEN (daily_sunrise.sunrise_time - INTERVAL 1 HOUR) 
                    AND (daily_sunrise.sunrise_time + INTERVAL 1 HOUR)
GROUP BY record_date
ORDER BY record_date;

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