Databricks 第11篇：Spark SQL 查詢（行轉列、列轉行、Lateral View、排序）

悅光陰發表於2021-02-09
SparkSQLView排序

本文分享在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;

 

 

 

參考文件：

Spark SELECT

掌握這個SQL技巧超越80%的人——行轉列/列轉行

SQL reference for Databricks Runtime 7.x

相關文章