如何根据日期字段按周统计订单各状态的对应数量

实现思路 #

• 第一步：通过WITH子句生成覆盖统计时间范围的周维度基线数据，包含每周起始日期、结束日期、周编号
• 第二步：按日期统计每天的新开、发送、关闭三类订单数量
• 第三步：将日度统计结果关联到周维度表，聚合得到周维度的统计结果

代码实现（以MySQL 8.0+为例，支持WITH语法） #

WITH RECURSIVE date_series AS (
    -- 生成统计时间范围内的所有连续日期，可按需调整起止日期
    SELECT '2019-01-01' AS cur_date
    UNION ALL
    SELECT DATE_ADD(cur_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE cur_date < '2019-12-31'
),
week_dim AS (
    -- 生成周维度表，周规则可按需调整，当前规则与样例对齐
    SELECT 
        MIN(cur_date) AS StartOfWeek,
        MAX(cur_date) AS EndOfWeek,
        CONCAT(YEAR(cur_date), 'W', LPAD(WEEKOFYEAR(cur_date), 2, '0')) AS WeekNumber
    FROM date_series
    GROUP BY YEAR(cur_date), WEEKOFYEAR(cur_date)
),
order_daily_stats AS (
    -- 按日期统计三类订单的日度数量
    SELECT 
        dt,
        SUM(open_cnt) AS open_cnt,
        SUM(send_cnt) AS send_cnt,
        SUM(close_cnt) AS close_cnt
    FROM (
        SELECT opening_date AS dt, COUNT(DISTINCT order_id) AS open_cnt, 0 AS send_cnt, 0 AS close_cnt FROM 你的业务表名 GROUP BY opening_date
        UNION ALL
        SELECT sending_date AS dt, 0 AS open_cnt, COUNT(DISTINCT order_id) AS send_cnt, 0 AS close_cnt FROM 你的业务表名 GROUP BY sending_date
        UNION ALL
        SELECT closing_date AS dt, 0 AS open_cnt, 0 AS send_cnt, COUNT(DISTINCT order_id) AS close_cnt FROM 你的业务表名 GROUP BY closing_date
    ) t
    GROUP BY dt
)
-- 关联周维度与日度统计得到最终结果
SELECT 
    w.StartOfWeek,
    w.EndOfWeek,
    w.WeekNumber,
    IFNULL(SUM(os.open_cnt), 0) AS order_opened,
    IFNULL(SUM(os.send_cnt), 0) AS order_send,
    IFNULL(SUM(os.close_cnt), 0) AS order_closed
FROM week_dim w
LEFT JOIN order_daily_stats os ON os.dt BETWEEN w.StartOfWeek AND w.EndOfWeek
GROUP BY w.StartOfWeek, w.EndOfWeek, w.WeekNumber
ORDER BY w.StartOfWeek;

注意事项 #

• 代码中你的业务表名需要替换为实际业务表的名称
• 如果周计算规则与需求不一致，可调整WEEK相关函数的参数，比如设置周日为一周起始，或者调整年度第一周的判定规则
• 如果已有现成的日期维度表，可直接替换date_series和week_dim部分的逻辑，直接读取现有维度表即可
• 可根据需求修改date_series中的起止日期，适配不同的统计时间范围

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