データサイエンティストが知っておくべき実用的な SQL の技

![image](https://www.kdnuggets.com/wp-content/uploads/kdn-practical-sql-tricks.png)** ## # イントロダクション SELECT、WHERE、GROUP BY だけに注目するのは基本的な集計には十分ですが、多くの実際の分析タスクでは、単純なクエリを超えるパターンが必要です。具体的には、連続するアクティビティストリークの検出、支出ティアによる顧客のセグメンテーション、ノイズの多い時系列データの平滑化、あるいは行をまたぐプランアップグレード経路の追跡などが挙げられます。 この記事では、基本的な範囲を超えた 7 つの実用的な SQL パターンを紹介し、実際の分析問題を解決するための技術に焦点を当てます。 ## # データセットの設定 ここでは、架空のサブスクリプション型ソフトウェア・アズ・ア・サービス（SaaS）企業からのサンプル顧客取引テーブルを使用します： CREATE TABLE transactions ( transaction_id SERIAL PRIMARY KEY, customer_id INT, plan_type VARCHAR(20), -- 'starter', 'pro', 'enterprise' amount NUMERIC(10,2), status VARCHAR(20), -- 'completed', 'refunded', 'failed' created_at TIMESTAMP ); 7 人の顧客にわたる 36 の取引からなる完全なデータセットは、2023 年 9 月から 2024 年 6 月までの期間をカバーしており、[seed.sql](http://github.com/balapriyac/data-science-tutorials/blob/main/practical-sql-tricks/seed.sql) で利用可能です。クエリに進む前に、これを必ず実行してください。 ## # 1. LAG() を用いたイベント間の時間の計測 LAG() と LEAD() を使えば、自己結合なしで直前または次の行の値にアクセスできます。これらは、更新間隔や顧客離脱の兆候、再エンゲージメントまでの遅延など、イベント間のギャップを計算する際に特に有用です。 タスク**: 各顧客の連続した完了取引の間隔が何日経過しているかを計算してください。 SELECT customer_id, created_at, LAG(created_at) OVER ( PARTITION BY customer_id ORDER BY created_at ) AS previous_transaction_at, ROUND( EXTRACT(EPOCH FROM ( created_at - LAG(created_at) OVER ( PARTITION BY customer_id ORDER BY created_at ) )) / 86400 ) AS days_since_last FROM transactions WHERE status = 'completed' ORDER BY customer_id, created_at; 出力（一部省略）: customer_id | created_at | previous_transaction_at | days_since_last -------------+---------------------+-------------------------+----------------- 3317 | 2024-01-03 11:02:00 | | 3317 | 2024-03-15 10:45:00 | 2024-01-03 11:02:00 | 72 3317 | 2024-05-22 09:30:00 | 2024-03-15 10:45:00 | 68 4482 | 2023-09-10 09:00:00 | | 4482 | 2023-10-10 09:00:00 | 2023-09-10 09:00:00 | 30 4482 | 2023-11-10 09:14:00 | 2023-10-10 09:00:00 | 31 4482 | 2024-01-03 09:14:00 | 2023-11-10 09:14:00 | 54 4482 | 2024-03-03 08:20:00 | 2024-01-03 09:14:00 | 60 4482 | 2024-04-03 10:00:00 | 2024-03-03 08:20:00 | 31 4482 | 2024-05-01 11:00:00 | 2024-04-03 10:00:00 | 28 ... 7891 | 2024-02-01 09:00:00 | | 7891 | 2024-04-01 09:00:00 | 2024-02-01 09:00:00 | 60 7891 | 2024-05-15 09:00:00 | 2024-04-01 09:00:00 | 44 8810 | 2024-01-05 12:00:00 | | 8810 | 2024-02-05 12:00:00 | 2024-01-05 12:00:00 | 31 8810 | 2024-04-05 12:00:00 | 2024-02-05 12:00:00 | 60 (29 rows) 各顧客の最初の行では、両方の列に NULL が設定されます。これは参照できる過去のイベントが存在しないためです。EXTRACT(EPOCH ...) はタイムスタンプ間隔を秒に変換し、86400 で割ることで日数を得ます。 LEAD() 関数も同様に動作しますが、過去ではなく未来の行を参照するため、次の更新までの時間計算や、チャーン（離脱）前の最後の取引フラグ付けに有用です。 ## # 2. セルフジョインを用いた同一テーブル内での行間比較 ** [A self-join](https://www.w3schools.com/sql/sql_join_self.asp)（セルフジョイン）は、同じテーブル内の行同士を関連付けるものです。時間経過に伴う同一エンティティの 2 つのイベント（アップグレード、ダウングレード、再アクティブ化、あるいは前後のパターンなど）を比較する必要がある場合に最適なツールです。 **タスク**: 顧客がいつかスタータープランからプロプランへ、またはプロプランからエンタープライズプランへアップグレードした事例を見つけること。 SELECT DISTINCT t1.customer_id FROM transactions t1 JOIN transactions t2 ON t1.customer_id = t2.customer_id AND t1.plan_type = 'starter' AND t2.plan_type = 'pro' AND t2.created_at > t1.created_at WHERE t1.status = 'completed' AND t2.status = 'completed' ORDER BY t1.customer_id; 出力: customer_id ------------- 4482 6204 7891 (3 rows) このテーブルは t1 と t2 という 2 つのエイリアスで参照されており、それぞれ同じ顧客の異なる時点における状態を表すことができます。条件式 t2.created_at > t1.created_at は時間的な順序を強制するもので、これを指定しないと、顧客がどちらのプランタイプを持っていたかに関わらず、順序を問わないマッチングが行われ、意図しない結果（例えば順序が逆の場合）も含まれてしまいます。DISTINCT 句は、アップグレード前に複数のスタート取引があったケースを統合し、重複行が発生するのを防ぎます。 この同じ構造は、プランの降格を検出したり、離脱後に再訪した顧客を見つけたり、時間順に並べる必要がある任意の 2 つの状態を比較したりする場合にも活用できます。 ## # 3. ROW_NUMBER() を用いたグループごとの最上位行の選択 カテゴリごとに上位 N 件の行を取得したい場合（顧客ごとの最高取引額、アカウントごとの最新イベント、コホートごとの初回購入など）、共通テーブル式 (CTE) 内で ROW_NUMBER() を使用するのが標準的なアプローチです。 **タスク**: 各顧客の単一の最高額完了取引を取得する。 WITH ranked AS ( SELECT customer_id, transaction_id, amount, plan_type, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY amount DESC, created_at DESC ) AS rn FROM transactions WHERE status = 'completed' ) SELECT customer_id, transaction_id, amount, plan_type FROM ranked WHERE rn = 1 ORDER BY customer_id; 出力: customer_id | transaction_id | amount | plan_type -------------+----------------+--------+------------ 3317 | 12 | 19.00 | starter 4482 | 8 | 299.00 | enterprise 5901 | 19 | 299.00 | enterprise 6103 | 25 | 299.00 | enterprise 6204 | 28 | 79.00 | pro 7891 | 32 | 79.00 | pro 8810 | 36 | 79.00 | pro (7 rows) ROW_NUMBER() は、各パーティション内でソート順が最初となる行に 1 を割り当てます。その後、外部クエリでその行のみをフィルタリングします。created_at DESC による二次ソートはタイブレーカーとして機能し、2 つの取引金額が同じ場合、より新しい方が勝者となります。 同点を含めたい場合は、ROW_NUMBER() を RANK() に置き換えてください。RANK() は同点の行に同じ番号を割り当て、次のランクをスキップします（1, 1, 3）。一方、DENSE_RANK() も同様に処理しますが、ランクをスキップしません（1, 1, 2）。 ## # 4. NTILE(n) を用いた支出による顧客のセグメンテーション ** NTILE(n) は、順序付けられた行を n 個のおおよそ等しいバケットに分割し、各行にバケット番号を割り当てます。これは、ハードコーディングされた閾値なしで顧客ティアリング、支出の四分位数、または A/B 分析のためのコホート構築を行うための適切なツールです。 タスク**: 完了した取引の合計金額に基づき、顧客を支出四分位にランク付けしてください。 WITH customer_spend AS ( SELECT customer_id, SUM(amount) AS total_spend, COUNT(*) AS total_transactions FROM transactions WHERE status = 'completed' GROUP BY customer_id ) SELECT customer_id, total_spend, total_transactions, NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile FROM customer_spend ORDER BY total_spend DESC; 出力: customer_id | total_spend | total_transactions | spend_quartile -------------+-------------+--------------------+---------------- 5901 | 1495.00 | 5 | 4 6103 | 835.00 | 5 | 3 4482 | 653.00 | 7 | 3 8810 | 237.00 | 3 | 2 6204 | 177.00 | 3 | 2 7891 | 177.00 | 3 | 1 3317 | 57.00 | 3 | 1 (7 rows) Quartile 4 は最も支出の多い顧客層、quartile 1 は最も支出の少ない顧客層です。NTILE() 関数は支出の閾値をハードコードしないため、新規顧客が追加されるたびにバケット（グループ）が自動的に再調整されます。これにより、CASE WHEN total_spend > 500 のような静的なカットオフ値よりも堅牢性が高まります。 ## # 5. ローリングウィンドウを用いたノイズの多いデータの平滑化 ** ローリング（または移動）平均は月ごとの変動を平滑化し、時系列データにおけるトレンドをより読みやすくします。明示的な ROWS BETWEEN フレームを持つウィンドウ関数を使用すれば、含める期間の数を精密に制御できます。 タスク**: ノイズを平滑化するために、月次収益の 3 ヶ月ローリング平均を計算してください。 WITH monthly AS ( SELECT DATE_TRUNC('month', created_at)::DATE AS month, SUM(amount) AS monthly_revenue FROM transactions WHERE status = 'completed' GROUP BY DATE_TRUNC('month', created_at) ) SELECT month, monthly_revenue, ROUND(AVG(monthly_revenue) OVER ( ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2) AS revenue_3mo_avg FROM monthly ORDER BY month; 出力: month | monthly_revenue | revenue_3mo_avg -------------+-----------------+----------------- 2023-09-01 | 19.00 | 19.00 2023-10-01 | 19.00 | 19.00 2023-11-01 | 79.00 | 39.00 2024-01-01 | 275.00 | 124.33 2024-02-01 | 476.00 | 276.67 2024-03-01 | 555.00 | 435.33 2024-04-01 | 835.00 | 622.00 2024-05-01 | 775.00 | 721.67 2024-06-01 | 598.00 | 736.00 (9 rows) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は、ウィンドウ関数に対して現在の行とその前の 2 行を参照するよう指示します。最初の 2 行は過去の履歴がないため入力数が少なくなり、それぞれ 1 ヶ月平均および 2 ヶ月平均として機能します。 同じ ORDER BY 値を持つすべての行を含めたい場合は、ROWS を RANGE に置き換えてください（複数の行が同じタイムスタンプを共有する場合に有用です）。より長い平滑化を行うには、2 PRECEDING を 5 PRECEDING に変更して 6 ヶ月のウィンドウとします。 ## # 6. FILTER を用いた条件付き集計 ** FILTER を使用すると、WHERE 条件を特定の集計関数に適用でき、クエリを複数のサブクエリに分ける必要がありません。その結果、データへの単一のパスで複数の条件付き集計が可能になります。 タスク**: 月別に合計収益、返金額、失敗したトランザクション数を取得する — 1 ヶ月につき 1 行で全てを出力します。 SELECT DATE_TRUNC('month', created_at) AS month, SUM(amount) FILTER (WHERE status = 'completed') AS revenue_completed, SUM(amount) FILTER (WHERE status = 'refunded') AS revenue_refunded, COUNT(*) FILTER (WHERE status = 'failed') AS failed_count FROM transactions GROUP BY DATE_TRUNC('month', created_at) ORDER BY month; 出力: 月 | 完了収益 | 返金収益 | 失敗数 ------------------------+-------------------+------------------+-------------- 2023-09-01 00:00:00+00 | 19.00 | | 0 2023-10-01 00:00:00+00 | 19.00 | | 0 2023-11-01 00:00:00+00 | 79.00 | | 0 2024-01-01 00:00:00+00 | 275.00 | | 0 2024-02-01 00:00:00+00 | 476.00 | 79.00 | 1 2024-03-01 00:00:00+00 | 555.00 | 79.00 | 0 2024-04-01 00:00:00+00 | 835.00 | 299.00 | 0 2024-05-01 00:00:00+00 | 775.00 | | 1 2024-06-01 00:00:00+00 | 598.00 | | 2 (9 rows) FILTER の代替手段として、3 つの独立したサブクエリを結合する方法がありますが、これはコード量が増え、可読性が低下し、多くの場合実行速度も遅くなります。なお、特定の月に一致する行がない場合、SUM with FILTER は NULL（ゼロではない）を返しますが、これは正確な結果です：実際にはその月に返金が行われていないのです。ゼロを望む場合は COALESCE(..., 0) で囲んでください。 FILTER は標準 SQL であり、PostgreSQL や BigQuery で動作します。Snowflake や一部の他のデータベースでは、SUM(CASE WHEN status = 'completed' THEN amount END) を使用してください。 ## # 7. ウィンドウ関数を用いた連続アクティビティストリーク検出 ** 不連続のないシーケンスの特定——月ごとの欠損がないアクティブ期間、取引のある連続日数、サブスクリプションの継続期間など——は、SQL におけるややこしい問題の一つです。古典的な解決策では、再帰的 CTE を使用せずにウィンドウ関数を活用して行をストリーク（連続区間）にグループ化します。 この手法は、各アクティブな月に顧客ごとのパーティション内で順次行番号を割り当てます。月が本当に連続している場合、その行番号を月の日付から減算すると、ストリーク内のすべての月で同じ定数値が得られます。欠損が生じると、この定数が崩れます。 タスク**: 各顧客の連続したアクティブな月（少なくとも 1 つの完了済み取引がある月）を特定すること。 WITH monthly_activity AS ( SELECT customer_id, DATE_TRUNC('month', created_at)::DATE AS active_month FROM transactions WHERE status = 'completed' GROUP BY customer_id, DATE_TRUNC('month', created_at) ), with_prev AS ( SELECT customer_id, active_month, LAG(active_month) OVER ( PARTITION BY customer_id ORDER BY active_month ) AS prev_month FROM monthly_activity ), streak_groups AS ( SELECT customer_id, active_month, SUM(CASE WHEN active_month = prev_month + INTERVAL '1 month' THEN 0 ELSE 1 END) OVER (PARTITION BY customer_id ORDER BY active_month) AS streak_id FROM with_prev ), streaks AS ( SELECT customer_id, streak_id, MIN(active_month) AS streak_start, MAX(active_month) AS streak_end, COUNT(*) AS streak_length_months FROM streak_groups GROUP BY customer_id, streak_id ) SELECT customer_id, streak_start, streak_end, streak_length_months FROM streaks ORDER BY customer_id, streak_start; customer_id | streak_start | streak_end | streak_length_months -------------+--------------+------------+---------------------- 3317 | 2024-01-01 | 2024-01-01 | 1 3317 | 2024-03-01 | 2024-03-01 | 1 3317 | 2024-05-01 | 2024-05-01 | 1 4482 | 2023-09-01 | 2023-11-01 | 3 4482 | 2024-01-01 | 2024-01-01 | 1 4482 | 2024-03-01 | 2024-05-01 | 3 5901 | 2024-02-01 | 2024-06-01 | 5 6103 | 2024-01-01 | 2024-04-01 | 4 6103 | 2024-06-01 | 2024-06-01 | 1 6204 | 2024-01-01 | 2024-01-01 | 1 6204 | 2024-03-01 | 2024-03-01 | 1 6204 | 2024-05-01 | 2024-05-01 | 1 7891 | 2024-02-01 | 2024-02-01 | 1 7891 | 2024-04-01 | 2024-05-01 | 2 8810 | 2024-01-01 | 2024-02-01 | 2 8810 | 2024-04-01 | 2024-04-01 | 1 (16 rows) ## # クイックリファレンス ** これらのパターンは、データベース固有の機能に依存せず、標準 SQL で動作します。また、リテンション分析（顧客維持率分析）、アップグレードファネル追跡、収益レポート作成などの分析ワークフローで頻繁に登場します。 ヒント 使用タイミング LAG() / LEAD() イベント間の時間間隔、またはエンティティごとの前後比較 自己結合 状態間の遷移（アップグレード、再アクティブ化）の検出 ROW_NUMBER() グループごとの上位 N 行、重複排除 NTILE(n) 支出や活動レベルによる顧客セグメンテーション ローリングウィンドウ (ROWS BETWEEN) ノイズの多い時系列データの平滑化、移動平均 FILTER 1 クエリパス内での複数の条件付き集計 連続するストリーク検出 サブスクリプションの継続期間、リテンション分析、セッション間のギャップ これらに慣れれば、多くの場合 Python で多段階のデータ変換として処理される作業を、単一の SQL クエリでより明確かつ効率的に表現できるようになります。

データサイエンティストが知っておくべき実用的な SQL の技

背景や根拠まで確認しますか？

調べる

選ぶ

サイト