如何使用SQL每周自动统计工单数量并存储至另一数据集？

嘿，这个需求其实挺常见的，分两步就能搞定：先写对统计每周工单的SQL，再配置定时任务让它自动跑并把结果存到目标数据集里。我给你一步步拆解清楚：

首先得明确每周结束节点（比如周日或周六），不同数据库的日期处理函数略有差异，我给你几个主流数据库的示例：

假设源工单表是work_order，核心字段是create_time（工单创建时间）；目标统计表是weekly_work_order_stats，结构为stat_week_end_date（每周结束日期，作为主键）、total_orders（当期工单总数）。

MySQL 示例 #

-- 先确保目标统计表存在（首次执行）
CREATE TABLE IF NOT EXISTS weekly_work_order_stats (
    stat_week_end_date DATE PRIMARY KEY,
    total_orders INT NOT NULL
);

-- 统计上周工单并插入/更新统计结果（周日作为周结束日）
INSERT INTO weekly_work_order_stats (stat_week_end_date, total_orders)
SELECT 
    DATE_ADD(DATE(create_time), INTERVAL (6 - WEEKDAY(create_time)) DAY) AS week_end_date,
    COUNT(*) AS total_orders
FROM work_order
-- 限制统计范围为上周，避免全表扫描（可选，数据量大时建议加）
WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
GROUP BY week_end_date
-- 若同一周已有统计数据，自动更新总数（防止重复执行导致数据重复）
ON DUPLICATE KEY UPDATE total_orders = VALUES(total_orders);

PostgreSQL 示例 #

-- 创建目标统计表（首次执行）
CREATE TABLE IF NOT EXISTS weekly_work_order_stats (
    stat_week_end_date DATE PRIMARY KEY,
    total_orders INT NOT NULL
);

-- 统计上周工单并插入/更新统计结果（周日作为周结束日）
INSERT INTO weekly_work_order_stats (stat_week_end_date, total_orders)
SELECT 
    DATE_TRUNC('week', create_time) + INTERVAL '6 days' AS week_end_date,
    COUNT(*) AS total_orders
FROM work_order
WHERE create_time >= CURRENT_DATE - INTERVAL '1 week'
GROUP BY week_end_date
-- 主键冲突时更新数据
ON CONFLICT (stat_week_end_date) DO UPDATE SET total_orders = EXCLUDED.total_orders;

SQL Server 示例 #

-- 创建目标统计表（首次执行）
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'weekly_work_order_stats')
CREATE TABLE weekly_work_order_stats (
    stat_week_end_date DATE PRIMARY KEY,
    total_orders INT NOT NULL
);

-- 统计上周工单并插入/更新统计结果（周日作为周结束日）
MERGE INTO weekly_work_order_stats AS target
USING (
    SELECT 
        DATEADD(day, 7 - DATEPART(weekday, create_time), CONVERT(DATE, create_time)) AS week_end_date,
        COUNT(*) AS total_orders
    FROM work_order
    WHERE create_time >= DATEADD(week, -1, GETDATE())
    GROUP BY DATEADD(day, 7 - DATEPART(weekday, create_time), CONVERT(DATE, create_time))
) AS source
ON target.stat_week_end_date = source.week_end_date
WHEN MATCHED THEN UPDATE SET target.total_orders = source.total_orders
WHEN NOT MATCHED THEN INSERT (stat_week_end_date, total_orders)
VALUES (source.week_end_date, source.total_orders);

注意：如果你的业务定义周结束日是周六，只需调整日期函数的偏移量即可（比如MySQL中把6 - WEEKDAY(create_time)改成5 - WEEKDAY(create_time)）。

接下来要让上面的SQL每周自动跑，不同数据库的定时工具不同：

MySQL：用事件调度器 #

首先确保事件调度器开启：

SET GLOBAL event_scheduler = ON;

然后创建每周日23:59执行的事件：

CREATE EVENT IF NOT EXISTS weekly_work_order_stat_job
ON SCHEDULE EVERY 1 WEEK
STARTS '2024-05-19 23:59:00' -- 替换成最近的周日时间
DO
BEGIN
    -- 把上面的MySQL统计SQL复制到这里
    INSERT INTO weekly_work_order_stats (stat_week_end_date, total_orders)
    SELECT 
        DATE_ADD(DATE(create_time), INTERVAL (6 - WEEKDAY(create_time)) DAY) AS week_end_date,
        COUNT(*) AS total_orders
    FROM work_order
    WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)
    GROUP BY week_end_date
    ON DUPLICATE KEY UPDATE total_orders = VALUES(total_orders);
END;

PostgreSQL：用pg_cron扩展 #

先安装pg_cron（需要超级用户权限）：

CREATE EXTENSION pg_cron;

然后创建每周日23:59的定时任务：

SELECT cron.schedule(
    'weekly-work-order-stat', -- 任务名称
    '59 23 * * 0', -- Cron表达式：分 时 日 月 周（0代表周日）
    $$
        -- 把上面的PostgreSQL统计SQL复制到这里
        INSERT INTO weekly_work_order_stats (stat_week_end_date, total_orders)
        SELECT 
            DATE_TRUNC('week', create_time) + INTERVAL '6 days' AS week_end_date,
            COUNT(*) AS total_orders
        FROM work_order
        WHERE create_time >= CURRENT_DATE - INTERVAL '1 week'
        GROUP BY week_end_date
        ON CONFLICT (stat_week_end_date) DO UPDATE SET total_orders = EXCLUDED.total_orders;
    $$
);

SQL Server：用SQL Server Agent作业 #

1. 打开SQL Server Management Studio（SSMS），找到左侧的SQL Server Agent → 作业，右键「新建作业」。
2. 给作业命名（比如「每周工单统计」），切换到「步骤」选项卡，新建步骤：类型选「Transact-SQL脚本(T-SQL)」，选择目标数据库，把上面的SQL Server统计脚本粘贴到命令框。
3. 切换到「计划」选项卡，新建计划：频率选「每周」，选择周日，执行时间设为23:59，保存即可。

云数据库场景 #

如果用的是阿里云RDS、AWS RDS等云数据库，直接在控制台找「定时任务」「事件调度」这类功能，可视化配置执行时间（每周日23:59）和要运行的SQL即可，不用写命令行，更省心。

• 权限问题：执行定时任务的账号需要有「插入/更新目标表」「创建事件/作业」的权限，提前给账号赋权。
• 数据准确性：用ON DUPLICATE KEY/ON CONFLICT/MERGE避免同一周数据重复插入，即使任务意外重复执行也不会出错。
• 性能优化：如果工单表数据量很大，一定要加时间范围过滤（比如只统计上周），避免全表扫描拖慢数据库。

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