SQLでデータをランク付けする方法：ウィンドウ関数の紹介

Key Takeaways

1. SQLは、データを順序付け、分析するためのいくつかのランキング関数を提供します。
2. どのように同順位を処理したいかに基づいて、適切な関数を選択します。
3. 特定のグループ内でランク付けするには、PARTITION BYを使用します。

Introduction

SQLでのランキングは、データの順序付け、同順位の処理、および位置に基づく結果のフィルタリングに不可欠です。SQLは、これらの目標を達成するために、いくつかのウィンドウ関数—ROW_NUMBER()、RANK()、DENSE_RANK()、およびNTILE()—を提供します。それぞれがわずかに異なるユースケースに対応します。

---

1. ROW_NUMBER()

• 各行に一意の連番を割り当てます。同順位は考慮しません。

• 構文:

  ROW_NUMBER() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS row_num

• 例: スコアの降順で一意の行番号を割り当てる:

  SELECT 
    student_name,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
  FROM students;

---

2. RANK()

• 同順位の値に同じランクを割り当てますが、後続のランクにギャップを残します。

• 構文:

  RANK() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS rank

• 例: 学生をランク付けし、ギャップを許可する:

  SELECT 
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
  FROM students;

  2人の学生がトップスコアを共有する場合、両方ともランク1になり、次はランク3になります。

---

3. DENSE_RANK()

• RANK()に似ていますが、同順位の後にギャップを残しません。

• 構文:

  DENSE_RANK() OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS dense_rank

• 例:

  SELECT 
    student_name,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
  FROM students;

  2人の学生が1位をタイで獲得した場合、両方ともランク1になり、次はランク2になります。

---

4. NTILE(N)

• 行をN個のバケットに分割し、1からNまでのバケット番号を割り当てます。

• 構文:

  NTILE(N) OVER (
    [PARTITION BY partition_expr]
    ORDER BY order_expr [ASC|DESC]
  ) AS tile

• 例:

  SELECT 
    student_name,
    score,
    NTILE(4) OVER (ORDER BY score DESC) AS quartile
  FROM students;

  学生をスコアごとに4つのグループに分割します。

---

5. Partitioning

• PARTITION BYを使用すると、グループ内でランク付けできます (例: 部門ごと、または科目ごと)。

• 例: 各クラス内の学生をランク付けする:

  SELECT
    class,
    student_name,
    score,
    RANK() OVER (
      PARTITION BY class
      ORDER BY score DESC
    ) AS class_rank
  FROM students;

---

6. Why Choose One?

---

7. Filtering Top‑N Results

たとえば、RANK()を使用して上位3人の学生を取得するには:

WITH ranked AS (
  SELECT
    student_name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
  FROM students
)
SELECT student_name, score
FROM ranked
WHERE rank <= 3;

このクエリには同順位が含まれます。正確に3行が必要ですか？代わりにROW_NUMBER()を使用してください。

---

8. Real-World Examples

• 製品ごとの店舗別売上:

  SELECT
    product,
    store_id,
    sales,
    RANK() OVER (
      PARTITION BY product
      ORDER BY sales DESC
    ) AS sales_rank
  FROM sales_data;

  製品ごとの売上高上位店舗の特定に役立ちます。

• 部署別給与分析:

  WITH dept_ranked AS (
    SELECT
      department_id,
      employee_name,
      salary,
      RANK() OVER (
        PARTITION BY department_id
        ORDER BY salary DESC
      ) AS dept_rank
    FROM employees
  )
  SELECT *
  FROM dept_ranked
  WHERE dept_rank = 1;

  部署ごとの最高給与の従業員を検索します。

---

9. Summary

1. 同順位の処理とギャップの好みに基づいてウィンドウ関数を選択します。
2. グループ化にはPARTITION BYを使用します。
3. ランキングロジックを定義するにはORDER BYを使用します。
4. ターゲットを絞った結果を得るには、WHEREまたはCTEを使用して結果をフィルタリングします。

FAQs

SQLのランキング関数の主な目的は何ですか？

結果セット内のデータを順序付け、分析するために使用されます。

RANK()とDENSE_RANK()の違いは何ですか？

RANK()は同順位の後にギャップを残しますが、DENSE_RANK()は残しません。

SQLでグループ内のデータをランク付けするにはどうすればよいですか？

ウィンドウ関数でPARTITION BY句を使用します。

Leapcellは、バックエンドプロジェクトをホストするための最適な選択肢です。

Leapcellは、Webホスティング、非同期タスク、およびRedis向けの次世代サーバーレスプラットフォームです。

多言語サポート

• Node.js、Python、Go、またはRustで開発します。

無制限のプロジェクトを無料でデプロイ

• 使用量に対してのみ料金が発生します — リクエストも課金もありません。

比類のない費用対効果

• アイドル料金なしの従量課金制。
• 例：$25で、平均応答時間60msで694万リクエストをサポートします。

合理化された開発者エクスペリエンス

• 簡単なセットアップのための直感的なUI。
• 完全に自動化されたCI / CDパイプラインとGitOps統合。
• 実用的な洞察のためのリアルタイムメトリックとロギング。

簡単なスケーラビリティと高性能

• 高い同時実行を簡単に処理するための自動スケーリング。
• 運用のオーバーヘッドはゼロ — ただ構築に集中してください。

ドキュメントで詳細をご覧ください。

Xでフォローしてください：@LeapcellHQ