月度至少下单1次的年度活跃用户总数计算SQL返回0求助

嘿，我一眼就看出你SQL里的问题啦——你用AND把多个互斥的日期区间连在一起，这相当于要求一条订单的创建日期同时属于2月、3月、4月……这些完全不重叠的时间段，这根本不可能嘛，所以返回0是必然的结果。

你的需求是统计「年度内每月至少下单1次的活跃用户总数」，也就是要找出那些在每个目标月份都有订单记录的用户。下面给你两种靠谱的修正方案：

方案1：分组统计+筛选全月份覆盖用户（推荐，扩展性强） #

这种方法先提取每个用户有订单的月份，再筛选出覆盖所有目标月份的用户，最后统计总数：

SELECT COUNT(DISTINCT user_months.CreatedBy) AS monthly_active_users
FROM (
    -- 第一步：获取每个用户在哪些月份有过订单
    SELECT 
        CreatedBy,
        DATE_FORMAT(CreatedDate, '%Y-%m') AS order_month
    FROM orders
    -- 先限定整体的时间范围（比如2017年2月到5月）
    WHERE CreatedDate >= '2017-02-01' AND CreatedDate < '2017-06-01'
    -- 按用户+月份去重，确保每个用户每个月份只算一次
    GROUP BY CreatedBy, DATE_FORMAT(CreatedDate, '%Y-%m')
) AS user_months
-- 分组筛选：只保留拥有所有目标月份记录的用户（这里是4个月份：2017-02至2017-05）
GROUP BY user_months.CreatedBy
HAVING COUNT(user_months.order_month) = 4;

方案2：多EXISTS子查询（逻辑直观，适合月份少的场景） #

如果目标月份数量不多，用多个EXISTS子查询逐个验证用户在每个月份都有订单，逻辑更直白：

SELECT COUNT(DISTINCT o.CreatedBy) AS monthly_active_users
FROM orders o
-- 验证用户在2017年2月有订单
WHERE EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.CreatedBy = o.CreatedBy 
      AND o2.CreatedDate >= '2017-02-01' AND o2.CreatedDate < '2017-03-01'
)
-- 验证用户在2017年3月有订单
AND EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.CreatedBy = o.CreatedBy 
      AND o2.CreatedDate >= '2017-03-01' AND o2.CreatedDate < '2017-04-01'
)
-- 验证用户在2017年4月有订单
AND EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.CreatedBy = o.CreatedBy 
      AND o2.CreatedDate >= '2017-04-01' AND o2.CreatedDate < '2017-05-01'
)
-- 验证用户在2017年5月有订单
AND EXISTS (
    SELECT 1 
    FROM orders o2 
    WHERE o2.CreatedBy = o.CreatedBy 
      AND o2.CreatedDate >= '2017-05-01' AND o2.CreatedDate < '2017-06-01'
);

关键提醒 #

• 如果你要统计一整年的用户，只需要调整方案1中的时间范围为'2017-01-01' AND CreatedDate < '2018-01-01'，同时把HAVING COUNT(...)的数值改成12就行。
• 方案1的性能在大数据集上通常更好，因为只需要扫描一次订单表；方案2适合快速验证逻辑，月份多的话写起来会比较繁琐。

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