Postgres查询优化：保留无经纪/代理数据的县记录

修改后的Postgres查询 #

with locations_cte as
(
    select 
        lpad(cbsa.zip,5,'0') as zip,
        cbsa.st,
        cbsa.county
    from cbsa_locations cbsa
)
,
brokerage_count_cte as 
(
    select 
        cb.county,
        cb.st,
        count(distinct bcz.brokerage_code) as brokerageCount
    from locations_cte cb
    left join brokerage_coverage_zips bcz on cb.zip = bcz.zip
    group by cb.st, cb.county
)
,
agent_count_cte as 
(
    select 
        cb.county,
        cb.st,
        count(distinct puz.profile_id) as agentCount,
        count(distinct case when pup.brokerage_code is not null then puz.profile_id end) as BAagentCount
    from locations_cte cb
    left join profile_coverage_zips puz on cb.zip = puz.zip
    left join partner_user_profiles pup on pup.id = puz.profile_id and pup.verification_status = 'Verified-Verified'
    group by cb.county, cb.st
)
select 
    cb.county,
    cb.st,
    COALESCE(bcc.brokerageCount, 0) as brokerageCount,
    COALESCE(acc.agentCount, 0) as agentCount,
    COALESCE(acc.BAagentCount, 0) as BAagentCount,
    COALESCE(acc.agentCount, 0) - COALESCE(acc.BAagentCount, 0) as UnaffiliatedAgentCount
from locations_cte cb
left join brokerage_count_cte bcc on bcc.st = cb.st and bcc.county = cb.county
left join agent_count_cte acc on acc.st = cb.st and acc.county = cb.county
group by cb.county, cb.st, bcc.brokerageCount, acc.agentCount, acc.BAagentCount
order by brokerageCount ASC

关键修改说明 #

• 保留所有县的基础逻辑：将两个统计CTE的关联顺序反转，以locations_cte（包含全部县数据）为左表，左连接业务数据表，确保无业务数据的县不会被过滤
• 空值转0处理：用COALESCE函数把统计结果中的NULL值转为0，明确显示无经纪商/代理的县的统计数量
• 代理统计逻辑修正：把partner_user_profiles的验证条件移到ON子句，并改为左连接，避免因无代理数据丢失对应县
• 主查询关联优化：将原内连接改为左连接，保证即使某县在经纪商或代理统计中无数据，也能出现在最终结果里
• 去重简化：移除不必要的distinct，通过group by保证结果唯一性

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