Hive按name分组统计Apple、Samsung设备数量异常排查

原始子查询输出数据 #

david    iphone 13
david    iphone 14
david    galaxy 22
david    oneplus
frank    iphone 13 
frank    oneplus
macy     oneplus

期望输出 #

david    2  1 
frank    1  0 
macy     0  0 

注释：输出不含表头说明行，上述示例中的# name apple samsung仅用于解释列含义

用户编写的Hive SQL语句 #

SELECT 
   name, 
   SUM(CASE WHEN (tb.device LIKE '%iphone%') THEN 1 ELSE 0 END) AS apple,
   SUM(CASE WHEN (tb.device LIKE '%galaxy%') THEN 1 ELSE 0 END) AS samsung
FROM (
SELECT 
   name, device
..
..
..
) tb 
GROUP BY name

问题原因排查 #

从逻辑上看，这段SQL的核心统计思路是正确的，执行后未得到期望结果，大概率是以下几个细节问题导致：

• 子查询数据不完整：子查询中省略的..部分可能添加了错误的过滤条件，比如误筛掉了部分包含iphone/galaxy的记录，导致分组统计的数据源不对。
• 字段存在隐形字符：device或name字段可能包含前后空格、制表符等不可见字符，比如原始数据里frank的iphone 13末尾有空格，若存在换行符这类特殊隐形字符，会直接导致匹配失败。可以给字段加TRIM()处理后再判断：

  SUM(CASE WHEN TRIM(tb.device) LIKE '%iphone%' THEN 1 ELSE 0 END) AS apple
  

• 大小写不匹配：Hive的LIKE操作区分大小写，如果原始数据中存在iPhone这类首字母大写的记录，SQL里的%iphone%会匹配失败。可以统一转成小写后匹配：

  SUM(CASE WHEN LOWER(tb.device) LIKE '%iphone%' THEN 1 ELSE 0 END) AS apple
  

• name字段分组异常：如果name字段存在大小写差异（如David和david）或隐形字符，会导致同一个用户被拆分成多个分组，统计结果分散。可以对name字段也做TRIM()或统一大小写处理后再分组：

  GROUP BY TRIM(name)
  

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