如何用SUM+CASE语句实现每月工单队列（新增/关闭/存量）统计？

核心思路 #

要实现月度工单队列统计，关键是先构建月度维度表，再通过SUM(CASE WHEN...)对每个月份分别计算新增、关闭、期初/期末未结工单量。以下是基于你提供的示例数据的完整实现：

完整SQL示例（以PostgreSQL为例） #

-- 1. 生成需要统计的月度维度表
WITH months AS (
    SELECT 
        DATE_TRUNC('month', dt) AS month_start,
        TO_CHAR(DATE_TRUNC('month', dt), 'Mon') AS month_name
    FROM (
        -- 从工单数据中提取所有涉及的月份（包括创建和关闭的月份）
        SELECT opened_at AS dt FROM tickets
        UNION
        SELECT closed_at AS dt FROM tickets WHERE closed_at IS NOT NULL
    ) AS all_dates
    GROUP BY DATE_TRUNC('month', dt)
    ORDER BY month_start
),
-- 2. 计算每个月份的核心指标
monthly_metrics AS (
    SELECT
        m.month_name AS month,
        -- 月初未结工单：创建时间早于当月第一天，且未关闭/关闭时间晚于当月第一天
        SUM(CASE 
            WHEN t.opened_at < m.month_start 
            AND (t.closed_at IS NULL OR t.closed_at >= m.month_start) 
            THEN 1 ELSE 0 
        END) AS starting_queue,
        -- 当月新增工单：创建时间在当月内
        SUM(CASE WHEN DATE_TRUNC('month', t.opened_at) = m.month_start THEN 1 ELSE 0 END) AS new_tickets,
        -- 当月关闭工单：关闭时间在当月内且不为空
        SUM(CASE 
            WHEN t.closed_at IS NOT NULL 
            AND DATE_TRUNC('month', t.closed_at) = m.month_start 
            THEN 1 ELSE 0 
        END) AS tickets_closed
    FROM months m
    CROSS JOIN tickets t
    GROUP BY m.month_start, m.month_name
)
-- 3. 计算月末未结工单，并整理最终输出
SELECT
    month,
    starting_queue,
    new_tickets,
    tickets_closed,
    -- 月末未结 = 期初未结 + 当月新增 - 当月关闭
    starting_queue + new_tickets - tickets_closed AS ending_queue
FROM monthly_metrics
ORDER BY month_start;

关键逻辑解释 #

• 月度维度表：通过UNION提取所有创建和关闭工单的日期，再按月份聚合，确保不会遗漏任何有工单变动的月份。
• starting_queue：判断工单在当月初是否处于未结状态——创建时间早于当月开始，且要么还没关闭，要么关闭时间在当月或之后。
• new_tickets：简单匹配创建时间落在当月的工单数量。
• tickets_closed：匹配关闭时间落在当月且不为空的工单数量。
• ending_queue：通过期初+新增-关闭的公式计算，避免重复统计，效率更高。

适配其他数据库的调整 #

如果使用MySQL，可以将DATE_TRUNC('month', dt)替换为DATE_FORMAT(dt, '%Y-%m-01')，TO_CHAR替换为DATE_FORMAT(m.month_start, '%b')；SQL Server则使用DATEFROMPARTS(YEAR(dt), MONTH(dt), 1)来获取当月第一天。

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