5つの複雑なSQL問題を解決する：トリッキーなクエリの説明
5 complex SQL problems solved Explanation of tricky queries.
多くの人々が、クラウドデータウェアハウス内でのコンピュートの集中によって提供される速度と効率の核心的な力を経験してきました。これは事実ですが、この価値には独自の欠点があります。
この方法の主な欠点の1つは、異なる言語、特にSQLでクエリを学習し実行する必要があることです。 SQLを書くことは、Pythonを立ち上げるよりも速く、コストがかからないという点で優れていますが、クラウドウェアハウスから抽出したい情報によっては、多くの異なる複雑さがあります。クラウドデータウェアハウスへの切り替えにより、複雑なSQLの使用価値がPythonに対して高まります。私自身がこの経験をしたので、SQLで最も学習と実行が困難な変換を記録し、読者の皆様にこの苦痛を軽減するために必要な実際のSQLを提供することにしました。
ワークフローを支援するために、変換が実行される前と後のデータ構造の例を提供しているため、作業を追跡して作業を検証できます。各5つの最も困難な変換を実行するために必要な実際のSQLも提供しています。データが変化するたびに、複数のプロジェクトで変換を実行するための新しいSQLが必要になります。必要に応じて解析に必要なSQLをキャプチャできるように、各変換用の動的SQLへのリンクを提供しました！
日付スパイン
「日付スパイン」という用語がどこから発生したかは明確ではありませんが、この用語を知らない人でも、おそらくそれが何であるかは知っているでしょう。
日次の売上データを分析しているとしましょう。以下のようになっています：
| sales_date | product | sales |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-18 | A | 65 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
16日と17日には売上がなかったため、行が完全に欠落しています。平均日次売上を算出したり、時系列予測モデルを構築する場合、この形式は大きな問題です。欠落している日に対して行を挿入する必要があります。
基本的な考え方は次のとおりです：
- 一意の日付を生成または選択する
- 一意の製品を生成または選択する
- 1&2のすべての組み合わせをクロス結合する（直積）
- ＃3を元のデータに外部結合する
日付スパインのカスタマイズ可能なSQL
最終結果は次のようになります：
| sales_date | product | sales |
| 2022-04-14 | A | 46 |
| 2022-04-14 | B | 409 |
| 2022-04-15 | A | 17 |
| 2022-04-15 | B | 480 |
| 2022-04-16 | A | 0 |
| 2022-04-16 | B | 0 |
| 2022-04-17 | A | 0 |
| 2022-04-17 | B | 0 |
| 2022-04-18 | A | 65 |
| 2022-04-18 | B | 0 |
| 2022-04-19 | A | 45 |
| 2022-04-19 | B | 411 |
ピボット / アンピボット
分析を行う際、テーブルを再構成したい場合があります。例えば、生徒、科目、成績のリストがあるが、科目をそれぞれの列に分けたい場合です。私たちは皆、エクセルのピボットテーブルを愛しています。しかし、SQLでそれを行おうとしたことはありますか？すべてのデータベースがPIVOTをサポートする方法について厄介な違いを持っているだけでなく、構文は直感的ではなく、簡単に忘れてしまいます。
前の表：
| 生徒 | 科目 | 成績 |
| Jared | 数学 | 61 |
| Jared | 地理 | 94 |
| Jared | 体育 | 98 |
| Patrick | 数学 | 99 |
| Patrick | 地理 | 93 |
| Patrick | 体育 | 4 |
カスタマイズ可能なピボット用SQL
結果：
| 生徒 | 数学 | 地理 | 体育 |
| Jared | 61 | 94 | 98 |
| Patrick | 99 | 93 | 4 |
ワンホットエンコーディング
これは必ずしも難しいわけではありませんが、時間がかかります。多くのデータサイエンティストはSQLでワンホットエンコーディングを行うことを考えません。構文は簡単でも、彼らはデータウェアハウスからデータを転送する方が、26行のCASE文を書く煩雑な作業よりも良いと考えているでしょう。私たちは彼らを責めません！
しかし、データウェアハウスとその処理能力を活用することをお勧めします。ここでは、STATEを列としてワンホットエンコードする例を示します。
前の表：
| ベビー名 | 州 | 数量 |
| アリス | AL | 156 |
| アリス | AK | 146 |
| アリス | PA | 654 |
| … | … | … |
| ゼルダ | NY | 417 |
| ゼルダ | AL | 261 |
| ゼルダ | CO | 321 |
カスタマイズ可能なOne-Hotエンコード用SQL
結果：
| ベビー名 | 州 | 州_AL | 州_AK | … | 州_CO | 数量 |
| アリス | AL | 1 | 0 | … | 0 | 156 |
| アリス | AK | 0 | 1 | … | 0 | 146 |
| アリス | PA | 0 | 0 | … | 0 | 654 |
| … | … | … | … | |||
| ゼルダ | NY | 0 | 0 | … | 0 | 417 |
| ゼルダ | AL | 1 | 0 | … | 0 | 261 |
| ゼルダ | CO | 0 | 0 | … | 1 | 321 |
マーケットバスケット分析
マーケットバスケット分析または関連ルールをマイニングする場合、最初のステップはしばしばトランザクションごとに集計して、1つのレコードにまとめることです。これはあなたのラップトップにとって課題であるかもしれませんが、データウェアハウスはこのデータを効率的に処理するように設計されています。
典型的なトランザクションデータ：
| 販売注文番号 | 顧客キー | 英語製品名 | 定価 | 重量 | 注文日 |
| SO51247 | 11249 | Mountain-200 Black | 2294.99 | 23.77 | 1/1/2013 |
| SO51247 | 11249 | Water Bottle – 30 oz. | 4.99 | 1/1/2013 | |
| SO51247 | 11249 | Mountain Bottle Cage | 9.99 | 1/1/2013 | |
| SO51246 | 25625 | Sport-100 Helmet | 34.99 | 12/31/2012 | |
| SO51246 | 25625 | Water Bottle – 30 oz. | 4.99 | 12/31/2012 | |
| SO51246 | 25625 | Road Bottle Cage | 8.99 | 12/31/2012 | |
| SO51246 | 25625 | Touring-1000 Blue | 2384.07 | 25.42 | 12/31/2012 |
カスタマイズ可能なMarket Basket用SQL
結果:
| NUMTRANSACTIONS | ENGLISHPRODUCTNAME_LISTAGG |
| 207 | Mountain Bottle Cage, Water Bottle – 30 oz. |
| 200 | Mountain Tire Tube, Patch Kit/8 Patches |
| 142 | LL Road Tire, Patch Kit/8 Patches |
| 137 | Patch Kit/8 Patches, Road Tire Tube |
| 135 | Patch Kit/8 Patches, Touring Tire Tube |
| 132 | HL Mountain Tire, Mountain Tire Tube, Patch Kit/8 Patches |
時系列集計
時系列集計はデータサイエンティストだけでなく、アナリストにも使用されます。しかし、ウィンドウ関数を使用する場合、データが正しくフォーマットされている必要があるため、難しいとされています。
たとえば、過去14日間の平均販売額を計算したい場合、ウィンドウ関数を使用するには、すべての販売データを1日ごとに分割する必要があります。残念ながら、販売データを扱ったことがある人は、通常、トランザクションレベルで保存されることを知っています。ここで、時系列集計が役立ちます。データセット全体を再フォーマットすることなく、集約された履歴的なメトリックを作成できます。また、1度に複数のメトリックを追加したい場合にも役立ちます:
- 過去14日間の平均販売額
- 過去6か月間の最大購入額
- 過去90日間の異なる製品タイプの数をカウントする
ウィンドウ関数を使用する場合、各メトリックを複数のステップで独立に構築する必要があります。
これを処理するより良い方法は、共通テーブル式（CTE）を使用して、事前に集約された各履歴ウィンドウを定義することです。
例:
| Transaction ID | Customer ID | Product Type | Purchase Amt | Transaction Date |
| 65432 | 101 | Grocery | 101.14 | 2022-03-01 |
| 65493 | 101 | Grocery | 98.45 | 2022-04-30 |
| 65494 | 101 | Automotive | 239.98 | 2022-05-01 |
| 66789 | 101 | Grocery | 86.55 | 2022-05-22 |
| 66981 | 101 | Pharmacy | 14 | 2022-06-15 |
| 67145 | 101 | Grocery | 93.12 | 2022-06-22 |
カスタマイズ可能な時系列集計SQLに対応
結果:
| トランザクションID | 顧客ID | 製品種別 | 購入金額 | トランザクション日 | 過去14日間の平均売上 | 過去6ヶ月の最大購入金額 | 過去90日間の製品種別のユニーク数 |
| 65432 | 101 | 食料品 | 101.14 | 2022-03-01 | 101.14 | 101.14 | 1 |
| 65493 | 101 | 食料品 | 98.45 | 2022-04-30 | 98.45 | 101.14 | 2 |
| 65494 | 101 | 自動車 | 239.98 | 2022-05-01 | 169.21 | 239.98 | 2 |
| 66789 | 101 | 食料品 | 86.55 | 2022-05-22 | 86.55 | 239.98 | 2 |
| 66981 | 101 | 薬局 | 14 | 2022-06-15 | 14 | 239.98 | 3 |
| 67145 | 101 | 食料品 | 93.12 | 2022-06-22 | 53.56 | 239.98 | 3 |
結論
この記事が、現代のデータスタック内でデータプラクティショナーが直面するさまざまな問題について、少しでも疑問を解消するのに役立つことを願っています。クラウドデータウェアハウスでのクエリ処理に関しては、SQLは二律背反の剣です。クラウドデータウェアハウスでの計算を集中させることで速度が向上しますが、時には追加のSQLスキルが必要になります。この記事がこれらの問題に対処するために必要な構文と背景を提供し、また質問に答えるのに役立つことを願っています。
Josh Berry（@Twitter）は、Rasgoの顧客向けデータサイエンスをリードし、2008年以来データと分析に従事しています。Joshは、Comcastで10年間データサイエンスチームを構築し、市場に出回った最初のフィーチャーストアの1つであるComcastの内部開発されたフィーチャーストアの重要なオーナーでした。Comcastを退社後、JoshはDataRobotで顧客向けデータサイエンスの構築に重要なリーダーの1人でした。余暇には、野球、F1レース、住宅市場の予測など、興味深いトピックについて複雑な分析を行っています。
We will continue to update VoAGI; if you have any questions or suggestions, please contact us!
Was this article helpful?
93 out of 132 found this helpful
Related articles
