时间戳

首页 > 解决方案 > MySQL sum() 按月和年的值每次都会给我不同的结果

mysql - MySQL sum() 按月和年的值每次都会给我不同的结果

问题描述

我正在尝试计算多年来客户“分数”的每日数据总和（日期格式为 %Y-%m-%d），但是每次我将查询运行到一个新表中时，我都会得到一个不同的总和结果，似乎跳过了随机日期。

看起来问题是客户名称。

我目前的查询：

INSERT INTO monthscore
(SELECT name, YEAR(date) as year, MONTH(date) as month, sum(score) as monthscore 
FROM fastfoodSales
GROUP BY name, year, month)

例如，客户在一个月内的实际得分为 400,000，但当我运行上述查询时，它输出 380,000 或 399,000 或 390,000。自 1960 年以来，大约有 100,000 名客户拥有数据。

fastfoodSales 数据示例：

+----+---------+------------+--------+
| id | name    | date       | score  |
+----+---------+------------+--------+
| 1  | Anthony | 2018-12-15 | 15.81  |
+----+---------+------------+--------+
| 2  | Jason   | 2018-12-15 | 123.12 |
+----+---------+------------+--------+
| 3  | Chris   | 2019-11-17 | 18.21  |
+----+---------+------------+--------+
| 4  | Chris   | 2019-11-17 | 19.24  |
+----+---------+------------+--------+
| 5  | Chris   | 2019-11-18 | 24.12  |
+----+---------+------------+--------+
| 6  | Jason   | 2019-12-01 | 14.24  |
+----+---------+------------+--------+
| 7  | Jason   | 2019-12-04 | 14.24  |
+----+---------+------------+--------+
| 8  | Anthony | 2019-12-14 | 81.14  |
+----+---------+------------+--------+
| 9  | Anthony | 2019-12-15 | 23.21  |
+----+---------+------------+--------+


CREATE TABLE `fastfoodSales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `date` date DEFAULT NULL,
  `score` double(18,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `indApril1` (`date`),
  KEY `indnov29` (`name`),
  KEY `indascore` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4152675 DEFAULT CHARSET=latin1

月分数表的预期输出：

+---------+------+-------+------------+
| name    | year | month | monthscore |
+---------+------+-------+------------+
| Anthony | 2019 | 08    | 3213.23    |
| Anthony | 2019 | 09    | 12312.32   |
| Anthony | 2019 | 10    | 531.38     |
| Anthony | 2019 | 11    | 431.17     |
| Anthony | 2019 | 12    | 4314.34    |
| Jason   | 2007 | 01    | 39.12      |
| Jason   | 2007 | 02    | 2.1        |
| Jason   | 2007 | 03    | 14.4       |
| Jason   | 2007 | 04    | 23.20      |
+---------+------+-------+------------+

CREATE TABLE `monthscore` (
  `name` varchar(200) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `year` int(4) DEFAULT NULL,
  `month` int(2) DEFAULT NULL,
  `score` double(19,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

但我最终得到：

+---------+------+-------+------------+
| name    | year | month | monthscore |
+---------+------+-------+------------+
| Anthony | 2019 | 08    | 2213.23    |
| Anthony | 2019 | 09    | 11312.32   |
| Anthony | 2019 | 10    | 531.38     |
| Anthony | 2019 | 11    | 431.17     |
| Anthony | 2019 | 12    | 4314.34    |
| Jason   | 2007 | 01    | 1.12       |
| Jason   | 2007 | 02    | 2.1        |
| Jason   | 2007 | 03    | 14.4       |
| Jason   | 2007 | 04    | 18.17      |
+---------+------+-------+------------+

标签： mysqlsummysql-workbench

解决方案

您的查询似乎很好-但我不知道您为什么要存储派生数据...

DROP TABLE IF EXISTS  fastfoodSales;

CREATE TABLE fastfoodsales
(id SERIAL PRIMARY KEY
,name  VARCHAR(12) NOT NULL
,date  DATE NOT NULL
,score DECIMAL(6,2) NOT NULL
);

INSERT INTO fastfoodSales (name,date,score) VALUES
('Anthony','2018-12-15',15.81),
('Jason','2018-12-15',123.12),
('Chris','2019-11-17',18.21),
('Chris','2019-11-17',19.24),
('Chris','2019-11-18',24.12),
('Jason','2019-12-01',14.24),
('Jason','2019-12-04',18.19),
('Anthony','2019-12-14',81.14),
('Anthony','2019-12-15',23.21);

SELECT name
     , DATE_FORMAT(date,'%Y-%m') yearmonth
     , SUM(score) monthscore 
  FROM fastfoodSales
 GROUP 
    BY name
     , yearmonth

+---------+-----------+------------+
| name    | yearmonth | monthscore |
+---------+-----------+------------+
| Anthony | 2018-12   |      15.81 |
| Anthony | 2019-12   |     104.35 |
| Chris   | 2019-11   |      61.57 |
| Jason   | 2018-12   |     123.12 |
| Jason   | 2019-12   |      32.43 |
+---------+-----------+------------+

推荐阅读