PostgreSQL中如何仅对同分组子集行执行自连接查询

现有表结构 #

people人员表 #

存储人员姓名和年龄信息，建表及测试数据如下：

CREATE TABLE people (
    name TEXT PRIMARY KEY,
    age  INT  NOT NULL
);

INSERT INTO people VALUES
    ('Lisa',     30),
    ('Marta',    27),
    ('John',     32),
    ('Sam',      41),
    ('Alex',     12),
    ('Aristides',43),
    ('Cindi',     1)
    ;

houses房屋表 #

存储房屋名称和对应居住成员，成员关联people表的姓名字段，建表及测试数据如下：

CREATE TABLE houses (
    house_name TEXT NOT NULL,
    house_member TEXT NOT NULL REFERENCES people(name)
);    

INSERT INTO houses VALUES
        ('house Smith', 'Lisa'),
        ('house Smith', 'Marta'),
        ('house Smith', 'John'),
        ('house Doe', 'Lisa'),
        ('house Doe', 'Marta'),
        ('house Doe', 'Alex'),
        ('house Doe', 'Sam'),
        ('house McKenny', 'Aristides'),
        ('house McKenny', 'John'),
        ('house McKenny', 'Cindi')
        ;

原有逻辑与需求 #

之前全表范围识别潜在子女的逻辑是对people表做自连接，将每个人员和表内所有其他人员比对年龄，原SQL如下：

SELECT DISTINCT A.name as child
FROM people A, people B
WHERE A.age + 16 < B.age;

判定规则：父母和子女年龄差至少为16岁。
现在需要缩小比对范围：仅和同一房屋内的其他居住成员比对年龄，不需要跨房屋匹配，提升查询效率，最终结果按成员姓名排序，预期返回结果如下：
| house_member |
|--------------|
| Alex |
| Cindi |
之前尝试用PARTITION BY house_name窗口分区、GROUP BY分组实现，因为逻辑中没有聚合计算，两种方案都不适用。

实现方案 #

不需要使用窗口函数或分组，直接在房屋表自连接时增加同房屋判定条件即可，SQL如下：

SELECT DISTINCT hA.house_member
FROM houses hA
-- 关联取当前成员的年龄
JOIN people pA 
  ON hA.house_member = pA.name
-- 房屋表自连接，限定比对范围为同一房屋
JOIN houses hB 
  ON hA.house_name = hB.house_name
  AND hA.house_member != hB.house_member -- 避免自己和自己比对
-- 关联取同屋比对对象的年龄
JOIN people pB 
  ON hB.house_member = pB.name
WHERE pA.age + 16 < pB.age
ORDER BY hA.house_member;

逻辑说明 #

• 给houses表取两个别名做自连接，通过hA.house_name = hB.house_name条件直接把比对范围限制在同一房屋内，避免全表扫描比对
• 额外加hA.house_member != hB.house_member条件排除自己和自己比对的无效计算，进一步提升效率
• 分别关联people表拿到两个比对对象的年龄，套用原有的年龄差判定规则
• 用DISTINCT去重，避免同一个人匹配到多个同屋长辈时返回重复记录
• 最后按成员姓名排序即可得到预期结果

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