SQL でピボット（PIVOT）を使用したデータ変換：行から列への変換

Databricks の Notebook を試してみる

2018/11/10/更新

ピボット（PIVOT）は、Apache Spark 1.6 から DataFrame 機能として実装されています。一意の値を1 つの列から複数の個々の列に変換し、テーブル値式を回転させる機能です。

Apache Spark 2.4 リリースでは、この便利なピボット機能を SQL でも利用できるようになりました。このブログでは、気温のデータを対象に、SQL の PIVOT で複雑なデータ変換を容易に行う方法を解説します。UNION ALL や Spark SQL とはどんなものなのかもあわせてご紹介しています。

ピボットで夏の気温を比較する

シアトルではこの夏は普段とは異なり、7月に 9日間、最高気温が 80℉（約27℃）から 90℉ （約32℃）に達し、不快なレベルにまで上昇しました。

まず、過去に同様の気温上昇傾向があったかどうかを調べてみましょう。この場合、数値を調べて提示する直感的な方法の 1 つは、列に月を指定、行に年を指定して、各月の平均最高気温を表示させることです。そうすることで、隣り合う月の温度が横に、異なる年の温度が縦になり、両方向での比較が容易になります。

現在、データブリックスの Spark SQL （Spark SQL とは、構造化データ処理のための Spark モジュールのことです。DataFrames と呼ばれるプログラミングの抽象化が可能で、分散型 SQL クエリエンジンとしても機能します。）ではPIVOT 構文をサポートしているので、次の SQL クエリでこれを実現することができます。

SELECT * FROM (
  SELECT year(date) year, month(date) month, temp
  FROM high_temps
  WHERE date BETWEEN DATE '2015-01-01' AND DATE '2018-08-31'
)
PIVOT (
  CAST(avg(temp) AS DECIMAL(4, 1))
  FOR month in (
    1 JAN, 2 FEB, 3 MAR, 4 APR, 5 MAY, 6 JUN,
    7 JUL, 8 AUG, 9 SEP, 10 OCT, 11 NOV, 12 DEC
  )
)
ORDER BY year DESC

上記のクエリは次のような結果を生成します：

気温上昇が大きかった年とそうではない年があることがわかります。2016年は、どちらかというと省エネルギーの年だったようです。

SQL でのピボット（PIVOT）テーブル

このクエリを詳しくみて機能についての理解を深めましょう。まず、ピボットの入力である FROM 句、つまりピボットを実行するベースとなるテーブルまたはサブクエリを指定する必要があります。わたしたちは、年、月、および高い気温を調べたいので、これらがサブクエリに表示されるフィールドになります。

次に、クエリのもう 1 つの重要な部分である PIVOT 句について考えてみます。PIVOT句の最初の引数は、集計関数と集計される列です。次に、FOR サブ句のピボット列を 2 番目の引数として指定し、最後の引数としてピボット列の値を含む IN演算子を指定します。

ピボット列はテーブルを回転するポイントで、ピボット列の値は出力テーブルの列に転置されます。また、IN 句を使用すると、ピボット値ごとにエイリアスも指定でき、より意味のある列名を容易に生成できます。

SQL ピボットに関する重要な考え方は、ピボット列と共に暗黙的なgroup-by 列のリストをベースにグループ化された集計を実行することです。暗黙的なgroup-by列は、どの集計関数にもピボット列としても表示されないFROM 句の列です。

上記のクエリでは、ピボット列が month列、暗黙的な group-by 列が year 列である場合、式avg(temp) は、(year, month) の各個別値ペアに集計され、ここでは、monthは指定されたピボット列の値の 1 つに等しくなります。その結果、このそれぞれの集計値は、year行と month列の対応するセルにマップされます。

注意すべきは、この暗黙的なgroup-byが原因で、ピボット出力の一部にしたくない列をFROM 句から省くことを確認する必要があることで、そうしないとクエリが不要な結果を生成してしまいます。

複数の集計式の指定

