Databricks 第11篇：Spark SQL 查询（行转列、列转行、Lateral View、排序）
本文分享在Azure Databricks中如何实现行转列和列转行，并介绍对查询的结果进行排序的各种方式。
一，行转列
在分组中，把每个分组中的某一列的数据连接在一起：
- collect_list：把一个分组中的列合成为数组，数据不去重，格式是['a','a','b']
- collect_set：把一个分组中的列合成为集合，数据去重，格式是['a','b']
用于连接文本数组的函数，通过sep把数组中的item分割开，连接成一个字符串：
concat_ws(sep, [str | array(str)]+)
举个例子，把每个用户的game，通过逗号连接起来：
select uid ,concat_ws(",",collect_list(game)) as game_list from user_game
二，列转行
Explode(expr) 用于处理array和map结构的数据，把一行的列值转换成多行，该函数产生一个虚拟表，包含一行或多行数据，也就是说，Explode(expr)函数把array类型expr中的元素分成多行，或者将map类型的expr中的元素分成多行和多列。
举个例子，把game_list中的每个item转换为一行数据：
with cte_game as ( select uid ,collect_list(game) as game_list from user_game
group by uid ) select uid ,explode(game_list) as game from cte_game
三，Lateral View子句
Lateral View子句用于连接表值函数（UDTF），比如explode、split 。Lateral View通过UDTF函数把数据拆分成多行，再把多行结果组合成一个虚拟表。
该子句主要解决的问题是：在select使用UDTF做查询的过程中，该查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)
使用LATERAL VIEW + explode 函数进行查询，语句如下：
select movie,category_name from movie_info LATERAL VIEW explode(category) tmpTable as category_name; -- category_name 是给 explode(category) 列起的别名
四，排序
在SELECT子句中，ORDER BY子句用于保证最终数据的顺序，除此之外，还可以按照分区来排序。
[ WITH with_query [ , ... ] ] select_statement[ ORDER BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ] [ SORT BY { expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [ , ... ] } ] [ CLUSTER BY { expression [ , ... ] } ] [ DISTRIBUTE BY { expression [, ... ] } ] [ WINDOW { named_window [ , WINDOW named_window, ... ] } ]
1，DISTRIBUTE BY子句
根据输入表达式对表进行重新分区，
DISTRIBUTE BY { expression [ , ... ] }
举个例子，把age相同的person作为同一个cluster（或partition）。
SELECT age, name FROM person DISTRIBUTE BY age;
2，SORT BY子句
在每个分区中，按照指定的顺序对每个分区内的数据进行排序，如果分区不止一个，那么SORT BY可能会返回部分排序的结果。
SORT BY { expression [ sort_direction | nulls_sort_order ] [ , ... ] }
参数注释：
nulls_sort_order 的有效值是 NULLS { FIRST | LAST }，可选，用于指定在非NULL值之前/之后是否返回NULL值。
如果未指定null_sort_order，则如果排序顺序为ASC，则NULL排在最前面，如果排序顺序为DESC，则NULL排在最后面。
- 如果指定了NULLS FIRST，则无论排序顺序如何，都将首先返回NULL值。
- 如果指定了NULLS LAST，则无论排序顺序如何，最后都会返回NULL值。
举个例子，按照name对每个分区中的数据进行排序：
SELECT /*+ REPARTITION(zip_code) */ name, age, zip_code FROM person SORT BY name ASC, age DESC;
3，CLUSTER BY子句
根据输入表达式对数据进行重新分区，然后对每个分区内的数据进行排序。从语义上讲，这等效于先执行DISTRIBUTE BY，再执行SORT BY。该子句仅确保结果行在每个分区内排序，并且不保证输出的总顺序。
CLUSTER BY { expression [ , ... ] }
举个例子，按照age把person分区，age相同的person位于同一个分区，然后按照age对每个分区中的person进行排序。
SELECT age, name FROM person CLUSTER BY age;
参考文档：
