すべての初心者が知っておくべき 16 の SQL テクニック

1 から 10 のスケールで、あなたのデータ ウェアハウス スキルはどの程度優れていますか?

7/10 を超えたいですか？この記事はあなたのためのものです。

あなたのSQLはどれくらい良いですか？できるだけ早く就職の面接の準備をしたいですか?

このブログ投稿では、最も複雑なデータ ウェアハウスの SQL 手法について詳しく説明しています。 BigQuery の標準 SQL 方言を使用して、このトピックに関するいくつかの考えを書き留めます。

1. 増分テーブルと MERGE

テーブルの更新は重要です。それは確かに重要です。理想的な状況は、プライマリ キー、一意の整数、および自動インクリメントであるトランザクションがある場合です。この場合のテーブルの更新は簡単です。

最新のデータ ウェアハウスで非正規化されたスター スキーマ データセットを操作する場合は、必ずしもそうではありません。 SQL を使用してセッションを作成したり、データの一部のみを使用してデータセットを増分更新したりする必要がある場合があります。 transaction_id存在しない可能性がありますが、代わりに、一意のキーが最新のtransaction_id (またはタイムスタンプ) に依存するデータ モデルを処理する必要があります。たとえば、 last_onlineデータセットのuser_id最新の既知の接続タイムスタンプに依存します。この場合、既存のユーザーupdate新しいユーザーinsert 。

MERGE と増分更新

MERGE を使用するか、操作を 2 つのアクションに分割できます。 1 つは既存のレコードを新しいレコードで更新し、もう 1 つは存在しない完全に新しいレコードを挿入します (LEFT JOIN の状況)。

MERGE は、リレーショナル データベースで一般的に使用されるステートメントです。 Google BigQuery MERGE コマンドは、Data Manipulation Language (DML) ステートメントの 1 つです。多くの場合、1 つのステートメントで 3 つの主要な機能をアトミックに実行するために使用されます。これらの関数は、UPDATE、INSERT、および DELETE です。

• UPDATE または DELETE 句は、2 つ以上のデータが一致する場合に使用できます。
• INSERT 句は、2 つ以上のデータが異なり、一致しない場合に使用できます。
• UPDATE または DELETE 句は、指定されたデータがソースと一致しない場合にも使用できます。

これは、Google BigQuery MERGE コマンドを使用すると、Google BigQuery テーブルのデータを更新、挿入、および削除することで、Google BigQuery データをマージできることを意味します。

次の SQL を検討してください。

2.単語を数える

UNNEST() を実行して、必要な単語が必要なリストに含まれているかどうかを確認することは、データ ウェアハウスのセンチメント分析など、多くの状況で役立つ可能性があります。

3. SELECT ステートメントの外で IF() ステートメントを使用する

これにより、いくつかのコード行を節約し、コードに関してより雄弁になる機会が得られます。通常、これをサブクエリに入れ、 where句にフィルターを追加しますが、代わりに次のようにすることもできます。

分割されたテーブルで使用しない別の例。これをしないでください。一致するテーブル サフィックスはおそらく (テーブル内の何かに基づいて) 動的に決定されるため、これは悪い例です。テーブル全体のスキャンに対して料金が発生します。

HAVING句およびAGGREGATE関数でも使用できます。

4. GROUP BY ROLLUP の使用

ROLLUP 関数は、複数のレベルで集計を実行するために使用されます。これは、ディメンション グラフを操作する必要がある場合に便利です。

次のクエリは、 where句で指定されたトランザクション タイプ (is_gift) ごとに 1 日あたりの合計クレジット支出を返します。また、各日の合計支出と、利用可能なすべての日付の合計支出も示します。

5. テーブルを JSON に変換する

各レコードがネストされた配列の要素である JSON オブジェクトにテーブルを変換する必要があると想像してください。これはto_json_string()関数が役立つ場所です:

その後、日付、マーケティング ファネル、インデックス、ヒストグラム グラフなど、どこでも使用できます。

6. PARTITION BY の使用

指定されたuser_id 、 date 、およびtotal_cost列。各日付について、すべての行を保持しながら、各顧客の合計収益値をどのように表示しますか?これは次のように達成できます。

7. 移動平均

多くの場合、BI 開発者は、移動平均をレポートや素晴らしいダッシュボードに追加する任務を負っています。これは、7 日、14 日、30 日/月、または年 MA の折れ線グラフです。では、どうすればよいのでしょうか。

