mariadb SQL SUM OVER查询不返回总和

ohtdti5x 于 2023-11-19 发布在其他

关注(0)|答案(2)|浏览(126)

我有一堆太阳能发电数据，我想分析并显示在一个图表中。在这种情况下，我想显示我的数据库（MariaDB）中每年每个月的总和和平均值（也许还带有一个误差条，显示每个月值的单个标准差-尽管这是次要的）
相关表格为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);

型
当我在MariaDB控制台上运行这个命令时，我得到这样的结果：

+------+-------+-------------------------+
| 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 |
| 2012 |     7 |                   0.006 |
| 2012 |     8 |                   0.006 |
| 2012 |     9 |                   0.018 |
| 2012 |    10 |                   0.012 |
| 2012 |    11 |                   0.012 |
| 2012 |    12 |                   0.018 |
| 2013 |     1 |                   0.012 |
| 2013 |     2 |                   0.024 |
| 2013 |     3 |                   0.024 |
| 2013 |     4 |                    0.03 |
| 2013 |     5 |                    0.03 |
| 2013 |     6 |                   0.006 |
| 2013 |     7 |                   0.024 |
| 2013 |     8 |                   0.024 |
| 2013 |     9 |                   0.018 |
| 2013 |    10 |                   0.048 |
| 2013 |    11 |                   0.012 |
| 2013 |    12 |                   0.036 |
| 2014 |     1 |                   0.012 |
| 2014 |     2 |                   0.018 |
| 2014 |     3 |                   0.024 |
| 2014 |     4 |                   0.006 |
| 2014 |     5 |                   0.006 |
| 2014 |     6 |                   0.024 |
| 2014 |     7 |                   0.012 |
| 2014 |     8 |                   0.018 |
| 2014 |     9 |                   0.006 |

型
等等
很明显，它并没有对PWR列中的每个月、每个年的所有记录求和。

mariadb

来源：https://stackoverflow.com/questions/77383592/sql-sum-over-query-doesnt-return-a-sum

2条答案

按热度按时间

tjrkku2a1#

您的查询甚至运行显示了MariaDB方面的许多弱点。您的查询无效有以下几个原因：

AS 'Year'：Year不是字符串文字。别名需要双引号。（MariaDb也允许反引号，这取决于它运行的模式。）
PWR是字符串。你不能得到一个字符串。
SUM(PWR) OVER(...)。中间结果中没有PWR，因此无法将其相加。
MariaDB在这里做什么？
MariaDB让我们为您的别名条错误的分隔符。
1.这是MySQL的一个传统。这两个DBMS将任何字符串转换为数字而不会引发错误。'100'变成100，'2k'变成2，'my brain hurts'变成0。为什么？DBMS只是从左边取数字的位置（例如，'2k'中的2）。当根本没有数字时，转换默认为0。
SUM OVER在任何情况下都不应该工作。为了理解这一点，我们必须想象同样的查询带有一个针对年、月和PWR的选择子句，因为我们需要这三列，来运行结果以获得PWR每年和每月的总和。但是如果我们用PWR替换SUM OVER行，那么DBMS将引发错误DTP.PWR' isn't in GROUP BY。（假设你没有启用作弊模式，MariaDB允许无效的GROUP BY查询。确保总是设置'ONLY_FULL_GROUP_BY'。）你可以看到你不能把PWR放在select子句中，因为DBMS不知道每年和每个月要选择哪一行的PWR，因为没有对它调用聚合函数（如MIN(pwr)或MAX(pwr)）。
您的查询已更正（假设您要选择每月SUM(pwr)：

SELECT YEAR(DT) AS "Year",
       MONTH(DT) AS "Month",
       SUM(PWR) AS "Total Monthly Power (kWh)"
FROM DTP
GROUP BY YEAR(DT), MONTH(DT)
ORDER BY YEAR(DT), MONTH(DT);

字符串
这是可行的，但是您应该纠正您的数据模型，并将PWR列更改为数值类型。
演示：https://dbfiddle.uk/IJLPyqUb

赞(0）回复(0）举报 2023-11-19

amrnrhlw2#

CAST（PWR AS DECIMAL（10，2））太短了，不需要是varchar（6）

赞(0）回复(0）举报 2023-11-19