SQL实现SUMIFS逻辑统计订单日期范围内每日成人与儿童数量

前提假设 #

先明确订单表结构如下，你可以根据自己的实际表名和字段名调整：

• 表名：orders
• 核心字段：
  • start_date：订单生效开始日期，DATE类型
  • end_date：订单生效结束日期，DATE类型
  • adult_cnt：订单对应成人数量
  • child_cnt：订单对应儿童数量

实现逻辑 #

1. 生成统计周期内的连续自然日序列，作为统计的主维度
2. 将自然日序列和订单表关联，关联条件为 自然日 >= 订单开始日期 AND 自然日 <= 订单结束日期
3. 按自然日分组聚合，求和对应成人、儿童总人数即可

通用SQL实现（支持MySQL8.0+/PostgreSQL/Oracle等支持递归CTE的数据库） #

WITH RECURSIVE date_range AS (
    -- 这里定义统计的起始日期和结束日期，按需修改
    SELECT '2021-09-01'::DATE AS stat_date
    UNION ALL
    SELECT stat_date + INTERVAL '1 day'
    FROM date_range
    -- 统计结束日期按需修改
    WHERE stat_date < '2021-09-30'
)
SELECT 
    dr.stat_date,
    SUM(o.adult_cnt) AS total_adult,
    SUM(o.child_cnt) AS total_child
FROM date_range dr
LEFT JOIN orders o
    ON dr.stat_date BETWEEN o.start_date AND o.end_date
-- 过滤掉没有有效订单的日期可以加下面这行，不需要就删掉
-- WHERE o.order_id IS NOT NULL
GROUP BY dr.stat_date
ORDER BY dr.stat_date;

特殊数据库适配说明 #

• 如果是MySQL5.x版本不支持递归CTE，可以提前构建一张永久的日期维度表dim_date，存储所有需要用到的自然日，替换上面SQL里的date_range即可
• 如果是Hive/Spark SQL，可以使用posexplode函数生成日期序列，不需要递归

结果验证 #

你提到的2021年9月3日的场景，假设对应4个订单的生效区间都包含2021-09-03，SQL会自动累加4个订单的成人数量得到10，完全符合需求。

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