大多数时候，我们需要找到特定列以及其他列的最大值或最小值(取首末次等：取第一行，或者最后一行的场景，通常我们会使用嵌套方法，首先里面这层使用 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)

问题

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

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

解决方案 1

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

> 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 中的任何地方都没有记录。

> 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工具，呕血总结，通幽洞微