MySQL：过滤含action=2的parentid并统计操作的不同天数

需求：先剔除所有包含action=2的parentid的全部数据，再统计剩余数据中每个parentid的操作涉及的不同天数。

示例数据 #

错误尝试及结果 #

原查询语句：

WITH overpass(parentid, date) AS
(
    SELECT parentid, DATE(datecre) as date
    FROM good
    WHERE parentid NOT IN (
        SELECT DISTINCT parentid
        FROM good
        WHERE action=2)
        GROUP BY date
)
SELECT parentid, COUNT(date) as days
FROM overpass
GROUP BY date;

错误输出：

期望输出 #

修正后的查询语句 #

WITH valid_data AS (
    SELECT parentid, DATE(datecre) AS date
    FROM good
    -- 过滤掉存在action=2的parentid
    WHERE parentid NOT IN (
        SELECT DISTINCT parentid
        FROM good
        WHERE action = 2
    )
)
SELECT 
    parentid,
    COUNT(DISTINCT date) AS days
FROM valid_data
GROUP BY parentid;

错误原因说明 #

1. 原CTE中的GROUP BY date逻辑错误，会导致每个日期仅保留一条数据，且随机关联一个parentid，破坏了原始数据的对应关系。
2. 外层查询GROUP BY date不符合需求，我们需要按parentid分组来统计每个分组的天数。
3. 统计不同天数必须使用COUNT(DISTINCT date)，否则会把同一天的多条记录重复计数。

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