mysql - 按月和年分组会给出错误的答案

Question

我创建了一个表并将值插入到我的数据库中，如下所示:

CREATE TABLE task (
  date DATE,
  total_rides INT
);

INSERT INTO TASK VALUES(('2011-01-01'), 985);
INSERT INTO TASK VALUES(('2011-01-02'), 801);
INSERT INTO TASK VALUES(('2011-01-03'), 1349);
INSERT INTO TASK VALUES(('2011-01-04'), 1562);
INSERT INTO TASK VALUES(('2011-01-05'), 1600);
INSERT INTO TASK VALUES(('2011-01-06'), 1606);
INSERT INTO TASK VALUES(('2011-01-07'), 1510);
INSERT INTO TASK VALUES(('2011-01-08'), 959);
INSERT INTO TASK VALUES(('2011-01-09'), 822);
INSERT INTO TASK VALUES(('2011-01-10'), 1321);
INSERT INTO TASK VALUES(('2011-01-11'), 1263);
INSERT INTO TASK VALUES(('2011-01-12'), 1162);
INSERT INTO TASK VALUES(('2011-01-13'), 1406);
INSERT INTO TASK VALUES(('2011-01-14'), 1421);
INSERT INTO TASK VALUES(('2011-01-15'), 1248);
INSERT INTO TASK VALUES(('2011-01-16'), 1204);
INSERT INTO TASK VALUES(('2011-01-17'), 1000);
INSERT INTO TASK VALUES(('2011-01-18'), 683);
INSERT INTO TASK VALUES(('2011-01-19'), 1650);
INSERT INTO TASK VALUES(('2011-01-20'), 1927);
INSERT INTO TASK VALUES(('2011-01-21'), 1543);
INSERT INTO TASK VALUES(('2011-01-22'), 981);
INSERT INTO TASK VALUES(('2011-01-23'), 986);
INSERT INTO TASK VALUES(('2011-01-24'), 1416);
INSERT INTO TASK VALUES(('2011-01-25'), 1985);
INSERT INTO TASK VALUES(('2011-01-26'), 506);
INSERT INTO TASK VALUES(('2011-01-27'), 431);
INSERT INTO TASK VALUES(('2011-01-28'), 1167);
INSERT INTO TASK VALUES(('2011-01-29'), 1098);
INSERT INTO TASK VALUES(('2011-01-30'), 1096);
INSERT INTO TASK VALUES(('2011-01-31'), 1501);

INSERT INTO TASK VALUES(('2011-02-01'), 1360);
INSERT INTO TASK VALUES(('2011-02-02'), 1526);
INSERT INTO TASK VALUES(('2011-02-03'), 1550);
INSERT INTO TASK VALUES(('2011-02-04'), 1708);
INSERT INTO TASK VALUES(('2011-02-05'), 1005);
INSERT INTO TASK VALUES(('2011-02-06'), 1623);
INSERT INTO TASK VALUES(('2011-02-07'), 1712);
INSERT INTO TASK VALUES(('2011-02-08'), 1530);
INSERT INTO TASK VALUES(('2011-02-09'), 1605);
INSERT INTO TASK VALUES(('2011-02-10'), 1538);
INSERT INTO TASK VALUES(('2011-02-11'), 1746);
INSERT INTO TASK VALUES(('2011-02-12'), 1472);
INSERT INTO TASK VALUES(('2011-02-13'), 1589);
INSERT INTO TASK VALUES(('2011-02-14'), 1913);
INSERT INTO TASK VALUES(('2011-02-15'), 1815);
INSERT INTO TASK VALUES(('2011-02-16'), 2115);
INSERT INTO TASK VALUES(('2011-02-17'), 2475);
INSERT INTO TASK VALUES(('2011-02-18'), 2927);
INSERT INTO TASK VALUES(('2011-02-19'), 1635);
INSERT INTO TASK VALUES(('2011-02-20'), 1812);
INSERT INTO TASK VALUES(('2011-02-21'), 1107);
INSERT INTO TASK VALUES(('2011-02-22'), 1450);
INSERT INTO TASK VALUES(('2011-02-23'), 1917);
INSERT INTO TASK VALUES(('2011-02-24'), 1807);
INSERT INTO TASK VALUES(('2011-02-25'), 1461);
INSERT INTO TASK VALUES(('2011-02-26'), 1969);
INSERT INTO TASK VALUES(('2011-02-27'), 2402);
INSERT INTO TASK VALUES(('2011-02-28'), 1446);

