求符合全员亲属/俱乐部成员关系的最高参与人数套餐SQL查询

现有数据表 #

1. RELATED表（用户关系记录表） #

字段包含 Person1_UID、Person2_UID、Type，数据如下：

Person1_UID Person2_UID   Type
1           2             club member
1           9             family
1           11            colleague
2           4             friend
2           7             colleague
3           4             club member
3           6             colleague
3           9             friend
3           10            follower
4           6             family
4           10            family
5           8             family
6           11            colleague
7           8             colleague
8           11            friend

2. DAY_PACKAGE表（日套餐参与信息表） #

字段包含 DID（日套餐ID）、Description（套餐描述）、UID（用户ID）、VID（优惠券ID），数据如下：

DID Description    UID  VID
1   Spa Day         1   19
1   Spa Day         2   20
1   Spa Day         9   17
2   Adventure Time  4   16
2   Adventure Time  6   16
3   Family Fun Day  3   18
3   Family Fun Day  4   17
3   Family Fun Day  7   18
3   Family Fun Day  10  19
4   Relaxation Date 5   20

需求说明 #

编写SQL查询，找出所有参与者彼此之间均为family（亲属）或club member（俱乐部成员）关系的日套餐中，参与人数最多的那些。查询需返回 DID、Description、Participant_Count 三个字段，期望结果如下：

DID Description Participant_Count
1   Spa Day         3
2   Adventure Time  2  

用户当前已写出统计各套餐总参与人数的基础SQL：

SELECT Description, COUNT(DISTINCT UID) AS Participants_Count
FROM DAY_PACKAGE
GROUP BY Description
ORDER BY Participants_Count DESC

解决方案 #

要实现需求，需分步骤筛选符合条件的套餐：

第一步：生成每个套餐内的所有用户配对 #

通过自连接DAY_PACKAGE表，得到每个套餐下所有两两不同用户的组合（排除用户自身配对）：

SELECT 
    dp1.DID,
    dp1.UID AS UID1,
    dp2.UID AS UID2
FROM DAY_PACKAGE dp1
JOIN DAY_PACKAGE dp2 
    ON dp1.DID = dp2.DID 
    AND dp1.UID < dp2.UID

第二步：验证用户对的关系是否符合要求 #

由于用户关系是双向的（比如A和B的关系可能存为Person1_UID=A,Person2_UID=B或反过来），关联RELATED表时需匹配两种情况，筛选出关系为family或club member的有效配对：

SELECT 
    dp_pair.DID,
    COUNT(*) AS valid_pairs,
    (SELECT COUNT(DISTINCT UID) FROM DAY_PACKAGE dp WHERE dp.DID = dp_pair.DID) AS Participant_Count
FROM (
    SELECT 
        dp1.DID,
        dp1.UID AS UID1,
        dp2.UID AS UID2
    FROM DAY_PACKAGE dp1
    JOIN DAY_PACKAGE dp2 
        ON dp1.DID = dp2.DID 
        AND dp1.UID < dp2.UID
) dp_pair
LEFT JOIN RELATED r 
    ON (dp_pair.UID1 = r.Person1_UID AND dp_pair.UID2 = r.Person2_UID) 
    OR (dp_pair.UID1 = r.Person2_UID AND dp_pair.UID2 = r.Person1_UID)
WHERE r.Type IN ('family', 'club member')
GROUP BY dp_pair.DID

第三步：筛选全符合关系要求且人数最多的套餐 #

一个有n个参与者的套餐，总共有n*(n-1)/2个用户对。只有当有效配对数等于总配对数时，说明该套餐所有参与者彼此关系都符合要求。结合人数排序，最终SQL如下：

支持QUALIFY子句的数据库（如BigQuery、Snowflake）

WITH package_pairs AS (
    -- 生成每个套餐的所有用户配对
    SELECT 
        dp1.DID,
        dp1.UID AS UID1,
        dp2.UID AS UID2
    FROM DAY_PACKAGE dp1
    JOIN DAY_PACKAGE dp2 
        ON dp1.DID = dp2.DID 
        AND dp1.UID < dp2.UID
),
package_valid_pairs AS (
    -- 统计每个套餐的有效配对数
    SELECT 
        pp.DID,
        COUNT(r.Type) AS valid_pair_count
    FROM package_pairs pp
    LEFT JOIN RELATED r 
        ON (pp.UID1 = r.Person1_UID AND pp.UID2 = r.Person2_UID) 
        OR (pp.UID1 = r.Person2_UID AND pp.UID2 = r.Person1_UID)
    WHERE r.Type IN ('family', 'club member')
    GROUP BY pp.DID
),
package_participant_count AS (
    -- 统计每个套餐的参与人数
    SELECT 
        DID,
        COUNT(DISTINCT UID) AS Participant_Count
    FROM DAY_PACKAGE
    GROUP BY DID
)
SELECT 
    ppc.DID,
    dp.Description,
    ppc.Participant_Count
FROM package_participant_count ppc
JOIN package_valid_pairs pvp 
    ON ppc.DID = pvp.DID
JOIN DAY_PACKAGE dp 
    ON ppc.DID = dp.DID
-- 验证有效配对数等于总配对数
WHERE pvp.valid_pair_count = (ppc.Participant_Count * (ppc.Participant_Count - 1)) / 2
-- 筛选参与人数最多的套餐
QUALIFY RANK() OVER (ORDER BY ppc.Participant_Count DESC) = 1
GROUP BY ppc.DID, dp.Description, ppc.Participant_Count
ORDER BY ppc.Participant_Count DESC;

不支持QUALIFY子句的数据库（如MySQL）

WITH package_pairs AS (
    SELECT 
        dp1.DID,
        dp1.UID AS UID1,
        dp2.UID AS UID2
    FROM DAY_PACKAGE dp1
    JOIN DAY_PACKAGE dp2 
        ON dp1.DID = dp2.DID 
        AND dp1.UID < dp2.UID
),
package_valid_pairs AS (
    SELECT 
        pp.DID,
        COUNT(r.Type) AS valid_pair_count
    FROM package_pairs pp
    LEFT JOIN RELATED r 
        ON (pp.UID1 = r.Person1_UID AND pp.UID2 = r.Person2_UID) 
        OR (pp.UID1 = r.Person2_UID AND pp.UID2 = r.Person1_UID)
    WHERE r.Type IN ('family', 'club member')
    GROUP BY pp.DID
),
package_participant_count AS (
    SELECT 
        DID,
        COUNT(DISTINCT UID) AS Participant_Count
    FROM DAY_PACKAGE
    GROUP BY DID
),
valid_packages AS (
    SELECT 
        ppc.DID,
        dp.Description,
        ppc.Participant_Count
    FROM package_participant_count ppc
    JOIN package_valid_pairs pvp 
        ON ppc.DID = pvp.DID
    JOIN DAY_PACKAGE dp 
        ON ppc.DID = dp.DID
    WHERE pvp.valid_pair_count = (ppc.Participant_Count * (ppc.Participant_Count - 1)) / 2
)
SELECT DID, Description, Participant_Count
FROM valid_packages
WHERE Participant_Count = (SELECT MAX(Participant_Count) FROM valid_packages)
ORDER BY Participant_Count DESC;

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