如何用Group By查询存在部分指定Fixture的房屋及缺失设施

问题背景 #

需要将现有房屋与外部维护的Fixture列表（Sink、Faucet、Toilet）做关联分析，找出那些至少拥有一个指定Fixture，但缺少其他Fixture的房屋，并返回每栋房屋对应的缺失Fixture。比如某房屋只装了Sink，就要返回它缺失Faucet和Toilet。

原始数据 #

预期结果 #

解决方案 #

1. SQL实现 #

因为Fixture列表存储在外部，我们可以先构造全量的房屋-Fixture组合，再排除已存在的关联，最后过滤出有至少一个Fixture的房屋：

-- 构造外部Fixture列表
WITH all_fixtures AS (
    SELECT 'Sink' AS fixture UNION ALL
    SELECT 'Faucet' AS fixture UNION ALL
    SELECT 'Toilet' AS fixture
),
-- 获取所有有至少一个Fixture的房屋
valid_houses AS (
    SELECT DISTINCT 房屋 FROM 房屋Fixture关联表
),
-- 生成所有房屋与Fixture的全量组合
all_house_fixtures AS (
    SELECT v.房屋, f.fixture
    FROM valid_houses v
    CROSS JOIN all_fixtures f
)
-- 筛选出缺失的Fixture
SELECT ahf.房屋, ahf.fixture AS `Missing Fixture`
FROM all_house_fixtures ahf
LEFT JOIN 房屋Fixture关联表 hf
    ON ahf.房屋 = hf.房屋 AND ahf.fixture = hf.Fixture
WHERE hf.Fixture IS NULL
ORDER BY ahf.房屋;

2. Python代码实现 #

如果用代码处理，直接对比已有Fixture和外部列表即可：

# 原始房屋-Fixture关联数据
house_fixtures = [
    (1, 'Sink'),
    (1, 'Faucet'),
    (1, 'Toilet'),
    (2, 'Sink'),
    (2, 'Faucet'),
    (3, 'Sink'),
]

# 外部维护的Fixture列表
required_fixtures = {'Sink', 'Faucet', 'Toilet'}

# 整理每栋房屋已有的Fixture集合
house_has = {}
for house, fixture in house_fixtures:
    house_has.setdefault(house, set()).add(fixture)

# 输出缺失结果表格
print("| 房屋 | Missing Fixture |")
print("|------|-----------------|")
for house, fixtures in house_has.items():
    for missing in required_fixtures - fixtures:
        print(f"| {house}    | {missing}          |")

运行代码后会直接输出符合预期的结果表格。

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