SQLピボット: 行を列に変換する

Databricks でこのノートブックをお試しください

データレイクハウスが次世代のデータウェアハウスである理由という電子書籍で、Databricks Lakehouse Platform の内部構造をご確認ください。

更新日: 2018 年 11 月 10 日

ピボットは、Apache Spark 1.6 で、テーブル値の式を回転させ、1 つの列からの一意の値を個別の列に変換できる新しい DataFrame 機能として初めて導入されました。

Apache Spark 2.4 リリースでは、この強力なデータピボット機能が SQL ユーザーにも拡張されました。このブログでは、シアトルの気温記録を使用して、この一般的な SQL ピボット機能を使用して複雑なデータ変換を実現する方法を紹介します。

ピボットを使用した夏の気温の調査

今年のシアトルでは、気温が不快なレベルまで上昇し、7 月には 9 日間、80 度台後半から 90 度台まで上昇しました。

水銀レベルの上昇に過去の傾向があるかどうかを調査または検討したいとします。これらの数値を検討して提示する直感的な方法の 1 つは、月を列として、各年の月ごとの平均最高値を 1 つの行に表示することです。そうすれば、隣接する月同士の水平方向と、異なる年同士の垂直方向の両方で気温を簡単に比較できます。

Spark SQL で PIVOT 構文がサポートされるようになったため、次の SQL クエリでこれを実現できます。

上記のクエリでは、次のような結果が生成されます。

そうですね、良い年と悪い年があるようです。2016 年は、エネルギー効率の良い年だったようです。

SQL でのピボット

このクエリを詳しく見て、その仕組みを理解しましょう。まず、FROM 句を指定する必要があります。これはピボットの入力、つまりピボットが実行されるテーブルまたはサブクエリです。この例では、年、月、最高気温が重要であるため、サブクエリに表示されるフィールドはそれらです。

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

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

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

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

この暗黙的な group-by のため、ピボット出力の一部にしたくない列は FROM 句から除外する必要があることに注意してください。そうしないと、クエリで望ましくない結果が生成されます。

複数の集計式の指定

上記の例では、PIVOT 句で使用されている集計式は 1 つだけですが、実際には、必要に応じて複数の集計式を指定できます。ここでも、上記の気象データを使用して、6 月から 9 月の間の平均最高気温とともに、最高気温の最大値をリストできます。

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

グループ化列とピボット列

ここで、毎日の最低気温のこのテーブルから、気温の傾向を調査する際に最低気温を含めたいとします。

このテーブルを以前の毎日の最高気温のテーブルと組み合わせるには、これらの 2 つのテーブルを「日付」列で結合します。ただし、日付でグループ化を実行するピボットを使用するため、UNION ALL を使用して 2 つのテーブルを連結するだけです。後でわかるように、このアプローチでは柔軟性も向上します。

次に、新しい結合テーブルでピボットクエリを試してみましょう。

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

各年に 2 つの行が表示されるようになったことに気付いたかもしれません。1 つは最高気温、もう 1 つは最低気温です。これは、ピボット入力に別の列 flag を含めたためです。これにより、元の列 year に加えて、別の暗黙的なグループ化列になります。

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

このクエリでは、同じデータの異なるレイアウトが表示されます。各年に 1 つの行が表示されますが、各月に 2 つの列が表示されます。

次のステップ

このブログで使用されているクエリの例を実行するには、付属のノートブックでピボット SQL の例を確認してください。

貢献してくれた Apache Spark コミュニティの貢献者に感謝します。

(このブログ記事はAI翻訳ツールを使用して翻訳されています) 原文記事