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

DESC employee;+---------------+------------------------------+----------+--+|   col_name    |          data_type           | comment  |+---------------+------------------------------+----------+--+| name          | string                       |          || work_place    | array                |          || gender_age    | struct|          || skills_score  | map              |          || depart_title  | map>    |          |+---------------+------------------------------+----------+--+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工具，呕血总结，通幽洞微