上記の例では、PIVOT句で使用されている集計式は 1 つだけ示されていますが、実際には、ユーザーは必要に応じて複数の集計式を指定できます。繰り返しますが、上記の気象データでは、6月から9月までの高温の平均（avg）と併せて最高気温(max)を一覧表示することができます。

SELECT * FROM (
  SELECT year(date) year, month(date) month, temp
  FROM high_temps
  WHERE date BETWEEN DATE '2015-01-01' AND DATE '2018-08-31'
)
PIVOT (
  CAST(avg(temp) AS DECIMAL(4, 1)) avg, max(temp) max
  FOR month in (6 JUN, 7 JUL, 8 AUG, 9 SEP)
)
ORDER BY year DESC

複数の集計式の場合、列はピボット列の値のデカルト積と集計式で、名前は<value>_<aggExpr>となります。

列のグループ化とピボット列の比較

次に、この毎日の低い気温の表から気温傾向の調査に低い気温を含めます。

このテーブルを前に作成した高い気温の表と組み合わせるには、「日付」列でこれらの 2 つのテーブルを結合します。とはいえ、日付にグループ化を実行するピボットを使用するので、単にUNION ALL（UNION ALL とは、2 つの表やテーブルを縦に結合させる操作のことで、重複行についてはそのまま全て表示します。）を使用して 2 つのテーブルを連結するだけです。後でわかりますが、このアプローチで互換性も向上します。

SELECT date, temp, 'H' as flag
FROM high_temps
UNION ALL
SELECT date, temp, 'L' as flag
FROM low_temps

それでは、新しい結合テーブルを使用してピボットクエリを試してみましょう。

SELECT * FROM (
  SELECT year(date) year, month(date) month, temp, flag `H/L`
  FROM (
    SELECT date, temp, 'H' as flag
    FROM high_temps
    UNION ALL
    SELECT date, temp, 'L' as flag
    FROM low_temps
  )
  WHERE date BETWEEN DATE '2015-01-01' AND DATE '2018-08-31'
)
PIVOT (
  CAST(avg(temp) AS DECIMAL(4, 1))
  FOR month in (6 JUN, 7 JUL, 8 AUG, 9 SEP)
)
ORDER BY year DESC, `H/L` ASC

その結果、過去4年間の各月の高い気温の平均と低い気温の平均が1つのテーブルに表示されます。ピボットクエリに flag 列を含める必要があることに注意しないと、avg(temp) 式は高い気温と低い気温の混合がベースになってしまいます。

お気づきのとおり、各年に行が2つ作成され、気温の高い行（H）と低い行（L）に分かれています。これは、ピボット入力にもう 1つの列、flagを追加したためです。この列は元のyear列に加えて別の暗黙的なグループ化列になります。

もう 1つの方法として、グループ化列ではなく、flagをピボット列として使用することもできます。これで、month、flag の2つのピボット列ができました。

SELECT * FROM (
  SELECT year(date) year, month(date) month, temp, flag
  FROM (
    SELECT date, temp, 'H' as flag
    FROM high_temps
    UNION ALL
    SELECT date, temp, 'L' as flag
    FROM low_temps
  )
  WHERE date BETWEEN DATE '2015-01-01' AND DATE '2018-08-31'
)
PIVOT (
  CAST(avg(temp) AS DECIMAL(4, 1))
  FOR (month, flag) in (
    (6, 'H') JUN_hi, (6, 'L') JUN_lo,
    (7, 'H') JUL_hi, (7, 'L') JUL_lo,
    (8, 'H') AUG_hi, (8, 'L') AUG_lo,
    (9, 'H') SEP_hi, (9, 'L') SEP_lo
  )
)
ORDER BY year DESC

このクエリでは、各年は 1 行ですが、各月では高（hi）／低（lo）2 列ずつ表示され、同じデータの異なるレイアウトを表示できます。

次のステップ

このブログで使用されているクエリの例を実行するには、この付属 Notebook のピボット SQL のサンプルを参照してください。
SQL のピボット機能の使い方、UNION ALL や Spark SQL とはどんなものなのか知りたい方にとって参考になれば幸いです。

Apache Spark コミュニティに貢献していただいている皆様に感謝します。