PostgreSQL如何多次自连接表并获取指定条件的统计数据？

数据与需求 #

现有数据表（假设表名为user_table）数据如下：

需求：针对指定userid集合 (100,101,102,103,104,105)，统计每个userid对应的满足**C1 > 当前userid的C1 且 C2 < 当前userid的C2**的userid数量。

解决方案 #

使用CTE构造目标userid集合，通过自连接实现条件统计，SQL语句如下：

WITH target_users AS (
    SELECT 100 AS userid UNION ALL
    SELECT 101 UNION ALL
    SELECT 102 UNION ALL
    SELECT 103 UNION ALL
    SELECT 104 UNION ALL
    SELECT 105
)
SELECT 
    tu.userid,
    COUNT(ud_other.userid) AS Count
FROM target_users tu
LEFT JOIN user_table ud_current ON tu.userid = ud_current.userid
LEFT JOIN user_table ud_other 
    ON ud_other.C1 > ud_current.C1 
    AND ud_other.C2 < ud_current.C2
GROUP BY tu.userid
ORDER BY tu.userid;

逻辑说明 #

1. target_users：生成需要统计的所有userid，包含原表中不存在的105；
2. 第一次左连接：关联当前userid对应的C1、C2值，确保即使userid不存在（如105）也能被纳入统计；
3. 第二次左连接：匹配满足C1更大且C2更小条件的其他userid；
4. 分组统计：按目标userid分组，统计匹配到的记录数，不存在的userid对应Count为0。

最终输出结果 #

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