使用MySQL查询或javascript进行数据优化

Question

我在mysql中有这些数据。

| code | date                | value |
| A    | 2016-04-04 00:00:00 | 0.1   |
| B    | 2016-04-04 00:00:02 | 0.5   |
| C    | 2016-04-04 00:00:05 | 1     |
| A    | 2016-04-04 00:11:00 | 0.2   |
| B    | 2016-04-04 00:12:25 | 0.6   |
| C    | 2016-04-04 00:15:30 | 0.4   |

我想使用nodejs/expressjs调整并向json发送数据每日、每月、每年的总和。

首先我尝试这个查询。

 SELECT date, value FROM 'table'
 WHERE code = 'A'
     AND date >= '2016-04-05 00:00:00'
 GROUP BY DATE_FORMAT(date, "%y-%m-%d-%H") // or "%y-%m-%d", "%y-%m"
 ORDER BY date;

此查询运行良好。但我认为这是浪费。因为重复查询代码的数量。

输出：

+---------------------+-------+ 
|         date        | value | 
+---------------------+-------+ 
| 2016-04-05 00:01:56 | 0 | 
| 2016-04-05 01:01:56 | 0 | 
| 2016-04-05 02:01:58 | 0 | 
| 2016-04-05 03:01:57 | 0 | 
| 2016-04-05 04:01:58 | 0 | 
| 2016-04-05 05:01:58 | 0 | 
| 2016-04-05 06:01:59 | 0 | 
| 2016-04-05 07:01:58 | 0 | 
| 2016-04-05 08:01:58 | 0 | 
| 2016-04-05 09:01:59 | 0 | 
| 2016-04-05 10:01:59 | 0.009 | 
| 2016-04-05 11:02:00 | 0.007 | 
+---------------------+-------+

我可以用一个查询获取所有数据吗？

最后，我想用expressjs发送这种形式的数据。

[
    A: [{ date: '2016-04-04 00:00:00', sum: 4},
       { date: '2016-04-04 00:01:00', sum: 6}],
    B: [{ date: '2016-04-04 00:00:00', sum: 4},
       { date: '2016-04-04 00:01:00', sum: 6}]
]

或者这种形式的

[
    { date: '2016-04-04 00:00:00', A: 4, B: 4 },
    { date: '2016-04-04 00:01:00', A: 4, B: 4 }
]

我尝试过mysqlquery、sequelize、lodash。但我找不到正确的路。

Answer 1

您可以使用子查询来格式化日期，以便对其进行分组。例如：

SELECT code, date, sum(value) as value
FROM (select code, DATE_FORMAT(date, "%y-%m-%d") as date, value from t1) as t2 
GROUP BY code, date
ORDER BY date

结果：

"A";"16-04-04";"0.30000000447034836"
"B";"16-04-04";"1.100000023841858"
"C";"16-04-04";"1.4000000059604645"