PostgreSQL按礼品类别统计用户完整礼品套数的查询方法

需求说明 #

需统计每个用户在各礼品类别下的完整套数，规则为：每个类别必须集齐该类别下所有指定gift_id才算1套：

• perfume类别需集齐gift_id=1、2、3、4
• drink类别需集齐gift_id=5、6、7、8

用户礼品持有表（简化版） #

期望查询结果 #

原因：drink类别未集齐4个指定gift_id，不计入统计；用户的第二套perfume缺少gift_id=3，也不计入。

解决方案 #

核心思路 #

1. 先确定每个礼品类别需要集齐的gift_id总数；
2. 统计每个用户在每个类别下，每个gift_id的持有数量；
3. 每个类别能组成的完整套数，由该类别下用户持有数量最少的那个gift_id决定，同时需确保用户已集齐该类别所有gift_id。

SQL查询语句 #

假设用户礼品持有表名为user_gifts，礼品类别定义表名为gift_definitions：

WITH category_required AS (
    -- 统计每个类别需要集齐的gift_id数量
    SELECT gift_category, COUNT(DISTINCT gift_id) AS required_gifts
    FROM gift_definitions
    GROUP BY gift_category
),
user_gift_counts AS (
    -- 统计每个用户每个类别下各gift_id的持有数量
    SELECT 
        ug.user_id,
        ug.gift_category,
        ug.gift_id,
        COUNT(*) AS gift_count
    FROM user_gifts ug
    JOIN gift_definitions gd 
        ON ug.gift_id = gd.gift_id 
        AND ug.gift_category = gd.gift_category
    GROUP BY ug.user_id, ug.gift_category, ug.gift_id
),
user_category_min_counts AS (
    -- 计算每个用户每个类别能组成的完整套数，需先确保集齐所有gift_id
    SELECT 
        user_id,
        gift_category,
        MIN(gift_count) AS possible_sets
    FROM user_gift_counts
    GROUP BY user_id, gift_category
    HAVING COUNT(DISTINCT gift_id) = (
        SELECT required_gifts 
        FROM category_required 
        WHERE gift_category = user_gift_counts.gift_category
    )
)
SELECT 
    user_id,
    gift_category,
    possible_sets AS set_count
FROM user_category_min_counts

逻辑解释 #

1. category_required：提前计算每个类别需要的gift_id总数，比如perfume和drink都是4个；
2. user_gift_counts：统计用户对每个类别下每个gift_id的持有次数，比如用户123的perfume类别中，gift_id1有2次，gift_id3有1次；
3. user_category_min_counts：对每个用户每个类别，取所有gift_id持有数量的最小值（这就是能组成的完整套数），同时通过HAVING子句筛选出已经集齐该类别所有gift_id的记录；
4. 最后输出的结果就是符合要求的用户-类别-套数统计。

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