[原文連結]( https://databricks.com/blog/2018/11/01/sql-pivot-converting-rows-to-columns.html )
引言
Pivot作為DataFrame的功能在Apache Spark 1.6被引入, 支援使用者将單列資料轉換為多列資料,Apache Spark 2.4版本擴充了這部分功能, 本文将以西雅圖當地氣溫為基礎,介紹如何通過SQL pivot實作複雜資料的轉換。
Pivot氣溫檢查
下表是今年西雅圖7月氣溫最高的9天氣溫溫度, 最高可達90華氏度!
假設我們想知道西雅圖氣溫曆史上是否存在過這種趨勢,最直覺的方法是将氣溫資料按照下列方式進行展示,月份作為列,年份作為行,值為當月平均最高氣溫,這種資料組織方式橫向可以比較相鄰月份的氣溫,縱向可以比較不同年份統一月份的平均最高氣溫
使用者可以通過Spark SQL的Pivot功能來實作上述功能,下面為詳細的Spark SQL語句
上述SQL語句輸出如下:
SQL Pivot
下面我們來分析這個SQL語句是如何工作的, 首先需要指定FROM語句,該語句作為pivot語句的輸入,在上述示例中FROM語句包含了年份,月份以及平均最高溫度,其次PIVOT語句, 聚合函數avg作為PIVOT語句第一個參數指定需要聚合的列,接着在FOR語句中指定pivot列以及在IN算子指定Pivot列的值, pivot将依據列值轉換為多列資料,我們還可以在IN中指定pivot列值别名,使輸出列名更加易讀,上述示例中為每個列值顯示對應的月份。
Pivot可以根據隐式的group-by列以及pivot列進行分組,其中隐式的group-by列是出現在FROM語句中,但是沒有出現在聚合函數或者pivot列中,上述查詢中月份作為pivot列,年份作為隐式的group-by列, avg(temp)作為(year, month)鍵值對的值, month的值是在FOR語句指定的, 另外需要注意的是如果pivot輸出不需要的列從FROM語句中剔除, 否則查詢會産生與預期不一樣的輸出。
多聚合
上述PIVOT語句隻包含一個聚合語句,顯示使用者會指定多個聚合語句,基于上述溫度資料,我們列出6月份到9月份最高溫度和最低溫度。下面是SQL語句的示例:
在多聚合函數語句,列的名稱是pivot列和聚合函數的笛卡爾乘積,名稱格式為<value>_<aggExpr>, 下面是上述SQL語句的輸出:
Grouping columns 和 Pivot Columns
假設我們要在溫度趨勢中包含最低溫度,下表列舉了每天的最低溫度
我們通過UNION ALL語句把兩張表合并,下面為合并語句
接着基于新生成的表執行Pivot查詢
上述SQL語句可以獲得過去4年每月的最低溫度和最高溫度,需要注意的是pivot查詢包含flag列,否則會出現最高溫度最低溫度混合輸出.
上述結果中,每年包含兩行資料,分别為最高溫度和最低溫度,這是因為在pivot輸入包含flag列,該列會作為pivot輸出隐式group-by列, flag除了可以作為group-by的列,還可以作為pivot列,這樣就可以擁有兩個pivot列:month和flag, 示例SQL語句如下:
輸出結果與flag作為group-by有所不同,如下所示,每行表示一年資料,每月的資料包含兩列