MariaDB中SUM OVER窗口函数求和异常问题排查与解决请求

我有一批光伏发电数据需要分析并可视化，目标是统计MariaDB中DTP表每年各月发电量的总和与平均值（可选添加单标准差误差棒）。目前用SUM OVER窗口函数写的查询未正确累加每月所有PWR记录的数值，附上相关信息请求正确SQL语句。

表DTP结构 #

+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| idDTP | int(11)    | NO   | PRI | NULL    | auto_increment |
| DT    | datetime   | NO   |     | NULL    |                |
| PWR   | varchar(6) | NO   |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+

当前错误查询语句 #

SELECT YEAR(DT) AS 'Year',
       MONTH(DT) AS 'Month',  
       SUM(PWR) OVER(PARTITION BY Month,Year) AS 'Total Monthly Power (kWh)', 

FROM DTP GROUP BY YEAR(DT), MONTH(DT);

错误执行结果（部分） #

+------+-------+-------------------------+
| Year | Month | Total Monthly Power kWh |
+------+-------+-------------------------+
| 2012 |     2 |                    0.03 |
| 2012 |     3 |                   0.012 |
| 2012 |     4 |                   0.012 |
| 2012 |     5 |                   0.006 |
| 2012 |     6 |                   0.036 |
...

问题根源 #

1. 字段类型错误：PWR是varchar字符串类型，直接用SUM会按字符串拼接而非数值累加，这是结果异常的核心原因。
2. 逻辑混用错误：SUM OVER窗口函数搭配GROUP BY的逻辑冗余，窗口函数会为每行返回分组总和，但GROUP BY仅保留每组第一行数据，导致统计结果失效。

正确SQL查询语句 #

SELECT 
    YEAR(DT) AS `Year`,
    MONTH(DT) AS `Month`,
    SUM(CAST(PWR AS DECIMAL(8,3))) AS `Total Monthly Power (kWh)`,
    AVG(CAST(PWR AS DECIMAL(8,3))) AS `Average Monthly Power (kWh)`,
    STDDEV_SAMP(CAST(PWR AS DECIMAL(8,3))) AS `Sample Std Deviation`
FROM DTP
GROUP BY YEAR(DT), MONTH(DT)
ORDER BY `Year`, `Month`;

语句说明 #

• 类型转换：通过CAST(PWR AS DECIMAL(8,3))将字符串类型的PWR转为数值型，确保聚合函数能正确计算。
• 聚合逻辑优化：直接用SUM、AVG搭配GROUP BY按年、月分组统计，逻辑清晰高效。
• 标准差支持：用STDDEV_SAMP计算样本标准差（若需总体标准差可替换为STDDEV_POP），满足误差棒可视化需求。
• 排序输出：添加ORDER BY保证结果按年月顺序排列，方便后续可视化处理。

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