SQL按日期小时分组如何获取每日员工打卡峰值Top1记录

现有存储员工打卡记录的CheckIns表，需生成两份统计报表：

• 按日期、小时维度统计打卡总次数
• 统计每日唯一打卡高峰时段，即当日员工打卡量最高的对应小时

第一份报表已通过如下SQL实现：

SELECT CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns]
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
ORDER BY CONVERT(DATE,CheckInDateTime)

针对第二份报表，在上述查询的ORDER BY子句添加CheckInCount DESC排序规则后，可将每日打卡量最高的小时记录排在对应日期结果的最前列，例：6月3日打卡高峰集中在20点-21点，20点时段的记录会排在该日期结果首位。

但该查询会返回所有日期+小时维度的统计结果，无法仅展示每日峰值的单条记录，需修改SQL达成需求，现有待调整SQL如下：

SELECT CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns]
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
ORDER BY CONVERT(DATE,CheckInDateTime), CheckInCount DESC

核心逻辑是在已有的小时维度聚合结果基础上，按日期分组筛选出打卡量最高的单条记录，以下是适配SQL Server环境的两种实现方式：

方法1：窗口函数实现（推荐，性能更好） #

用CTE封装已有的小时聚合逻辑，通过窗口函数给同日期下的记录按打卡量倒序编号，再筛选每个日期编号为1的记录即可：

WITH HourlyCheckInStats AS (
    -- 此处复用已完成的小时维度统计逻辑，和第一份报表口径完全一致
    SELECT 
        CONVERT(DATE,CheckInDateTime) AS [Date]
        , DATEPART(HOUR, CheckInDateTime) AS [Hour]
        , COUNT(*) AS CheckInCount
    FROM [dbo].[CheckIns]
    GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
)
SELECT [Date], [Hour], CheckInCount
FROM (
    SELECT
        *
        -- 如果同日期存在多个小时打卡量并列最高、需要全部返回的话，把ROW_NUMBER替换为RANK即可
        , ROW_NUMBER() OVER (PARTITION BY [Date] ORDER BY CheckInCount DESC) AS RankNum
    FROM HourlyCheckInStats
) rankedStats
WHERE RankNum = 1
ORDER BY [Date]

方法2：关联子查询实现（兼容老版本SQL Server） #

如果使用的SQL Server版本不支持窗口函数，可以通过HAVING子句关联匹配每日最高打卡量实现：

SELECT 
    CONVERT(DATE,CheckInDateTime) AS [Date]
    , DATEPART(HOUR, CheckInDateTime) AS [Hour]
    , COUNT(*) AS CheckInCount
FROM [dbo].[CheckIns] main
GROUP BY CONVERT(DATE,CheckInDateTime), DATEPART(HOUR, CheckInDateTime)
HAVING COUNT(*) = (
    SELECT TOP 1 COUNT(*)
    FROM [dbo].[CheckIns]
    WHERE CONVERT(DATE,CheckInDateTime) = CONVERT(DATE, main.CheckInDateTime)
    GROUP BY DATEPART(HOUR, CheckInDateTime)
    ORDER BY COUNT(*) DESC
)
ORDER BY [Date]

内容的提问来源于stack exchange，提问作者Anthony