MariaDB如何编写SQL计算DTP表的日周月年平均发电量

你原SQL的核心问题 #

• 语法逻辑错误：WHERE (year(DT) AND month(DT) AND day(DT)) 是布尔逻辑运算，返回的是0/1值，无法匹配子查询返回的多列结果
• 子查询字段不匹配：IN子查询返回的字段顺序和数量都和外层条件要求不一致，且多余返回了PWR字段，子查询末尾还多写了分号导致语法报错
• 逻辑冗余：统计周期聚合不需要嵌套子查询，直接按时间维度分组即可实现

各统计周期查询实现 #

你使用的MariaDB 10.5版本原生支持常用的时间处理函数，直接按对应维度分组取AVG即可，以下是各维度的独立查询：

1. 日平均发电量

按天分组统计每天所有10分钟采样的平均功率：

SELECT 
  DATE(DT) AS 统计日期,
  AVG(PWR) AS 日平均发电功率_kW
FROM DTP
WHERE DT IS NOT NULL
GROUP BY 统计日期
ORDER BY 统计日期;

2. 周平均发电量

使用YEARWEEK函数处理周维度，第二个参数设为1代表周从周一开始计算，符合国内统计习惯：

SELECT 
  YEARWEEK(DT, 1) AS 统计年周,
  AVG(PWR) AS 周平均发电功率_kW
FROM DTP
WHERE DT IS NOT NULL
GROUP BY 统计年周
ORDER BY 统计年周;

3. 月平均发电量

SELECT 
  DATE_FORMAT(DT, '%Y-%m') AS 统计月份,
  AVG(PWR) AS 月平均发电功率_kW
FROM DTP
WHERE DT IS NOT NULL
GROUP BY 统计月份
ORDER BY 统计月份;

4. 年平均发电量

SELECT 
  YEAR(DT) AS 统计年份,
  AVG(PWR) AS 年平均发电功率_kW
FROM DTP
WHERE DT IS NOT NULL
GROUP BY 统计年份
ORDER BY 统计年份;

补充说明 #

如果你的需求是计算周期内总发电量的平均值，而非采样点功率的平均值，需要先按小粒度求和再取平均。比如计算所有日期的日均总发电量（单位kWh），每个10分钟是1/6小时，单条记录对应发电量为PWR * 1/6：

SELECT AVG(daily_total) AS 日均总发电量_kWh
FROM (
  SELECT SUM(PWR * 1/6) AS daily_total
  FROM DTP
  WHERE DT IS NOT NULL
  GROUP BY DATE(DT)
) t;

其他周期的总发电量平均可以按照这个逻辑修改分组维度即可。

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