如何按周统计未结工单数量？需回溯12个月并结合日历表

嘿，这个需求我之前做过类似的，结合现成的日历表来实现确实是最靠谱的方案，尤其是要精准卡每周周五17:00的截止时间，完全能满足你回溯52周的要求。下面给你拆解具体的实现步骤和细节：

首先得明确两个关键判定规则：

• 未结工单：solvedate IS NULL（一直没解决），或者solvedate > 当周周五17:00（在当周截止后才解决的，也算当周未结）
• 周的范围：每周从周一开始，到周五17:00结束——也就是说，所有在这个时间点前已创建、且到点还没解决的工单，都归到这一周统计

假设你的日历表名为calendar，包含date字段（每条记录对应一个自然日），我们可以分两步来做：

1. 从日历表生成过去52周的时间窗口 #

先从日历表中筛选出过去52周的所有周五，然后生成对应的周起始（周一）和周截止时间（周五17:00）。这里要注意不同数据库的日期函数差异，我用SQL Server的语法举例子，你可以根据自己用的数据库调整：

WITH weekly_windows AS (
    SELECT
        -- 计算周起始日期（周一）：周五往前推4天
        DATEADD(DAY, -4, c.date) AS week_start_date,
        -- 生成周截止时间：周五的17:00整
        DATETIMEFROMPARTS(YEAR(c.date), MONTH(c.date), DAY(c.date), 17, 0, 0, 0) AS week_cutoff_datetime
    FROM calendar c
    WHERE
        -- 筛选周五（SQL Server中DATEPART(weekday, date)=6代表周五，也可以用DATENAME(weekday, date)='Friday'避免语言设置影响）
        DATEPART(WEEKDAY, c.date) = 6
        -- 取过去52周的周五，确保覆盖需求的回溯范围
        AND c.date >= DATEADD(WEEK, -52, CAST(GETDATE() AS DATE))
        AND c.date <= CAST(GETDATE() AS DATE)
)

如果是PostgreSQL，调整后的时间窗口CTE大概是这样：

WITH weekly_windows AS (
    SELECT
        -- 周起始（周一）：PostgreSQL默认周起始是周日，给周五加1天后截断到周，再减1天得到周一
        DATE_TRUNC('week', c.date + INTERVAL '1 day') - INTERVAL '1 day' AS week_start_date,
        -- 周截止时间：周五17:00
        c.date + INTERVAL '17 hours' AS week_cutoff_datetime
    FROM calendar c
    WHERE
        -- 筛选周五（WEEKDAY=4代表周五）
        EXTRACT(DOW FROM c.date) = 4
        AND c.date >= CURRENT_DATE - INTERVAL '52 weeks'
        AND c.date <= CURRENT_DATE
)

2. 关联工单表统计未结数据 #

接下来把生成的周窗口和工单表关联，按照我们之前明确的规则筛选未结工单，然后按周聚合统计：

SELECT
    w.week_start_date,
    w.week_cutoff_datetime,
    COUNT(DISTINCT t.ticketID) AS unresolved_ticket_count,
    -- 如果需要列出具体工单ID，用STRING_AGG（SQL Server）或者GROUP_CONCAT（MySQL）等函数
    STRING_AGG(t.ticketID, ', ') AS unresolved_ticket_ids
FROM weekly_windows w
LEFT JOIN tickets t
    ON t.createdate <= w.week_cutoff_datetime  -- 工单在当周截止前已创建
    AND (t.solvedate IS NULL OR t.solvedate > w.week_cutoff_datetime)  -- 到截止时间仍未解决
GROUP BY w.week_start_date, w.week_cutoff_datetime
ORDER BY w.week_start_date DESC;  -- 按周倒序，最新的周在前

• 数据库函数适配：不同数据库的日期处理函数差异很大，比如MySQL的WEEKDAY()、Oracle的TRUNC()等，一定要根据自己的数据库调整代码里的函数。
• 日历表完整性：确保你的日历表包含过去52周的所有日期，尤其是每个周五的记录，不然会漏掉对应的周统计。如果日历表有现成的week_start、week_end字段，直接用会更高效。
• 跨周工单的判定：如果工单是在某周周五17:00之后创建的，它会自动被归到下一周的统计里，因为它不满足createdate <= 当周截止时间的条件，这符合你的需求。
• 已解决工单的回溯：对于那些后来被解决的工单，只要它的解决时间晚于当周截止时间，就会被计入该周的未结统计，这完全符合你“周五17:00前仍未解决”的定义。

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