Databricksでこのノートブックを試してください
2018年11月10日更新
PivotはApacheSpark 1.6で、ユーザーが1つの列の一意の値を個々の列に変換することでテーブル値の式をローテーションできる新しいDataFrame機能として最初に導入されました。
Apache Spark2.4リリースデータをピボットするこの強力な機能をSQLユーザーにも拡張します。このブログでは、シアトルでの気温の記録を使用して、この一般的なSQLピボット機能を使用して複雑なデータ変換を実現する方法を紹介します。
ピボットを使用した夏の気温の調査
この夏シアトルでは、7月の9日間、気温が不快なレベルに上昇し、80年代と90年代にピークを迎えました。
| 日付 | 温度（°F） |
|---|---|
| 07-22-2018 | 86 |
| 07-23-2018 | 90 |
| 07-24-2018 | 91 |
| 07-25-2018 | 92 |
| 07-26-2018 | 92 |
| 07-27-2018 | 88 |
| 07-28-2018 | 85 |
| 07-29-2018 | 94 |
| 07-30-2018 | 89 |
歴史的なトレンがあったかどうかを調査または調査したいとします。 d水銀レベルの上昇。これらの数値を調べて表示する直感的な方法の1つは、月を列として、次に毎年の月平均の最高値を1行にすることです。そうすれば、水平方向（隣接する月間）と垂直方向（異なる年間）の両方で温度を簡単に比較できます。
これで、PIVOT構文がサポートされるようになりました。 Spark SQLでは、次のSQLクエリを使用してこれを実現できます。
上記のクエリは、次のような結果を生成します。
まあ、良い年と悪い年があるようです。 2016年は、かなりエネルギーに優しい年のようです。
SQLでのピボット
このクエリを詳しく見て、どのように機能するかを理解しましょう。まず、ピボットの入力であるFROM句、つまり、ピボットの実行に基づくテーブルまたはサブクエリを指定する必要があります。私たちの場合、年、月、高温が心配なので、これらはサブクエリに表示されるフィールドです。
次に、クエリのもう1つの重要な部分について考えてみましょう。 PIVOT句。 PIVOT句の最初の引数は、集計関数と集計される列です。次に、FORサブ句のピボット列を2番目の引数として指定し、その後にピボット列の値を含むIN演算子を指定します。最後の引数。
ピボット列はテーブルが回転するポイントであり、ピボット列の値は出力テーブルの列に転置されます。 IN句を使用すると、ピボット値ごとにエイリアスを指定できるため、より意味のある列名を簡単に生成できます。
ピボットに関する重要なアイデアは次のとおりです。暗黙のgroup-by列とピボット列のリストに基づいてグループ化された集計を実行します。暗黙のgroup-by列は、FROM句の列であり、集計関数やピボット列には表示されません。
上記のクエリでは、ピボット列が列月で、暗黙のgroup-by列が列年であり、式avg(temp)は、(year, month)の個別の値のペアごとに集計されます。ここで、monthは指定されたピボット列の値の1つに等しくなります。その結果、これらの集計値はそれぞれ、行yearおよびcolumn月の対応するセルにマップされます。
この暗黙のgroup-byのため、ピボット出力の一部にしたくない列を除外する必要があることに注意してください。 FROM句。そうしないと、クエリで望ましくない結果が生成されます。
複数の集計式の指定
上記の例は、使用されている集計式を1つだけ示しています。 PIVOT句では、実際には、ユーザーは必要に応じて複数の集計式を指定できます。繰り返しになりますが、上記の気象データを使用して、6月から9月までの平均最高気温とともに最高最高気温を一覧表示できます。
複数の集計式の場合、列はピボットの直積になります。 <value>_<aggExpr>という名前の列値と集計式。
列とピボット列のグループ化
ここで、毎日の低温のこのテーブルからの温度傾向の調査に低温を含めたいとします。
| 日付 | 温度（°F） |
|---|---|
| … | … |
| 2018年8月1日 | 59 |
| 2018年8月2日 | 58 |
| 08-03-2018 | 59 |
| 08-04-2018 | 58 |
| 08-05-2018 | 59 |
| 08-06-2018 | 59 |
| … | … |
このテーブルを前の毎日の高温のテーブルと組み合わせるために、これら2つのテーブルを[日付]列で結合できます。ただし、日付でグループ化を実行するピボットを使用するため、 UNION ALL。また、後で説明しますが、このアプローチにより柔軟性も向上します。
次に、新しい結合テーブルを使用してピボットクエリを試してみましょう。
その結果、過去4年間の各月の平均最高値と平均最低値が1つのテーブルに表示されます。ピボットクエリに列flagを含める必要があることに注意してください。そうしないと、式avg(temp)は高低の組み合わせに基づいてしまいます。
お気づきかもしれませんが、今では毎年2つの行があり、1つは高温用、もう1つは低温用です。これは、ピボット入力にもう1つの列flagが含まれているためです。これは、元の列year。
または、グループ化列ではなく、flagをピボット列として使用することもできます。これで、monthとflagの2つのピボット列ができました：
このクエリは、次の異なるレイアウトを示します。同じデータで、年ごとに1行、月ごとに2列です。
次のステップ
このブログで使用されているクエリの例を実行するには、ピボットSQLの例を確認してください。
Apache Sparkコミュニティの貢献者の貢献に感謝します！
