如何对风传感器wind数据表执行分组操作并计算各组平均值？

嘿，针对你这个风传感器数据表的分组统计需求，我来给你整理几个实用的SQL方案，都是实际项目里常用的~

先确认下你的表结构（方便后续参考）：

describe wind;

输出：

Field	Type	Null	Key	Default	Extra
average	float	YES	NULL	NULL
min	float	YES	NULL	NULL
max	float	YES	NULL	NULL
direction	int(11)	YES	NULL	NULL
timestamp	datetime	YES	NULL	NULL

1. 按时间维度分组（最常用场景） #

按时间分组是这类传感器数据最常见的统计方式，比如按小时、天、周来汇总平均风速等指标。需要注意的是风向是环形数据（0°和360°是同一个方向），直接取平均值会有偏差，建议用中位数或者众数来代表分组内的典型风向。

按小时分组统计 #

-- 按小时聚合，计算每小时的平均风速、最小/最大风速均值，以及风向中位数
SELECT
  DATE_FORMAT(timestamp, '%Y-%m-%d %H:00:00') AS hour_interval,
  ROUND(AVG(average), 2) AS avg_wind_speed, -- 保留两位小数更直观
  ROUND(AVG(min), 2) AS avg_min_speed,
  ROUND(AVG(max), 2) AS avg_max_speed,
  -- 用GROUP_CONCAT配合SUBSTRING_INDEX取风向中位数
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(direction ORDER BY direction), ',', FLOOR(COUNT(*)/2)+1), ',', -1) AS median_direction
FROM wind
GROUP BY hour_interval
ORDER BY hour_interval DESC;

按天分组统计 #

只需要调整时间格式化的规则即可：

SELECT
  DATE_FORMAT(timestamp, '%Y-%m-%d') AS day_interval,
  ROUND(AVG(average), 2) AS avg_daily_wind_speed,
  ROUND(AVG(min), 2) AS avg_daily_min_speed,
  ROUND(AVG(max), 2) AS avg_daily_max_speed,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(direction ORDER BY direction), ',', FLOOR(COUNT(*)/2)+1), ',', -1) AS median_daily_direction
FROM wind
GROUP BY day_interval
ORDER BY day_interval DESC;

2. 按风向区间分组统计 #

如果需要分析不同风向的风速特征，可以把0-360°的度数转换成常见的风向区间（比如8个基础方向）：

SELECT
  CASE
    WHEN direction BETWEEN 337.5 AND 360 OR direction BETWEEN 0 AND 22.5 THEN '北 (N)'
    WHEN direction BETWEEN 22.5 AND 67.5 THEN '东北 (NE)'
    WHEN direction BETWEEN 67.5 AND 112.5 THEN '东 (E)'
    WHEN direction BETWEEN 112.5 AND 157.5 THEN '东南 (SE)'
    WHEN direction BETWEEN 157.5 AND 202.5 THEN '南 (S)'
    WHEN direction BETWEEN 202.5 AND 247.5 THEN '西南 (SW)'
    WHEN direction BETWEEN 247.5 AND 292.5 THEN '西 (W)'
    WHEN direction BETWEEN 292.5 AND 337.5 THEN '西北 (NW)'
  END AS wind_direction,
  COUNT(*) AS total_records,
  ROUND(AVG(average), 2) AS avg_speed_in_direction,
  ROUND(AVG(min), 2) AS avg_min_speed,
  ROUND(AVG(max), 2) AS avg_max_speed
FROM wind
GROUP BY wind_direction
-- 按顺时针顺序排序，方便查看
ORDER BY
  CASE wind_direction
    WHEN '北 (N)' THEN 1
    WHEN '东北 (NE)' THEN 2
    WHEN '东 (E)' THEN 3
    WHEN '东南 (SE)' THEN 4
    WHEN '南 (S)' THEN 5
    WHEN '西南 (SW)' THEN 6
    WHEN '西 (W)' THEN 7
    WHEN '西北 (NW)' THEN 8
  END;

3. 按风速区间自定义分组 #

如果需要分析不同风速段的风向分布，也可以用CASE自定义分组：

SELECT
  CASE
    WHEN average < 5 THEN '微风 (<5m/s)'
    WHEN average BETWEEN 5 AND 10 THEN '中等风速 (5-10m/s)'
    ELSE '大风 (>10m/s)'
  END AS speed_range,
  COUNT(*) AS total_records,
  SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(direction ORDER BY direction), ',', FLOOR(COUNT(*)/2)+1), ',', -1) AS median_direction
FROM wind
GROUP BY speed_range;

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