SQL使用GROUP BY按日统计满足双重状态条件的distinct parent_id数量

原SQL存在的问题 #

• 分组规则不符合需求：原SQL按精确到时分秒的created_at字段和parent_id分组，无法实现按天统计的目标
• 状态判断逻辑错误：一是将需求中的canceled状态错写为cancelling，二是原有判断逻辑没有明确拆分两类目标组合，可能匹配到不符合要求的记录
• 计数逻辑错误：原写法统计的是分组后的行数量，无法得到按天去重的parent_id统计结果

修正后SQL #

使用子查询先判断每个parent_id每日是否满足条件，再按天聚合统计数量：

SELECT 
    stat_date,
    year_key,
    day_key,
    COUNT(parent_id) AS pencan
FROM (
    SELECT 
        DATE(created_at) AS stat_date,
        DATE_FORMAT(created_at, '%Y') AS year_key,
        DATE_FORMAT(created_at, '%m-%d') AS day_key,
        parent_id
    FROM sales_flat_order_status_history 
    WHERE created_at BETWEEN '2010-01-01 00:00:00' AND '2013-04-30 23:59:59'
    GROUP BY stat_date, year_key, day_key, parent_id
    HAVING 
        -- 必须存在processing状态
        SUM(status = 'processing') > 0
        AND (
            -- 满足条件1：同时存在pending状态
            SUM(status = 'pending') > 0
            -- 满足条件2：同时存在canceled状态
            OR SUM(status = 'canceled') > 0
        )
) AS qualified_parent
GROUP BY stat_date, year_key, day_key
ORDER BY stat_date ASC;

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