如何实现支持多周跨度的用户登录时长分组报表查询？

嘿，这个需求我之前帮同事处理过，咱们来把这个冗余的查询彻底优化掉，同时满足任意日期范围的需求！

首先得说，你之前用的多个子查询确实太冗余了，而且WHILE循环确实不是SQL里处理这类问题的最佳实践——SQL是集合式语言，用集合操作比循环高效得多。下面给你一套简洁且可扩展的方案：

核心思路 #

1. 先把每个登录会话映射到它所属的周（自动计算周的起始和结束日期）
2. 按「用户+周+周内日期」聚合登录时长
3. 用PIVOT把每日的聚合结果转成Day1到Day7的列（替代原来的7个子查询）
4. 关联用户表获取用户名，最后格式化周范围的显示文本

完整实现代码 #

-- 定义用户选择的日期范围参数
DECLARE @StartDate DATE = '2018-05-01', @EndDate DATE = '2018-06-01';

WITH WeeklySessions AS (
    -- 第一步：给每个会话标记所属的周信息和周内天数
    SELECT 
        si.UserID,
        -- 计算周起始日（这里默认周日为一周第一天，若要周一请看下面的说明）
        WeekStart = DATEADD(DAY, -(DATEPART(dw, si.DateLoggedInOn) - 1), si.DateLoggedInOn),
        -- 计算周结束日（起始日+6天）
        WeekEnd = DATEADD(DAY, 6, DATEADD(DAY, -(DATEPART(dw, si.DateLoggedInOn) - 1), si.DateLoggedInOn)),
        -- 标记该日期是周内的第几天（Day1到Day7）
        DayOfWeek = 'Day' + CAST(DATEPART(dw, si.DateLoggedInOn) AS VARCHAR(1)),
        si.MinutesLoggedInFor
    FROM SessionInfo si
    WHERE si.DateLoggedInOn BETWEEN @StartDate AND @EndDate
),
WeeklyAggregated AS (
    -- 第二步：按用户、周、周内日期聚合总时长
    SELECT 
        UserID,
        WeekStart,
        WeekEnd,
        DayOfWeek,
        TotalMinutes = SUM(MinutesLoggedInFor)
    FROM WeeklySessions
    GROUP BY UserID, WeekStart, WeekEnd, DayOfWeek
)
-- 第三步：用PIVOT转置成Day1-Day7列，关联用户表输出最终结果
SELECT 
    u.UserName,
    u.UserID,
    -- 格式化周范围显示（比如：5/13/18 –5/19/18）
    ReportingWeek = CONVERT(VARCHAR, wa.WeekStart, 101) + ' – ' + CONVERT(VARCHAR, wa.WeekEnd, 101),
    -- 用ISNULL处理无登录的天数，显示0而非NULL
    ISNULL(wa.[Day1], 0) AS Day1,
    ISNULL(wa.[Day2], 0) AS Day2,
    ISNULL(wa.[Day3], 0) AS Day3,
    ISNULL(wa.[Day4], 0) AS Day4,
    ISNULL(wa.[Day5], 0) AS Day5,
    ISNULL(wa.[Day6], 0) AS Day6,
    ISNULL(wa.[Day7], 0) AS Day7
FROM User u
-- LEFT JOIN保证所有用户都能显示（即使某周无登录记录）
LEFT JOIN (
    SELECT *
    FROM WeeklyAggregated
    PIVOT (
        SUM(TotalMinutes)
        FOR DayOfWeek IN ([Day1], [Day2], [Day3], [Day4], [Day5], [Day6], [Day7])
    ) AS PivotTable
) wa ON u.UserID = wa.UserID
-- 如果只想显示有登录记录的用户，取消下面的注释
-- WHERE wa.UserID IS NOT NULL
ORDER BY u.UserID, wa.WeekStart;

关键细节调整 #

• 周起始日修改：如果你的业务里周是从周一开始，把WeekStart的计算改成：

  WeekStart = DATEADD(DAY, -(DATEPART(dw, si.DateLoggedInOn) - 2), si.DateLoggedInOn),
  

  同时DayOfWeek要改成：

  DayOfWeek = 'Day' + CAST(DATEPART(dw, si.DateLoggedInOn) - 1 AS VARCHAR(1)),
  

• 日期格式：CONVERT(VARCHAR, wa.WeekStart, 101)是美式日期格式（MM/DD/YY），如果需要其他格式，替换101为对应的格式码（比如103是DD/MM/YY）。

方案优势 #

• 彻底消除冗余代码，逻辑清晰易维护
• 完全基于集合操作，性能远优于WHILE循环
• 支持任意日期范围，自动拆分所有涉及的周
• 灵活控制是否显示无登录记录的用户/周

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