1 から 10 のスケールで、あなたのデータ ウェアハウス スキルはどの程度優れていますか? 7/10 を超えたいですか？この記事はあなたのためのものです。 あなたのSQLはどれくらい良いですか？できるだけ早く就職の面接の準備をしたいですか? このブログ投稿では、最も複雑なデータ ウェアハウスの SQL 手法について詳しく説明しています。 BigQuery の標準 SQL 方言を使用して、このトピックに関するいくつかの考えを書き留めます。 1. 増分テーブルと MERGE テーブルの更新は重要です。それは確かに重要です。理想的な状況は、プライマリ キー、一意の整数、および自動インクリメントであるトランザクションがある場合です。この場合のテーブルの更新は簡単です。 https://gist.github.com/mshakhomirov/18775cbbe8288af864ad79247c0de63d?embedable=true#file-1-1-sql 最新のデータ ウェアハウスで非正規化されたスター スキーマ データセットを操作する場合は、必ずしもそうではありません。 SQL を使用して 作成したり、データの一部のみを使用してデータセットを増分更新したりする必要がある場合があります。 存在しない可能性がありますが、代わりに、一意のキーが最新の (またはタイムスタンプ) に依存するデータ モデルを処理する必要があります。たとえば、 データセットの 最新の既知の接続タイムスタンプに依存します。この場合、既存のユーザー 新しいユーザー 。 セッションを transaction_id transaction_id last_online user_id update insert MERGE と増分更新 使用するか、操作を 2 つのアクションに分割できます。 1 つは既存のレコードを新しいレコードで更新し、もう 1 つは存在しない完全に新しいレコードを挿入します (LEFT JOIN の状況)。 MERGE を 、リレーショナル データベースで一般的に使用されるステートメントです。 Google BigQuery MERGE コマンドは、Data Manipulation Language (DML) ステートメントの 1 つです。多くの場合、1 つのステートメントで 3 つの主要な機能をアトミックに実行するために使用されます。これらの関数は、UPDATE、INSERT、および DELETE です。 MERGE は UPDATE または DELETE 句は、2 つ以上のデータが一致する場合に使用できます。 INSERT 句は、2 つ以上のデータが異なり、一致しない場合に使用できます。 UPDATE または DELETE 句は、指定されたデータがソースと一致しない場合にも使用できます。 これは、Google BigQuery MERGE コマンドを使用すると、Google BigQuery テーブルのデータを更新、挿入、および削除することで、Google BigQuery データをマージできることを意味します。 次の SQL を検討してください。 https://gist.github.com/mshakhomirov/5ad1a7518c54bc030d1c78b56fe3cf82?embedable=true#file-1-2-sql 2.単語を数える UNNEST() を実行して、必要な単語が必要なリストに含まれているかどうかを確認することは、データ ウェアハウスのセンチメント分析など、多くの状況で役立つ可能性があります。 https://gist.github.com/mshakhomirov/694e040539b0d1b556f8e053d315a3bf?embedable=true#file-2-sql 3. SELECT ステートメントの外で IF() ステートメントを使用する これにより、いくつかのコード行を節約し、コードに関してより雄弁になる機会が得られます。通常、これをサブクエリに入れ、 句にフィルターを追加しますが、代わりに ようにすることもできます。 where 次の https://gist.github.com/mshakhomirov/933e6a358e49dcccd4e547a5509c8fda?embedable=true#file-3-sql テーブルで使用し 別の例。 。一致するテーブル サフィックスはおそらく (テーブル内の何かに基づいて) 動的に決定されるため、これは悪い例です 分割された ない これをしないでください 。テーブル全体のスキャンに対して料金が発生します。 https://gist.github.com/mshakhomirov/1c62d79cd9690140c569cd047b9d491f?embedable=true#file-3-2-sql 句および 関数でも使用できます。 HAVING AGGREGATE 4. GROUP BY ROLLUP の使用 ROLLUP 関数は、複数のレベルで集計を実行するために使用されます。これは、ディメンション グラフを操作する必要がある場合に便利です。 次のクエリは、 句で指定されたトランザクション タイプ (is_gift) ごとに 1 日あたりの合計クレジット支出を返します。また、各日の合計支出と、利用可能なすべての日付の合計支出も示します。 where https://gist.github.com/mshakhomirov/4cf738aaad967fe92c4fb7192874fadf?embedable=true#file-4-sql 5. テーブルを JSON に変換する 各レコードがネストされた配列の要素である JSON オブジェクトにテーブルを変換する必要があると想像してください。これは 関数が役立つ場所です: to_json_string() https://gist.github.com/mshakhomirov/aac1f93312ae305ba80c915fe4a2a386?embedable=true#file-5-sql その後、日付、マーケティング ファネル、インデックス、ヒストグラム グラフなど、どこでも使用できます。 6. PARTITION BY の使用 指定された 、 、および 列。各日付について、すべての行を保持しながら、各顧客の合計収益値をどのように表示しますか?これは次のように達成できます。 user_id date total_cost https://gist.github.com/mshakhomirov/e4f11721eb5a3182150df08f25b70d64?embedable=true#file-6-sql 7. 移動平均 多くの場合、BI 開発者は、移動平均をレポートや素晴らしいダッシュボードに追加する任務を負っています。これは、7 日、14 日、30 日/月、または年 MA の折れ線グラフです。では、どうすればよいのでしょうか。 https://gist.github.com/mshakhomirov/ebf5488d0036bc9b84ae05889346d986?embedable=true#file-7-sql 8. 日付配列 を扱う場合や、データセットの欠損値 (日付など) を確認したい場合に非常に便利です。 BigQuery には という関数があります。 ユーザー維持率 GENERATE_DATE_ARRAY https://gist.github.com/mshakhomirov/2ba5a67053f85794462dab98e56ad74d?embedable=true#file-8-sql 9. 行番号() これは、データから最新のもの、つまり最新の更新されたレコードなどを取得したり、重複を削除したりするのに役立ちます。 https://gist.github.com/mshakhomirov/05d0c04c5975207d98552ffd436add8b?embedable=true#file-9-sql 10. NTILE() 別の番号付け機能。モバイルアプリを使用している場合、 監視するのに非常に便利です。たとえば、アプリを Firebase に接続していて、ユーザーが と、どれくらいの時間がかかったかを確認できます。 Login duration in seconds login この関数は、行の順序に基づいて行を バケットに分割し、各行に割り当てられた 1 ベースのバケット番号を返します。バケット内の行数の差は、最大で 1 です。残りの値 (行数をバケットで割った余り) は、 1 から始めて、バケットごとに 1 つずつ分配されます。エラーが表示されます。 constant_integer_expression constant_integer_expression https://gist.github.com/mshakhomirov/16fe941aa8c4ed79e4aad8b7049b307a?embedable=true#file-10-sql 11.ランク/dense_rank 関数とも呼ばれます。 では次に利用可能なランキングをスキップしないため、 使用する傾向があります。連続したランク値を返します。結果を個別のバケットに分割するパーティションで使用できます。各パーティション内の行は、同じ値を持つ場合、同じランクを受け取ります。 ナンバリング RANK DENSE_RANK デフォルトのランキング関数として 例： https://gist.github.com/mshakhomirov/459b68c5f3d1e8284c01e516db1d8dcb?embedable=true#file-11-1-sql 製品価格の別の例: https://gist.github.com/mshakhomirov/4c90a6fc8516d8264e172676a83a1048?embedable=true#file-11-2-sql 12. ピボット/アンピボット ピボットは行を列に変更します。それだけです。 Unpivot はその です。 逆 https://gist.github.com/mshakhomirov/f90b035ba259e672d4d51a669e0cd1fc?embedable=true#file-12-sql 13.最初の値/最後の値 これは、特定のパーティションの最初/最後の値に対する各行のデルタを取得するのに役立つ別の便利な関数です。 https://gist.github.com/mshakhomirov/ea4de9144b97bf8c196cab07609c309e?embedable=true#file-13-sql 14. テーブルを構造体の配列に変換して UDF に渡す これは、複雑なロジックを持つユーザー定義関数 (UDF) を各行またはテーブルに適用する必要がある場合に役立ちます。テーブルを常に TYPE STRUCT オブジェクトの配列と見なし、それぞれを UDF に渡すことができます。それはあなたの論理に依存します。たとえば、購入の有効期限を計算するために使用します。 https://gist.github.com/mshakhomirov/35d956fa9db86b12b44ab62c00f42a40?embedable=true#file-14-sql 同様に、 を使用せずにテーブルを作成できます。たとえば、単体テスト用のテスト データをモックするために使用します。このようにして、エディターで + + を使用するだけで非常に高速に実行できます。 UNION ALL Alt Shift Down https://gist.github.com/mshakhomirov/6ea226c1b5b789d4a31691ce065c20d7?embedable=true#file-14-2-sql 15. FOLLOWING と UNBOUNDED FOLLOWING を使用してイベント ファネルを作成する 良い例は、マーケティング ファネルかもしれません。データセットには、同じタイプの継続的に繰り返されるイベントが含まれている場合がありますが、理想的には、各イベントを別のタイプの次のイベントと連鎖させたいと考えています。これは、ファネル データセットを構築するために、イベント、購入などのリストを取得する必要がある場合に役立ちます。 PARTITION BY を使用すると、各パーティションに存在するイベントの数に関係なく、後続のすべてのイベントをグループ化する機会が得られます。 https://gist.github.com/mshakhomirov/05fd7d79d8acf3b173181a5d950ab6e7?embedable=true#file-15-sql 16.正規表現 構造化されていないデータから何かを抽出する必要がある場合に使用します。つまり、為替レート、カスタム グループなどです。 正規表現を使用した為替レートの操作 為替レート データを使用した次の例を考えてみましょう。 https://gist.github.com/mshakhomirov/9ca6e153da19c491034bd57995875308?embedable=true#file-16-1-sql 正規表現を使用したアプリ バージョンの操作 アプリの 、 バージョン、または バージョンを取得し、カスタム レポートを作成するために、正規 を使用したい場合があります。 メジャー バージョン リリース mod regexp https://gist.github.com/mshakhomirov/b1f442a296ffef52c7baa1245e1dc316?embedable=true#file-16-2-sql 結論 SQL は、データの操作に役立つ強力なツールです。デジタル マーケティングのこれらの SQL ユース ケースが役立つことを願っています。これは確かに便利なスキルであり、多くのプロジェクトで役立ちます。これらの SQL スニペットのおかげで私の生活はとても楽になり、ほぼ毎日のように仕事で使用しています。さらに、SQL と最新のデータ ウェアハウスは、データ サイエンスに不可欠なツールです。その堅牢な方言機能により、データを簡単にモデル化および視覚化できます。 SQL はデータ ウェアハウスやビジネス インテリジェンスの専門家が使用する言語であるため、それらとデータを共有する場合に最適な選択肢です。これは、市場のほぼすべてのデータ ウェアハウス / レイク ソリューションと通信するための最も一般的な方法です。 最初に で によって公開されました mydataschool.com datamike マイクは情熱的でデジタルに焦点を当てた個人であり、豊富な意欲と熱意を持ち、デジタル マーケティングの完全な組み合わせが投げかける課題を愛しています。英国在住、2015年ニューキャッスル大学MBA修了。

Read me on Medium.com

このオーディオは、ストーリーの元の言語で制作されています。

長すぎる; 読むには

Making security fun one episode at a time

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

長すぎる; 読むには

Mike Shakhomirov

About Author

ラベル

Languages

この記事は...

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

長すぎる; 読むには

Mike Shakhomirov

About Author

ラベル

Languages

この記事は...

関連ストーリー