基于日历表的过去8周支持工单周环比SQL查询需求

没问题，我来帮你搞定这个周环比统计！既然已经有了dbo.Calendar日历表，咱们可以借助它精准锚定每周的时间范围，再关联工单表统计数量，最后算出周环比数据。

核心思路 #

咱们分三步来实现：

1. 从日历表筛选过去8周的日期，按周分组后关联工单表，统计每周的工单数量；
2. 用窗口函数获取上一周的工单量，计算环比变化率；
3. 格式化输出结果，让数据更直观易读。

完整SQL查询 #

假设你的工单表是dbo.SupportTickets，工单ID字段为TicketID，创建日期字段为CreatedDate（建议用UTC时间，和日历表的calendar_DT时区保持一致），直接用下面的查询即可：

WITH WeeklyTicketCounts AS (
    -- 第一步：统计每周的工单数量
    SELECT
        DATEPART(iso_week, c.calendar_DT) AS WeekNumber,
        DATEPART(year, c.calendar_DT) AS YearNumber,
        -- 取每周的周一作为周起始日期（可根据业务需求调整）
        DATEADD(day, 1 - DATEPART(weekday, c.calendar_DT), MIN(c.calendar_DT)) AS WeekStartDate,
        -- 取每周的周日作为周结束日期
        DATEADD(day, 7 - DATEPART(weekday, c.calendar_DT), MAX(c.calendar_DT)) AS WeekEndDate,
        -- LEFT JOIN确保无工单的周也会显示0
        COUNT(t.TicketID) AS TicketCount
    FROM dbo.Calendar c
    LEFT JOIN dbo.SupportTickets t 
        ON CONVERT(date, t.CreatedDate) = c.calendar_DT
    WHERE 
        c.calendar_DT >= DATEADD(week, -8, GETUTCDATE() - 1) 
        AND c.calendar_DT <= GETUTCDATE()
    GROUP BY DATEPART(iso_week, c.calendar_DT), DATEPART(year, c.calendar_DT)
),
WeeklyWithComparison AS (
    -- 第二步：获取上周数据并计算环比
    SELECT
        YearNumber,
        WeekNumber,
        WeekStartDate,
        WeekEndDate,
        TicketCount,
        -- 用LAG函数获取上一周的工单数量
        LAG(TicketCount) OVER (ORDER BY YearNumber, WeekNumber) AS PreviousWeekTickets,
        -- 计算环比变化百分比，处理除数为0的特殊情况
        CASE 
            WHEN LAG(TicketCount) OVER (ORDER BY YearNumber, WeekNumber) = 0 THEN NULL
            ELSE ROUND(
                (CAST(TicketCount AS FLOAT) - LAG(TicketCount) OVER (ORDER BY YearNumber, WeekNumber)) 
                / LAG(TicketCount) OVER (ORDER BY YearNumber, WeekNumber) * 100, 
                2
            )
        END AS WeekOverWeekChangePercent
    FROM WeeklyTicketCounts
)
-- 第三步：格式化输出结果
SELECT
    YearNumber,
    WeekNumber,
    CONCAT('Week ', WeekNumber, ' (', FORMAT(WeekStartDate, 'yyyy-MM-dd'), ' to ', FORMAT(WeekEndDate, 'yyyy-MM-dd'), ')') AS WeekRange,
    TicketCount AS CurrentWeekTotal,
    ISNULL(PreviousWeekTickets, 0) AS PreviousWeekTotal,
    CASE 
        WHEN WeekOverWeekChangePercent IS NULL THEN 'N/A'
        WHEN WeekOverWeekChangePercent > 0 THEN CONCAT('+', WeekOverWeekChangePercent, '%')
        ELSE CONCAT(WeekOverWeekChangePercent, '%')
    END AS WeekOverWeekChange
FROM WeeklyWithComparison
ORDER BY YearNumber DESC, WeekNumber DESC;

关键细节说明 #

• 周定义调整：上面用的是ISO周（周一为一周起始），如果你的业务是周日开始算一周，可以把DATEPART(iso_week, ...)改成DATEPART(week, ...)，记得结合年份分组，避免跨年周的混淆。
• 时区一致性：如果工单表的CreatedDate是本地时间，记得先转成UTC再关联，比如用CONVERT(date, SWITCHOFFSET(t.CreatedDate, '+00:00'))转换。
• 无工单周处理：用LEFT JOIN关联日历表，保证即使某一周没有工单，也会在结果中显示该周，工单数量为0，不会出现数据缺失。
• 环比容错处理：针对上周工单量为0的情况做了特殊处理，避免除以0的报错，同时格式化百分比显示，让结果更友好。

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