查询满足近3个月2种颜色及此前独特色条件的人员ID

筛选规则 #

需同时符合以下两个要求：

• 近3个月的记录中，该人员至少有2种不同的颜色
• 3个月之前的记录中，该人员存在至少1种独特色（独特色定义：该颜色未出现在该人员近3个月的颜色记录中）

示例参考 #

示例表数据 #

预期输出 #

结果说明 #

PersonID=2符合要求：近3个月有绿色、黄色2种不同颜色，3个月前的红色未在近3个月记录中出现，属于独特色。PersonID=1近3个月只有黑色1种颜色，不满足第一个筛选条件，因此被排除。

实现代码（MySQL语法） #

-- 定义表名：person_color，可根据实际表名修改
WITH recent_color_info AS (
    -- 统计每个用户近3个月的颜色数量、颜色集合
    SELECT
        PersonID,
        COUNT(DISTINCT Color) AS distinct_recent_color_num,
        GROUP_CONCAT(DISTINCT Color) AS recent_color_list
    FROM person_color
    -- 实际使用可将'2021-10-25'替换为CURDATE()取当前日期计算
    WHERE Date >= DATE_SUB('2021-10-25', INTERVAL 3 MONTH)
    GROUP BY PersonID
),
old_color_info AS (
    -- 提取每个用户3个月前的所有非重复颜色
    SELECT DISTINCT
        PersonID,
        Color AS old_color
    FROM person_color
    WHERE Date < DATE_SUB('2021-10-25', INTERVAL 3 MONTH)
)
SELECT DISTINCT o.PersonID
FROM old_color_info o
INNER JOIN recent_color_info r
    ON o.PersonID = r.PersonID
WHERE
    -- 满足条件1：近3个月至少2种不同颜色
    r.distinct_recent_color_num >= 2
    -- 满足条件2：存在3个月前的独特色
    AND FIND_IN_SET(o.old_color, r.recent_color_list) = 0;

如果你使用其他数据库，只需替换对应字符串拼接、字符串查找函数即可：

• PostgreSQL：将GROUP_CONCAT替换为STRING_AGG(Color, ',')，FIND_IN_SET替换为POSITION(o.old_color IN r.recent_color_list) = 0
• SQL Server：将GROUP_CONCAT替换为STRING_AGG(Color, ',')，FIND_IN_SET替换为CHARINDEX(o.old_color, r.recent_color_list) = 0

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