SQL Server 按日期和时间分组

Question

使用 SQL Server，我有一个包含 CreatedAt 列的表，该列具有 datetime 数据类型。我正在尝试编写一个查询，该查询将返回每个日期的总记录，但还将日期分成一天中的时间，即早上、下午和晚上。所以，比如下表

ID     createdAt
------------------------------
1      2017-10-17 21:31:52.160
2      2017-10-17 12:12:12.160
3      2017-10-17 09:09:09.160
4      2017-10-17 21:31:52.160
5      2017-10-16 21:31:52.160

将返回以下结果

DayCounter    SessionDay    Morning    Afternoon    Evening
4             2017-10-17    1          1            2
1             2017-10-16    0          0            1

我有以下两个查询，它们按天对数据进行分组，然后拆分从日期中提取一天中的时间，但我不知道如何将它们放在一起。

SELECT 
    COUNT(createdAt) AS counter, 
    CONVERT(DATE, createdAt) AS sessionDay
FROM 
    MyTable
GROUP BY 
    CONVERT(DATE, createdAt)

和

SELECT 
    CASE 
       WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 12 
          THEN 'Morning'
       WHEN CAST(CONVERT(CHAR(2), createdAt, 108) AS INT) < 17 
          THEN 'Afternoon'
       ELSE 'Evening' 
    END AS sessionPeriod, 
    createdAt 
FROM 
    MyTable

Answer 1

您似乎想要条件聚合，以及更清晰地使用日期/时间函数:

select cast(createdAt as date),
       sum(case when datepart(hour, createdat) < 12 then 1 else 0 end) as Morning,
       sum(case when datepart(hour, createdat) >= 12 and datepart(hour, createdat) < 17 then 1 else 0 end) as Afternoon,
       sum(case when datepart(hour, createdat) >= 17 then 1 else 0 end) as Evening
from mytable
group by cast(createdAt as date)
order by cast(createdAt as date);