SQL Interview Prep · レッスン

NTILEで区分に分ける

Q: 「NTILEで区分に分ける」で何を学びますか？

行を四分位やパーセンタイル区分に分割します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

行を四分位やパーセンタイル区分に分割します。

「NTILEで区分に分ける」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

同じサイズのバケットが必要な場合

面接官は、「顧客を支出額で4つの均等なグループに分けてください」や「各行がどのデシルに入るかを示してください」と質問します。そのための関数がNTILEです。

NTILE(n)は、順序付けされた行をできるだけ均等にn個のバケットへ分け、各行に1からnまでのバケット番号を付けます。このレッスンでは、行の分け方、件数が均等でない場合の扱い、ランキングとの違いを学びます。

NTILEの基本構文

値で順序付けした行に対してNTILE(4)を使うと、四分位を作成できます。すべてのウィンドウ関数と同様にOVER句が必要で、その中のORDER BYによって、どの行が低いバケットに入り、どの行が高いバケットに入るかが決まります。

昇順にすると最小の値がバケット1に入り、降順にするとその関係が逆になります。

SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend) AS spend_quartile
FROM customers;

NTILEによる行の分配方法

12行に対してNTILE(4)を使うと、各バケットには12 / 4 = 3行ずつ入ります。バケット1には最も低い3つの値、バケット4には最も高い3つの値が入ります。

重要なのは、NTILEが行数で分割し、値の範囲では分割しないという点です。同じ行数が入っている限り、2つのバケットがまったく異なる値の範囲をカバーすることもあります。

均等に分割できない場合

行数を均等に分けられない場合はどうなるでしょうか。10行に対してNTILE(4)を使うと、10 / 4 = 2、余りは2です。NTILEは先にあるバケットに余分な行を割り当てます。

バケット1：3行
バケット2：3行
バケット3：2行
バケット4：2行

つまり、バケットのサイズの差は最大でも1行で、大きいバケットが先に並びます。この正確なルールは、面接でよく問われるポイントです。

NTILEは値の同順位を考慮しない

重要な落とし穴があります。NTILEは同じ値を同じバケットにまとめません。位置に基づいて行を埋めるため、total_spendが同じ2行でも、行の順序だけを理由に異なるバケットに入ることがあります。

業務上、同じ値を同じティアに入れる必要がある場合、NTILEは適切なツールではありません。代わりに値に基づく方法が必要です。面接官は意図的にこの落とし穴を仕掛けることがあります。

デシルとパーセンタイル

バケット数は、引数として渡す数にすぎません。NTILE(10)はデシルを、NTILE(100)はパーセンタイル帯を作成します。これにより、アナリストはユーザーをパフォーマンスのティアやリスク帯に分けられます。

出力は帯の番号です。そのため、NTILE(10)のバケット9に入る値は、上から2番目に高いデシルに属します。

SELECT
  user_id,
  score,
  NTILE(10) OVER (ORDER BY score DESC) AS decile
FROM leaderboard;

パーティションごとのバケット

PARTITION BYを追加すると、各グループ内で独立してバケット分けできます。たとえば、地域ごとの支出額の四分位を求める場合です。各地域でバケット1から再開します。

これにより、「各地域内で支出額が上位四分位に入る顧客」のような質問に答えられます。全体の支出額が少ない地域でも、その地域独自のバケット4を持ちます。

SELECT
  region,
  customer_id,
  total_spend,
  NTILE(4) OVER (
    PARTITION BY region
    ORDER BY total_spend DESC
  ) AS regional_quartile
FROM customers;

特定のティアに絞り込む

NTILE(...)をWHERE句に直接記述することはできません。ウィンドウ関数はWHEREの後に計算されるためです。クエリをCTEまたはサブクエリで包み、その後でバケット列を使って絞り込みます。

「支出額が上位四分位の顧客を取得する」というパターンは、実際の解答でNTILEが使われる最も一般的な例です。

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
  FROM customers
)
SELECT customer_id, total_spend
FROM q
WHERE quartile = 1;

NTILEと値に基づくパーセンタイル

NTILEは行数が均等になるようにバケット分けします。一方、真の統計的パーセンタイル、つまり90パーセンタイルにおける値が必要な場合は、PERCENTILE_CONTまたはPERCENTILE_DISCを使います。

NTILE(100)：行がどのパーセンタイル帯に入るかを、順位上の位置で示します。
PERCENTILE_CONT(0.9)：90パーセンタイルにおける実際の値を返します。

この違いを理解しているかどうかで、自信のある解答と当てずっぽうの解答が分かれます。

ORDER BYが重要な場合

NTILEではOVER内のORDER BYが必須です。順序が定義されていなければ、バケットに意味はありません。順序の向きによって、どちら側がバケット1になるかが決まります。

同順位の値によって割り当てがあいまいになり、境界上の行がどのバケットに入るかが重要な場合は、順序にタイブレーク用の列を追加して、決定的で再現可能な結果にしてください。

バケットに名前を付ける

生のバケット番号（1、2、3、4）が最終成果物になることはほとんどありません。アナリストは通常、NTILEの結果に対するCASE式を使って、Low、Medium、High、Topのような業務上のラベルに変換します。

CTEでNTILEを計算し、外側のクエリで番号を変換します。これによりウィンドウのロジックが整理され、出力をそのまま提示できる形に整えられます。

WITH q AS (
  SELECT customer_id, total_spend,
         NTILE(4) OVER (ORDER BY total_spend) AS bucket
  FROM customers
)
SELECT customer_id, total_spend,
  CASE bucket
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    WHEN 4 THEN 'Top'
  END AS spend_tier
FROM q;

確認問題

行数が均等でない場合の分配ルールを確認しましょう。

まとめ

NTILEは、順序付けされた行を同じ行数のバケットに分配します。

NTILE(n)は行に1からnのラベルを付けます。NTILE(4)は四分位、NTILE(10)はデシルです。
値の範囲ではなく行数で分割し、余分な行は先にあるバケットに割り当てます。
同順位の値を同じバケットにまとめず、WHEREに直接記述することもできません。
実際のパーセンタイル値が必要な場合は、PERCENTILE_CONTを使います。

次は、FIRST_VALUE、LAST_VALUE、そしてフレームの境界を使って境界値を取得します。

よくある質問

「NTILEで区分に分ける」レッスンは無料ですか？

はい。「NTILEで区分に分ける」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「NTILEで区分に分ける」で何を学びますか？

行を四分位やパーセンタイル区分に分割します。ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「NTILEで区分に分ける」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Interview Prepに戻る