基于2017年前后拆分订单表 按月统计上月未下单用户数咨询

• 先统一处理用户历史下单标识：2017年之前有过下单记录的用户，统一存入历史用户池
• 对Table2做去重，得到每个月的唯一下单用户列表，排除同用户同月多笔订单的干扰
• 对每个月的下单用户，匹配上月的下单记录和历史用户池，标记是否为上月无下单的用户
• 按月份分组统计符合条件的用户数量

WITH 
-- 2017年之前的历史用户池
history_user AS (
    SELECT DISTINCT UserID FROM Table1
),
-- 每个月去重后的下单用户
monthly_distinct_user AS (
    SELECT DISTINCT YearMonth, UserID 
    FROM Table2
),
-- 为每个用户的下单月份计算对应的上月年月
monthly_user_with_prev AS (
    SELECT 
        YearMonth,
        UserID,
        date_format(add_months(to_date(concat(YearMonth, '01'), 'yyyyMMdd'), -1), 'yyyyMM') AS PrevMonth
    FROM monthly_distinct_user
),
-- 标记每个用户是否为当月上月无下单的用户
user_new_flag AS (
    SELECT 
        a.YearMonth,
        a.UserID,
        CASE 
            -- 2017年1月特殊逻辑：因无2017年前的月度订单数据，默认历史无下单的用户算上月无下单
            WHEN a.YearMonth = '201701' AND h.UserID IS NULL THEN 1
            -- 其他月份：匹配不到上月下单记录即为符合条件
            WHEN b.UserID IS NULL THEN 1
            ELSE 0
        END AS is_qualified
    FROM monthly_user_with_prev a
    LEFT JOIN monthly_distinct_user b 
        ON a.PrevMonth = b.YearMonth AND a.UserID = b.UserID
    LEFT JOIN history_user h 
        ON a.UserID = h.UserID
)
-- 按月份聚合得到最终结果
SELECT 
    YearMonth,
    SUM(is_qualified) AS `Count(NewUserID)`
FROM user_new_flag
GROUP BY YearMonth
ORDER BY YearMonth;

由于Table1仅存储2017年之前有下单记录的用户ID，无具体下单月份，因此2017年1月的统计逻辑默认按「2017年之前无任何下单记录」计算，若需要精准判断2017年1月上月（2016年12月）的下单情况，需补充Table1的月份字段。

• 若YearMonth为数值类型，可将上述SQL中的字符串格式转换逻辑替换为数值运算即可，例如201701的上月计算为IF(YearMonth % 100 = 1, YearMonth - 89, YearMonth -1)
• 若业务定义中2017年之前有下单但上月（2016年12月）无下单的用户也需计入2017年1月的统计结果，可删除上述CASE WHEN中201701的特殊判断条件

内容的提问来源于stack exchange，提问作者Osman BüyükYılmaz