INSERT INTO TASK VALUES(('2012-01-01'), 2294);
INSERT INTO TASK VALUES(('2012-01-02'), 1951);
INSERT INTO TASK VALUES(('2012-01-03'), 2236);
INSERT INTO TASK VALUES(('2012-01-04'), 2368);
INSERT INTO TASK VALUES(('2012-01-05'), 3272);
INSERT INTO TASK VALUES(('2012-01-06'), 4098);
INSERT INTO TASK VALUES(('2012-01-07'), 4521);
INSERT INTO TASK VALUES(('2012-01-08'), 3425);
INSERT INTO TASK VALUES(('2012-01-09'), 2376);
INSERT INTO TASK VALUES(('2012-01-10'), 3598);
INSERT INTO TASK VALUES(('2012-01-11'), 2177);
INSERT INTO TASK VALUES(('2012-01-12'), 4097);
INSERT INTO TASK VALUES(('2012-01-13'), 3214);
INSERT INTO TASK VALUES(('2012-01-14'), 2493);
INSERT INTO TASK VALUES(('2012-01-15'), 2311);
INSERT INTO TASK VALUES(('2012-01-16'), 2298);
INSERT INTO TASK VALUES(('2012-01-17'), 2935);
INSERT INTO TASK VALUES(('2012-01-18'), 3376);
INSERT INTO TASK VALUES(('2012-01-19'), 3292);
INSERT INTO TASK VALUES(('2012-01-20'), 3163);
INSERT INTO TASK VALUES(('2012-01-21'), 1301);
INSERT INTO TASK VALUES(('2012-01-22'), 1977);
INSERT INTO TASK VALUES(('2012-01-23'), 2432);
INSERT INTO TASK VALUES(('2012-01-24'), 4339);
INSERT INTO TASK VALUES(('2012-01-25'), 4270);
INSERT INTO TASK VALUES(('2012-01-26'), 4075);
INSERT INTO TASK VALUES(('2012-01-27'), 3456);
INSERT INTO TASK VALUES(('2012-01-28'), 4023);
INSERT INTO TASK VALUES(('2012-01-29'), 3243);
INSERT INTO TASK VALUES(('2012-01-30'), 3624);
INSERT INTO TASK VALUES(('2012-01-31'), 4509);

我想计算两年内每个月的平均每日共享单车数量以及每日共享单车的方差，为此我编写了一个查询:

SELECT MONTH(DATE) AS mon, YEAR(date) AS Yr, AVG(task.total_rides) AS Average, std(task.total_rides) AS stdev, VARIANCE(task.total_rides) AS Var
FROM task
GROUP BY CAST(MONTH(task.date) AS VARCHAR(2)) + '-' + CAST(YEAR(task.date) AS VARCHAR(4));

它给我的输出是:

mon  Yr        Average        stdev          Var
1    2,011    1231.9032     366.3764      134231.7003
2    2,011    2456.9322     973.6375      947969.9615

只有 2011 年 1 月份的第一个结果是正确的。

而不是分别计算 2011 年 2 月和 2012 年 1 月的平均值、标准差和方差并将输出显示为:

预期输出:

mon  Yr        Average        stdev          Var
1    2,011    1231.9032     366.3764      134231.7003
2    2,011    1721.9643     391.3279      153137.5344
1    2,012    3120.7742     858.6585      737294.3684 

它将属于 2011 年 2 月和 2012 年 1 月的所有值组合在一起，然后计算 avg、std 和 var。

知道我做错了什么吗？

提前致谢

Answer 1

您需要使用单独的月份和年份部分进行分组或使用正确的语法进行连接:

GROUP BY CONCAT(CAST(MONTH(task.date) AS VARCHAR(2)), '-', CAST(YEAR(task.date) AS VARCHAR(4)))

或

GROUP BY MONTH(task.date), YEAR(task.date)

后者是首选，因为它适用于数字数据，使用您使用的结果(因此它适用于适当的严格设置)并且您不会在其他任何地方使用连接的字符串。

您的方法不起作用的原因是 + 不适用于连接字符串。是为了加法。因为 MySQL 假定任何字符串都可以转换为数字，所以不会导致错误。因此，您的查询将 2011+0+2 和 2012+0+1 计算为 2013 年并将它们组合在一起。

MySQL 隐式转换为数字的方式是从头开始获取尽可能多的数字字符。所以‘123abc’等于 123，‘-’为零，因为开头没有数字字符。