为何SQL全外连接无法统计未匹配的avc_id客户？

问题描述 #

右表avc_enr包含108K个客户（b.avc_id），表a（别名）包含约97K个客户（a.avc_id）。尝试使用右连接、左连接及全外连接，但Total_users的计数始终为97K而非108K，想了解为何全外连接的count函数未统计无匹配的客户？

原SQL代码：

with avc_enr as 
(
    select 
        dt, avc_id, service_template_name 
    from 
        hive.thor_satellite.v_nms_inventory_nmsdb_avc_service
    where 
        current_status = 'ACTIVE' and dt = 20220809
)
select 
    a.dt, a.metrics_date,
    avg(a.vsat_fl_byte_count_kbps) as AUPU_Kbps, 
    count(b.avc_id) as Total_users
from 
    hive.thor_satellite.vda_satellite_nms_performance_smts_avc_pm_throughput a
full outer join 
    avc_enr b on a.avc_id = b.avc_id and a.dt = b.dt
where 
    a.dt = 20220809
group by 
    a.dt, a.metrics_date

问题根源 #

1. WHERE子句过滤了右表无匹配的记录
   全外连接后，avc_enr中存在但表a中不存在的记录，a.dt会是NULL，而WHERE a.dt = 20220809直接将这些NULL记录过滤掉，导致最终只统计到表a中存在的97K客户。

2. COUNT函数的计数逻辑限制
   即使去掉WHERE子句的问题，COUNT(b.avc_id)仅统计非NULL的b.avc_id值，对于表a存在但表b不存在的记录，b.avc_id为NULL不会被计数，但你的需求是统计avc_enr的全部客户，计数逻辑不匹配。

解决方法 #

方法一：调整WHERE条件保留右表记录 #

将日期过滤逻辑调整为兼容左右表的形式，避免过滤右表无匹配的记录：

with avc_enr as 
(
    select 
        dt, avc_id, service_template_name 
    from 
        hive.thor_satellite.v_nms_inventory_nmsdb_avc_service
    where 
        current_status = 'ACTIVE' and dt = 20220809
)
select 
    COALESCE(a.dt, b.dt) as dt, 
    a.metrics_date,
    avg(a.vsat_fl_byte_count_kbps) as AUPU_Kbps, 
    count(distinct b.avc_id) as Total_users -- 用distinct避免重复计数
from 
    hive.thor_satellite.vda_satellite_nms_performance_smts_avc_pm_throughput a
full outer join 
    avc_enr b on a.avc_id = b.avc_id and a.dt = b.dt
where 
    COALESCE(a.dt, b.dt) = 20220809
group by 
    COALESCE(a.dt, b.dt), a.metrics_date

方法二：改用右连接贴合需求 #

如果核心目标是统计avc_enr的全部客户，右连接更贴合逻辑，同时避免过滤右表数据：

with avc_enr as 
(
    select 
        dt, avc_id, service_template_name 
    from 
        hive.thor_satellite.v_nms_inventory_nmsdb_avc_service
    where 
        current_status = 'ACTIVE' and dt = 20220809
)
select 
    b.dt, 
    a.metrics_date,
    avg(a.vsat_fl_byte_count_kbps) as AUPU_Kbps, 
    count(b.avc_id) as Total_users -- 右连接下b.avc_id不会为NULL，直接计数
from 
    hive.thor_satellite.vda_satellite_nms_performance_smts_avc_pm_throughput a
right outer join 
    avc_enr b on a.avc_id = b.avc_id and a.dt = b.dt
group by 
    b.dt, a.metrics_date

关键说明 #

• 全外连接的结果包含左右表所有记录，但WHERE子句如果仅过滤左表字段，会丢失右表无匹配的记录。
• 计数时要明确统计目标：若要统计右表avc_enr的客户数，优先用右连接，或确保COUNT的是右表非空字段，同时避免过滤右表记录。

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