按日期和时间划分的MySql查询组

Question

我正在尝试创建一个SQL查询来解决我的问题。

我的桌子：

+----+---------------------+-------+
| id |         date        | value |
+----+---------------------+-------+
| 1  | 2014-10-10 05:10:10 |  10   |
+----+---------------------+-------+
| 2  | 2014-10-10 09:10:10 |  20   |
+----+---------------------+-------+
| 3  | 2014-10-10 15:10:10 |  30   |
+----+---------------------+-------+
| 4  | 2014-10-10 23:10:10 |  40   |
+----+---------------------+-------+
| 5  | 2014-10-11 08:10:10 |  15   |
+----+---------------------+-------+
| 6  | 2014-10-11 09:10:10 |  25   |
+----+---------------------+-------+
| 7  | 2014-10-11 10:10:10 |  30   |
+----+---------------------+-------+
| 8  | 2014-10-11 23:10:10 |  40   |
+----+---------------------+-------+

我希望按天和这三个分组的值相加，如“早晨”( 06:00 - 12:00)、“下午”(12:00- 18:00)和“夜晚”(00:00-06:00和6:00- 24:00)。

就像这样：

+------------+-------+---------+-----------+-------+
|    date    | value | morning | afternoon | night |
+------------+-------+---------+-----------+-------+
| 2014-10-10 |  100  |   20    |     30    |  50   |
+------------+-------+---------+-----------+-------+
| 2014-10-11 |  110  |   70    |     0     |  40   |
+------------+-------+---------+-----------+-------+

Answer 1

您可以在sum表达式上使用几个case：

SELECT   DAY(`date`) AS `date`
         SUM(CASE WHEN HOUR(`date`) BETWEEN 6 AND 12 THEN value ELSE 0 END) AS `morning`,
         SUM(CASE WHEN HOUR(`date`) BETWEEN 12 AND 18 THEN value ELSE 0 END) AS `afternoon`,
         SUM(CASE WHEN HOUR(`date`) < 6 OR HOUR(`date`) > 18 THEN value ELSE 0 END) AS `evening`
FROM     my_table
GROUP BY DAY(`date`)

Answer 2

要做到这一点有很多种方法，但对于我自己来说，我会首先在交叉应用中提取伪信息，然后对这些信息进行分组。

我相信这提供了显著的可再生的好处，并允许您重复使用任何计算在其他条款。例如，您已经集中了分组机制，这意味着您只需要在一个地方而不是在select和group by中更改它。同样，您可以在WHERE子句中添加"extraData.Morning = 1“，而不是重写早晨的计算。

例如：

CREATE TABLE #TestData (ID INT, Data DATETIME, Value INT)

INSERT INTO #TestData (ID, Data, Value) VALUES
    (1  ,'2014-10-10 05:10:10'   ,10)
    ,(2   ,'2014-10-10 09:10:10'   ,20)
    ,(3   ,'2014-10-10 15:10:10'   ,30)
    ,(4   ,'2014-10-10 23:10:10'   ,40)
    ,(5   ,'2014-10-11 08:10:10'   ,15)
    ,(6   ,'2014-10-11 09:10:10'   ,25)
    ,(7   ,'2014-10-11 10:10:10'   ,30)
    ,(8   ,'2014-10-11 23:10:10'   ,40)

SELECT
    extraData.DayComponent
    ,SUM(td.Value)
    ,SUM(CASE WHEN extraData.Morning = 1 THEN td.Value ELSE 0 END) AS Morning
    ,SUM(CASE WHEN extraData.Afternoon = 1 THEN td.Value ELSE 0 END) AS Afternoon
    ,SUM(CASE WHEN extraData.Night = 1 THEN td.Value ELSE 0 END) AS Night
FROM #TestData td
    CROSS APPLY (
        SELECT
            DATEADD(dd, 0, DATEDIFF(dd, 0, td.Data))        AS DayComponent
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 6 AND 12 THEN 1 ELSE 0 END AS Morning
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 12 AND 18 THEN 1 ELSE 0 END AS Afternoon
            ,CASE WHEN DATEPART(HOUR, td.Data) BETWEEN 0 AND 6 
                OR DATEPART(HOUR, td.Data) BETWEEN 18 AND 24 THEN 1 ELSE 0 END AS Night
    ) extraData
GROUP BY
    extraData.DayComponent

DROP TABLE #TestData