Hive row_number()优化改写：MAX/MIN STRUCT函数

欢迎大家来到IT世界,在知识的湖畔探索吧!

大多数时候，我们需要找到特定列以及其他列的最大值或最小值(取首末次等：取第一行，或者最后一行的场景，通常我们会使用嵌套方法，首先里面这层使用 row_number 开窗计算出序号rn，外层过滤【下文方案2】。这次介绍使用max/min struct来代替他【下文方案3】)。例如，我们有以下员工表。

DESC employee; +---------------+------------------------------+----------+--+ | col_name | data_type | comment | +---------------+------------------------------+----------+--+ | name | string | | | work_place | array<string> | | | gender_age | struct<gender:string,age:int>| | | skills_score | map<string,int> | | | depart_title | map<string,array<string>> | | +---------------+------------------------------+----------+--+ 5 rows selected (0.186 seconds) > SELECT name,gender_age.gender AS gender,gender_age.age AS age FROM employee; +----------+---------+------+ | name | gender | age | +----------+---------+------+ | Michael | Male | 30 | | Will | Male | 35 | | Shelley | Female | 27 | | Lucy | Female | 57 | | Steven | Male | 30 | +----------+---------+------+ 5 rows selected (75.887 seconds) 

欢迎大家来到IT世界,在知识的湖畔探索吧!

问题

谁是最年长的男性或女性？

有三种可用的解决方案。 注意，gender_age 是一个结构。

解决方案 1

最常用的方法是先在每个性别组中找到年龄的MAX，然后通过匹配性别和MAX年龄进行SELF JOIN，如下所示。这将创建两个阶段的工作并且效率不高。

欢迎大家来到IT世界,在知识的湖畔探索吧!> SELECT employee.gender_age.gender, employee.gender_age.age, name > FROM > employee JOIN > ( > SELECT > max(gender_age.age) as max_age, gender_age.gender as gender > FROM employee > GROUP BY gender_age.gender > ) maxage > ON employee.gender_age.age = maxage.max_age > AND employee.gender_age.gender = maxage.gender; +--------------+------+-------+ | gender | age | name | +--------------+------+-------+ | Female | 57 | Lucy | | Male | 35 | Will | +--------------+------+-------+ 2 rows selected (94.043 seconds) 

解决方案 2

一旦 Hive 0.11.0 引入了分析功能，我们也可以使用 ROW_NUMBER 来解决问题，但只会触发一个 MapReduce 作业。

> SELECT gender, age, name > FROM > ( > SELECT gender_age.gender AS gender, > ROW_NUMBER() OVER (PARTITION BY gender_age.gender ORDER BY gender_age.age DESC) AS row_num, > gender_age.age as age,name > FROM employee > ) t WHERE row_num = 1; +---------+------+-------+ | gender | age | name | +---------+------+-------+ | Female | 57 | Lucy | | Male | 35 | Will | +---------+------+-------+ 2 rows selected (61.655 seconds) 

解决方案 3

实际上，自 Hive 0.6.0 以来，通过Hive-1128添加的MAX/MIN STRUCT函数有一种更好的方法，尽管它在 Hive Wiki 中的任何地方都没有记录。

欢迎大家来到IT世界,在知识的湖畔探索吧!> SELECT gender_age.gender, > max(struct(gender_age.age, name)).col1 as age, > max(struct(gender_age.age, name)).col2 as name > FROM employee > GROUP BY gender_age.gender; +--------------+------+-------+ | gender | age | name | +--------------+------+-------+ | Female | 57 | Lucy | | Male | 35 | Will | +--------------+------+-------+ 2 rows selected (47.659 seconds) 

上述作业仅触发一个 MapReduce 作业。我们仍然需要使用Group By子句。但是，我们可以使用MAX/MIN STRUCT函数在MAX/MIN值的同一行中显示所有其他列。默认情况下，如果不是Group By列，则Group By子句不允许SELECT列表中显示列。

概括

解决方案 3 在性能、查询复杂性和旧 Hive 的版本支持方面更好。解决方案 2 更好更强大，因为它不需要 GROUP BY 关键字。此外，上述解决方案均适用于 Spark SQL。

解决方案 3 ：max/min struct 其实是把 row_number 中的partition by 信息改为group by，order by改为 struct 里面的参数。max/min struct 是依次比较struct 故也适用 order by 多个参数。

更多进阶干货：

Hive调优：如何实现并发？

HiveSQL高级进阶技巧案例（中）

ClickHouse安装部署与SQL实战

Apache Atlas | 元数据管理框架的独舞

大数据可视化BI工具，呕血总结，通幽洞微