Hive中group by搭配limit使用时聚合结果不符合预期

结果差异的常见原因 #

以下是按出现概率从高到低排列的可能原因：

• SQL写法不规范导致的隐式分组错误
  你贴出的第一条SQL的SELECT子句里没有包含channel字段，但返回结果却带了channel列，说明实际执行的SQL和你给出的存在出入。如果你使用的是低版本MySQL，且关闭了ONLY_FULL_GROUP_BY校验开关，数据库允许SELECT字段和GROUP BY字段不匹配，会默认按隐式的多字段分组，最终返回的聚合结果不是仅按channel维度统计的数值，自然偏小。
• channel字段存在脏数据导致分组拆分
  如果channel字段里存在和'a'相似的脏值，比如带前后空格的' a '、大小写不同的'A'、包含不可见字符的a，当数据库的校对规则是大小写敏感/二进制匹配时，这些值会被分成不同的分组：你单独执行where channel = 'a'时，如果校对规则是大小写不敏感的，会匹配所有相似值得到2057的正确总数，但分组时这些值会被拆成多个独立的分组，你看到的limit 3里的a只是其中一个小分组的统计值39。
• 低版本SQL引擎的执行计划bug
  少数低版本的大数据计算引擎（如旧版Hive、Spark SQL）存在优化逻辑错误，会把LIMIT算子提前到GROUP BY之前执行，相当于先取表的前几十行数据再做分组聚合，得到的统计值自然远小于实际总数。
• 数据一致性问题
  如果两次查询间隔内有大量数据写入、或者你查询的是不同步的副本数据，也可能出现结果不一致，这种情况出现概率极低。

排查方案 #

1. 先修正SQL写法，明确把分组字段放到SELECT子句中，同时添加排序规则避免返回随机分组：

select channel, count(users) as cnt
from user_table
group by channel
order by cnt desc
limit 3;

2. 去掉LIMIT查看全部分组结果，确认是否存在多个和a相关的分组，累加后是否等于2057。
3. 如果全量分组结果里a的统计值还是不对，可以开启数据库的严格分组模式，避免隐式分组的逻辑错误。

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