基于ShopOrder表的最近月份每日有效店铺数统计查询需求

API 探路者

2026-5-26

解决ShopOrder表月度每日有效店铺数统计问题

搞定这个需求其实不难，核心是先把目标月份（2018年4月）的每一天都生成出来作为日期维度，再和你的ShopOrder表关联，统计每个日期下满足条件的去重店铺数（毕竟一个店铺可能有多个订单，只要有一个订单覆盖当天就算有效）。

核心思路

生成日期序列：构造2018年4月1日到4月30日的所有日期，确保哪怕当天没有有效店铺也能显示（值为0）
关联订单表：将每个日期与ShopOrder表匹配，筛选出starttime ≤ 当天日期 ≤ endtime的订单记录
分组统计：按日期分组，统计去重后的shopid数量

分数据库实现代码

MySQL 8.0+（支持递归CTE）

WITH april_dates AS (
    SELECT DATE('2018-04-01') AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM april_dates
    WHERE day < DATE('2018-04-30')
)
SELECT 
    ad.day,
    COUNT(DISTINCT so.shopid) AS count
FROM april_dates ad
LEFT JOIN ShopOrder so 
    ON ad.day BETWEEN so.starttime AND so.endtime
GROUP BY ad.day
ORDER BY ad.day;

MySQL 5.x（不支持CTE）

如果你的MySQL版本较低，用数字序列生成日期：

SELECT 
    DATE('2018-04-01') + INTERVAL (n-1) DAY AS day,
    COUNT(DISTINCT so.shopid) AS count
FROM (
    SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL
    SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL
    SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL
    SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL
    SELECT 19 UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL
    SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24 UNION ALL
    SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL
    SELECT 28 UNION ALL SELECT 29 UNION ALL SELECT 30
) AS numbers
LEFT JOIN ShopOrder so 
    ON (DATE('2018-04-01') + INTERVAL (n-1) DAY) BETWEEN so.starttime AND so.endtime
GROUP BY day
ORDER BY day;

PostgreSQL

PostgreSQL有内置的日期生成函数，写法更简洁：

SELECT 
    generate_series('2018-04-01'::date, '2018-04-30'::date, '1 day') AS day,
    COUNT(DISTINCT so.shopid) AS count
FROM generate_series('2018-04-01'::date, '2018-04-30'::date, '1 day') ad(day)
LEFT JOIN ShopOrder so 
    ON ad.day BETWEEN so.starttime AND so.endtime
GROUP BY ad.day
ORDER BY ad.day;

SQL Server

SQL Server的递归CTE需要开启MAXRECURSION选项（因为递归次数超过默认的100）：

WITH april_dates AS (
    SELECT CAST('2018-04-01' AS DATE) AS day
    UNION ALL
    SELECT DATEADD(DAY, 1, day)
    FROM april_dates
    WHERE day < CAST('2018-04-30' AS DATE)
)
SELECT 
    ad.day,
    COUNT(DISTINCT so.shopid) AS count
FROM april_dates ad
LEFT JOIN ShopOrder so 
    ON ad.day BETWEEN so.starttime AND so.endtime
GROUP BY ad.day
ORDER BY ad.day
OPTION (MAXRECURSION 0);

结果验证

用你提供的测试数据，运行后会得到符合预期的结果：

2018-04-27：2个有效店铺（shop123、shop234）
2018-04-26：1个有效店铺（shop234）
4月1日-22日：0个有效店铺
4月28日：2个有效店铺
4月29日-30日：1个有效店铺

内容的提问来源于stack exchange，提问作者yonney.yang