如何用Hive SQL统计每条记录新旧地址一致的唯一客户数

场景 #

假设CName代表客户名称：

• John Smith：无论旧地址是什么，新地址始终相同
• Sandra Zay：情况与John Smith一致
• Joe Pipers：每条记录的旧地址和对应新地址完全一致
• Jane Tolar：情况与Joe Pipers一致

数据集示例：

+------------------+------------------------+-------------------+
|    CName         |     Old_Address        |     New_Address   |
+------------------+------------------------+-------------------+
|  John Smith      |  123 Nowheresville     | 123 Nowheresville |
|  John Smith      |  456 Evergreen Terrace | 123 Nowheresville |
|  Sandra Zay      |  155 Rombust Ave       | 155 Rombust Ave   |
|  Sandra Zay      |  276 Alternews St      | 155 Rombust Ave   |
|  Joe Pipers      |  999 Somewhereelse     | 999 Somewhereelse |
|  Joe Pipers      |  876 BeautifulPl       | 876 BeautifulPl   |
|  Jane Tolar      |  145 Someplace         | 145 Someplace     |
|  Jane Tolar      |  732 Happyland         | 732 Happyland     |
+------------------+------------------------+-------------------+

需求 #

在Impala环境下使用Hive SQL，统计所有记录中Old_Address与New_Address均一致的客户数量，每个符合条件的客户计1条，最终输出总数。例如Joe Pipers和Jane Tolar各计1条，期望输出：

+-----------------+
|  count(CNames)  |
+-----------------+
|       2         |
+-----------------+ 

解决方案 #

通过两次聚合逻辑实现需求：

1. 先按客户分组，筛选出所有记录都满足地址一致的客户
2. 再统计这类客户的总数

核心SQL代码 #

SELECT COUNT(DISTINCT CName) AS `count(CNames)`
FROM (
    SELECT CName
    FROM your_table_name
    GROUP BY CName
    HAVING SUM(CASE WHEN Old_Address != New_Address THEN 1 ELSE 0 END) = 0
) qualified_customers;

代码说明 #

• 内层子查询：按CName分组，用CASE标记不满足地址一致的记录，通过SUM统计该客户下不符合条件的记录数，HAVING SUM(...) = 0确保该客户所有记录都满足Old_Address = New_Address
• 外层查询：用COUNT(DISTINCT CName)统计符合条件的客户总数，保证每个客户只被计数一次

简洁替代方案 #

也可以通过MIN函数判断客户所有记录是否都符合条件：

SELECT COUNT(DISTINCT CName) AS `count(CNames)`
FROM (
    SELECT CName
    FROM your_table_name
    GROUP BY CName
    HAVING MIN(CASE WHEN Old_Address = New_Address THEN 1 ELSE 0 END) = 1
) qualified_customers;

如果客户所有记录都满足地址一致，MIN的结果为1；只要有一条不满足，MIN结果为0，以此筛选目标客户。

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