如何在SQL中组织工单系统的每日状态统计数据？

针对你的需求，推荐设计每日工单统计快照表，把每日各队列各状态的工单数量预先存储起来，既避免重复查询原工单表的低效问题，又能让后续数据提取和绘图变得非常便捷。

核心表结构设计 #

CREATE TABLE daily_ticket_stats (
    stat_id INT AUTO_INCREMENT PRIMARY KEY,
    stat_date DATE NOT NULL, -- 统计日期
    queue_id INT NOT NULL, -- 关联现有队列表的ID（如果有独立队列表）
    queue_name VARCHAR(100) NOT NULL, -- 冗余队列名称，避免频繁关联查询
    ticket_status VARCHAR(50) NOT NULL, -- 工单状态：New/Open/Pending，用VARCHAR方便后续扩展状态
    ticket_count INT NOT NULL DEFAULT 0, -- 对应状态的工单数量
    -- 唯一约束：确保同一天同一个队列同一个状态只会有一条记录
    UNIQUE KEY idx_daily_queue_status (stat_date, queue_id, ticket_status)
);

结构说明 #

• 用stat_date存储统计日期，确保每日数据按日期归档
• 同时保留queue_id和queue_name：如果后续队列名称修改，通过queue_id能关联到最新名称，而冗余queue_name可以直接提取数据用于绘图，不用额外关联查询
• ticket_status用VARCHAR而非ENUM：虽然ENUM更紧凑，但如果未来要新增状态（比如Closed），VARCHAR不需要修改表结构，灵活性更高
• 唯一约束防止重复插入同一天的同一队列状态数据，支持后续定时任务的幂等更新

数据写入优化（替代165次查询） #

不用每天执行165次SELECT，只需要一个定时任务（比如服务器cron、系统定时任务），执行一次批量统计SQL就能把当天所有队列所有状态的统计结果写入/更新到快照表：

INSERT INTO daily_ticket_stats (stat_date, queue_id, queue_name, ticket_status, ticket_count)
SELECT 
    CURDATE(),
    q.queue_id,
    q.queue_name,
    ts.status,
    COUNT(t.ticket_id)
FROM 
    queues q -- 假设你有独立的队列表，存储所有队列信息
CROSS JOIN 
    (SELECT 'New' AS status UNION SELECT 'Open' UNION SELECT 'Pending') ts
LEFT JOIN 
    tickets t 
    ON t.queue_id = q.queue_id 
    AND t.status = ts.status 
    AND DATE(t.updated_at) = CURDATE() -- 这里根据业务逻辑调整：统计当天状态为对应值的工单用updated_at，统计当天创建的用created_at
GROUP BY 
    q.queue_id, q.queue_name, ts.status
ON DUPLICATE KEY UPDATE ticket_count = VALUES(ticket_count);

这个SQL会自动遍历所有队列和所有状态，就算后续队列增加到100个，也不需要修改代码，一次执行就能完成所有统计。

数据提取与绘图 #

因为表结构是扁平化的，各种绘图需求都能快速实现：

• 提取单个队列的所有状态趋势：

SELECT stat_date, ticket_status, ticket_count
FROM daily_ticket_stats
WHERE queue_name = '客户支持队列'
ORDER BY stat_date;

• 提取所有队列的某一种状态趋势：

SELECT stat_date, queue_name, ticket_count
FROM daily_ticket_stats
WHERE ticket_status = 'Pending'
ORDER BY stat_date, queue_name;

这些结果可以直接导出到Python、BI工具里生成折线图，完全不需要额外的数据整理。

额外优化建议 #

• 给表添加索引：CREATE INDEX idx_stat_date_queue ON daily_ticket_stats(stat_date, queue_id);，加快按日期和队列查询的速度
• 如果不需要保留太久的历史数据，可以定期清理（比如保留最近6个月），避免表数据量过大

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