如何在AWS Redshift中统计拥有重复街道地址的会员数量

问题回顾 #

现有两张表：

• Members（会员表）：包含name、address_id1、address_id2字段，每个会员可关联多个地址ID，例如Jane Smith的address_id1=1234、address_id2=5678
• Addresses（地址表）：包含address_id、street_address字段，每条记录存储地址字符串，例如address_id=1234和5678对应同一条street_address=123 Main St.

需求：统计拥有多个对应同一street_address的不同address_id的会员总数，基于AWS Redshift执行查询。

原查询存在的问题：

• JOIN条件不合法：on distinct street_address不是有效的关联逻辑，JOIN必须指定明确的字段匹配规则
• 未处理会员多地址ID的关联逻辑，也没有判断多地址ID是否对应同一街道地址

针对固定双地址ID的场景（address_id1 + address_id2） #

如果会员只有两个地址ID字段，直接关联两个地址的信息并筛选条件即可：

SELECT COUNT(DISTINCT m.name) AS qualifying_member_count
FROM members m
JOIN addresses a1 ON m.address_id1 = a1.address_id
JOIN addresses a2 ON m.address_id2 = a2.address_id
-- 确保两个地址ID不同，且对应同一街道地址
WHERE m.address_id1 != m.address_id2
  AND a1.street_address = a2.street_address;

通用场景（支持N个地址ID字段） #

如果会员表有更多地址ID字段（如address_id3、address_id4），先将所有地址ID拆分为单行记录，再统计每个会员的地址对应情况：

WITH member_addresses AS (
    -- 把会员的所有地址ID拆成单行
    SELECT name, address_id1 AS address_id FROM members
    UNION ALL
    SELECT name, address_id2 AS address_id FROM members
    -- 有更多地址ID的话，继续追加UNION ALL
    -- UNION ALL
    -- SELECT name, address_id3 AS address_id FROM members
),
member_address_details AS (
    -- 关联地址表，获取每个地址ID对应的街道地址
    SELECT 
        ma.name,
        ma.address_id,
        a.street_address
    FROM member_addresses ma
    JOIN addresses a ON ma.address_id = a.address_id
),
member_street_stats AS (
    -- 按会员+街道地址分组，统计该组合下的不同地址ID数量
    SELECT 
        name,
        street_address,
        COUNT(DISTINCT address_id) AS unique_address_id_count
    FROM member_address_details
    GROUP BY name, street_address
)
-- 筛选出存在至少一个街道地址对应2个及以上不同地址ID的会员，统计总数
SELECT COUNT(DISTINCT name) AS qualifying_member_count
FROM member_street_stats
WHERE unique_address_id_count >= 2;

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