Hive中按分组统计频次最高值的实现方法（适配Hive0.14）

原SQL报错根因 #

原有SQL抛出语义异常来自两个问题：

• 语法疏漏：select hour(tpep_pickup_datetime), pulocationid 行尾缺失逗号，Hive解析时将后续窗口函数识别为以pulocationid命名的自定义函数调用，直接触发Invalid function pulocationid报错
• 逻辑偏差：需求是取每个locationid下出现频次最高的小时，而非小时数值最大的项；同时row_number()窗口函数未指定排序规则，本身无法实现按频次排序取Top1的效果，叠加Hive 0.14对窗口函数分组校验的严格限制，最终触发窗口拆分失败的异常。

兼容Hive 0.14的稳定实现 #

Hive 0.14窗口函数支持能力有限，采用分层聚合+窗口排序的写法兼容性最好，大数据量下性能表现也更稳定，实现逻辑分两步：

1. 第一层聚合：按pulocationid、对应上车小时分组，统计每个(locationid, 小时)组合的出现次数
2. 第二层排序：对每个locationid分区，按组合出现次数降序排名，筛选排名为1的结果即为目标值

可直接运行的SQL代码：

SELECT
    pulocationid AS locationid,
    pickup_hour AS hour
FROM (
    SELECT
        pulocationid,
        pickup_hour,
        ROW_NUMBER() OVER (PARTITION BY pulocationid ORDER BY cnt DESC) AS rn
    FROM (
        SELECT
            pulocationid,
            HOUR(tpep_pickup_datetime) AS pickup_hour,
            COUNT(1) AS cnt
        FROM yellowtaxi22
        GROUP BY pulocationid, HOUR(tpep_pickup_datetime)
    ) stat_hour_cnt
) rank_res
WHERE rn = 1;

补充说明 #

• 若同一locationid下存在多个小时出现频次并列最高，上述写法会返回其中一个；如果需要返回所有并列最高的小时，将ROW_NUMBER()替换为RANK()即可。
• Hive 0.14对同select子句内混用普通聚合、窗口函数的写法解析存在已知bug，容易触发窗口分组拆分失败的错误，分层写子查询的方式可以完全规避这个问题。

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