Query Go
グループごとの上位 N 件 — ROW_NUMBER / RANK / DENSE_RANK
Query Go
ガイド

グループごとの上位 N 件 — ROW_NUMBER / RANK / DENSE_RANK

カテゴリごとの売上上位3件など、各グループで上位N件を取りたいとき。

グループごとの上位 N 件 — ROW_NUMBER / RANK / DENSE_RANK diagram

問題 — 上位 N 件 per group

「カテゴリごとに売上トップ3の商品」「店舗ごとに直近5件の注文」といったグループ内でN件抽出は、単純な ORDER BY ... LIMIT N ではできません。LIMIT はクエリ全体の先頭N件しか取れないからです。

この用途ではウィンドウ関数の番号系 3兄弟 — ROW_NUMBER() / RANK() / DENSE_RANK() — を使い分けます。同点（TIE）をどう扱うかで選びます。

3関数の違い

  • ROW_NUMBER(): 同点でも連番 1,2,3,4。N件ちょうど欲しいときに。
  • RANK(): 同点は同順位、次は飛ぶ 1,1,3,4。順位付けの「常識」どおり。
  • DENSE_RANK(): 同点は同順位、次は飛ばない 1,1,2,3。「◯位までを全部」取りたいときに。

たとえば売上1位が同点で2人いる場合:

  • 「トップ3件ちょうど」なら ROW_NUMBER
  • 「3位までを全員」なら RANK ≤ 3 または DENSE_RANK ≤ 3

書き方

いずれもサブクエリで番号を振り、外側で WHERE rn <= N で絞ります。

sql
-- カテゴリごとに売上トップ3商品 (同点は飛ばす = ちょうど3件)
SELECT category_id, product_id, sales
FROM (
  SELECT
    p.*,
    ROW_NUMBER() OVER (
      PARTITION BY category_id
      ORDER BY sales DESC
    ) AS rn
  FROM products p
) t
WHERE rn <= 3;

-- 「3位までを全員」にしたい場合
-- SELECT ... WHERE dr <= 3
-- ただし rn の部分を DENSE_RANK() に変える

RDBMS 対応

ウィンドウ関数は標準SQLで、現行の主要 RDBMS すべてで使えます:

  • PostgreSQL 8.4+
  • SQL Server 2005+
  • MySQL 8.0+ (それ以前は非対応 → 自己結合 + COUNT などで代替する必要があった)
  • SQLite 3.25+
  • Oracle 8.1.6+

古い MySQL (5.7 以前) を相手にする場合は、LEFT JOIN + COUNT(*) の疑似ランキングなど代替実装になります。

性能と注意

ウィンドウ関数はグループ内を全部番号付けしてから絞るため、グループ内の行数が多いと無駄が出ます。各グループが巨大なら LATERAL + LIMIT N の方が速い場合が多い（最新1件 per group と同じ構図）。

ORDER BY の列が NULL を含む場合、NULL の並び位置が RDBMS で違うので要注意（PostgreSQL は DESC で NULLS FIRST、MySQL は NULLS LAST）。明示的に NULLS LAST を書くのが安全。

関連トピック

ROW_NUMBER / RANK / DENSE_RANK / NTILEGROUP BYORDER BY