8. 日付配列

ユーザー維持率を扱う場合や、データセットの欠損値 (日付など) を確認したい場合に非常に便利です。 BigQuery にはGENERATE_DATE_ARRAYという関数があります。

9. 行番号()

これは、データから最新のもの、つまり最新の更新されたレコードなどを取得したり、重複を削除したりするのに役立ちます。

10. NTILE()

別の番号付け機能。モバイルアプリを使用している場合、 Login duration in seconds監視するのに非常に便利です。たとえば、アプリを Firebase に接続していて、ユーザーがloginと、どれくらいの時間がかかったかを確認できます。

この関数は、行の順序に基づいて行をconstant_integer_expressionバケットに分割し、各行に割り当てられた 1 ベースのバケット番号を返します。バケット内の行数の差は、最大で 1 です。残りの値 (行数をバケットで割った余り) は、 constant_integer_expression 1 から始めて、バケットごとに 1 つずつ分配されます。エラーが表示されます。

11.ランク/dense_rank

ナンバリング関数とも呼ばれます。 RANKでは次に利用可能なランキングをスキップしないため、 DENSE_RANKデフォルトのランキング関数として使用する傾向があります。連続したランク値を返します。結果を個別のバケットに分割するパーティションで使用できます。各パーティション内の行は、同じ値を持つ場合、同じランクを受け取ります。例：

製品価格の別の例:

12. ピボット/アンピボット

ピボットは行を列に変更します。それだけです。 Unpivot はその逆です。

13.最初の値/最後の値

これは、特定のパーティションの最初/最後の値に対する各行のデルタを取得するのに役立つ別の便利な関数です。

14. テーブルを構造体の配列に変換して UDF に渡す

これは、複雑なロジックを持つユーザー定義関数 (UDF) を各行またはテーブルに適用する必要がある場合に役立ちます。テーブルを常に TYPE STRUCT オブジェクトの配列と見なし、それぞれを UDF に渡すことができます。それはあなたの論理に依存します。たとえば、購入の有効期限を計算するために使用します。

同様に、 UNION ALLを使用せずにテーブルを作成できます。たとえば、単体テスト用のテスト データをモックするために使用します。このようにして、エディターでAlt + Shift + Downを使用するだけで非常に高速に実行できます。

15. FOLLOWING と UNBOUNDED FOLLOWING を使用してイベント ファネルを作成する

良い例は、マーケティング ファネルかもしれません。データセットには、同じタイプの継続的に繰り返されるイベントが含まれている場合がありますが、理想的には、各イベントを別のタイプの次のイベントと連鎖させたいと考えています。これは、ファネル データセットを構築するために、イベント、購入などのリストを取得する必要がある場合に役立ちます。 PARTITION BY を使用すると、各パーティションに存在するイベントの数に関係なく、後続のすべてのイベントをグループ化する機会が得られます。

16.正規表現

構造化されていないデータから何かを抽出する必要がある場合に使用します。つまり、為替レート、カスタム グループなどです。

正規表現を使用した為替レートの操作

為替レート データを使用した次の例を考えてみましょう。

正規表現を使用したアプリ バージョンの操作

アプリのメジャー バージョン、リリースバージョン、またはmodバージョンを取得し、カスタム レポートを作成するために、正規regexpを使用したい場合があります。

結論

SQL は、データの操作に役立つ強力なツールです。デジタル マーケティングのこれらの SQL ユース ケースが役立つことを願っています。これは確かに便利なスキルであり、多くのプロジェクトで役立ちます。これらの SQL スニペットのおかげで私の生活はとても楽になり、ほぼ毎日のように仕事で使用しています。さらに、SQL と最新のデータ ウェアハウスは、データ サイエンスに不可欠なツールです。その堅牢な方言機能により、データを簡単にモデル化および視覚化できます。 SQL はデータ ウェアハウスやビジネス インテリジェンスの専門家が使用する言語であるため、それらとデータを共有する場合に最適な選択肢です。これは、市場のほぼすべてのデータ ウェアハウス / レイク ソリューションと通信するための最も一般的な方法です。

最初にmydataschool.comでdatamikeによって公開されました

マイクは情熱的でデジタルに焦点を当てた個人であり、豊富な意欲と熱意を持ち、デジタル マーケティングの完全な組み合わせが投げかける課題を愛しています。英国在住、2015年ニューキャッスル大学MBA修了。