如何对两个SQL查询的去重手机号统计结果做差值计算？

需求：计算两个SQL查询返回的去重手机号总数的差值，两个查询均按bMONTH（月份）和bYEAR（年份）分组统计。

原查询语句 #

第一个查询（总去重手机号数） #

select
    count(distinct phnumber) as UniquePHNUMBERS_TOTAL,
    bMONTH,
    bYEAR
from
    (select
         month(c.callinfodate) as bMONTH,
         year(c.callinfodate) as bYEAR,
         c.phnumber,
         count(distinct c.idofthecallinfo) as TOTALcallinfoS,
         ses.applicationname, ele.typename
     from
         callinfo c
     left join
         sessioninfo ses on c.idofthecallinfo = ses.idofthecallinfo
     left join
         elementinfo ele on c.idofthecallinfo = ele.idofthecallinfo
     where
         ses.applicationname in ('CALLS_1', 'CALLS_2', 'CALLS_3', 'CALL_4')
    group by
        c.callinfodate, c.phnumber, ses.applicationname, ele.typename) as IVRTOTAL
group by
    bMONTH, bYEAR

第二个查询（需扣除的去重手机号数） #

select
    count(distinct phnumber) as UniquePHNUMBERS_TOTAL,
    bMONTH,
    bYEAR
from
    (select
         month(c.callinfodate) as bMONTH,
         year(c.callinfodate) as bYEAR,
         c.phnumber,
         count(distinct c.idofthecallinfo) as TOTALcallinfoS,
         ses.applicationname, ele.typename
     from
         callinfo c
     left join
         sessioninfo ses on c.idofthecallinfo = ses.idofthecallinfo
     left join
         elementinfo ele on c.idofthecallinfo = ele.idofthecallinfo
     where
         ((ses.applicationname in ('CALLS_4')
          and ele.typename in ('CALLS_41', 'CALLS_42', 'CALLS_43', 'CALLS_44',
                               'CALLS_45', 'CALLS_46', 'CALLS_47')))
     group by
         c.callinfodate, c.phnumber, ses.applicationname, ele.typename) as IVRTOTAL
group by
    bMONTH, bYEAR

各查询结果 #

第一个查询结果 #

UniquePHNUMBERS_TOTAL
---------------------
11219
153041
149043
143166
138100
8343

注：原结果未携带年月字段，实际查询需保留bMONTH和bYEAR用于关联。

第二个查询结果 #

4007
68528
63922
61037
60494
3276

预期差值结果 #

7212
84513
85121
82129
77606
5067

问题描述 #

尝试用JOIN关联两个查询做减法时，因未按年月字段精准关联，产生笛卡尔积导致大量冗余行，错误结果如下：

7212
7943
-49818
-52703
-57309
-49275
149034
149765
92004
89119
84513
92547
145036
145767
88006
85121
80515
88549
139159
139890
82129
79244
74638
82672
134093
134824
77063
74178
69572
77606
4336
5067
-52694
-55579
-60185
-52151

正确SQL实现 #

SELECT
    total.bMONTH,
    total.bYEAR,
    (total.UniquePHNUMBERS_TOTAL - COALESCE(subtract.UniquePHNUMBERS_TOTAL, 0)) AS Difference
FROM
    (
        -- 第一个查询（总去重数），保留年月字段
        select
            count(distinct phnumber) as UniquePHNUMBERS_TOTAL,
            bMONTH,
            bYEAR
        from
            (select
                 month(c.callinfodate) as bMONTH,
                 year(c.callinfodate) as bYEAR,
                 c.phnumber,
                 count(distinct c.idofthecallinfo) as TOTALcallinfoS,
                 ses.applicationname, ele.typename
             from
                 callinfo c
             left join
                 sessioninfo ses on c.idofthecallinfo = ses.idofthecallinfo
             left join
                 elementinfo ele on c.idofthecallinfo = ele.idofthecallinfo
             where
                 ses.applicationname in ('CALLS_1', 'CALLS_2', 'CALLS_3', 'CALL_4')
            group by
                c.callinfodate, c.phnumber, ses.applicationname, ele.typename) as IVRTOTAL
        group by
            bMONTH, bYEAR
    ) AS total
LEFT JOIN
    (
        -- 第二个查询（需扣除的去重数），保留年月字段
        select
            count(distinct phnumber) as UniquePHNUMBERS_TOTAL,
            bMONTH,
            bYEAR
        from
            (select
                 month(c.callinfodate) as bMONTH,
                 year(c.callinfodate) as bYEAR,
                 c.phnumber,
                 count(distinct c.idofthecallinfo) as TOTALcallinfoS,
                 ses.applicationname, ele.typename
             from
                 callinfo c
             left join
                 sessioninfo ses on c.idofthecallinfo = ses.idofthecallinfo
             left join
                 elementinfo ele on c.idofthecallinfo = ele.idofthecallinfo
             where
                 ((ses.applicationname in ('CALLS_4')
                  and ele.typename in ('CALLS_41', 'CALLS_42', 'CALLS_43', 'CALLS_44',
                                       'CALLS_45', 'CALLS_46', 'CALLS_47')))
             group by
                 c.callinfodate, c.phnumber, ses.applicationname, ele.typename) as IVRTOTAL
        group by
            bMONTH, bYEAR
    ) AS subtract
ON total.bMONTH = subtract.bMONTH AND total.bYEAR = subtract.bYEAR
ORDER BY total.bYEAR, total.bMONTH;

关键说明 #

1. 两个子查询均保留bMONTH和bYEAR字段，用于精准关联同一年月的统计数据，避免笛卡尔积
2. 使用LEFT JOIN确保即使某年月在第二个查询中无数据，仍能保留总数值（通过COALESCE将空值转为0，避免减法报错）
3. 按年月排序，保证结果顺序与原查询一致

内容的提问来源于stack exchange，提问作者B.Avramov