SQL Academy · レッスン

上位・下位N件を取得する

Q: 「上位・下位N件を取得する」で何を学びますか？

最大値と最小値を取得します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

最大値と最小値を取得します。

レッスン 4/413 ステップ

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

Top-Nクエリとは

Top-Nクエリは、「最も高価な商品3件」「最新の注文10件」「最も低いスコア5件」といった問いに答えます。

仕組みは並べ替えと上限指定の組み合わせにすぎませんが、非常によく使われるため名前が付いています。

-- Top 3 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

最下位N件のレコード

最小値を取得するには、並び順の方向を反転します。ASCで並べ替えて先頭のN件を取得すると、最も安い行、最も古い行、または最も低い行を得られます。

-- 5 cheapest products
SELECT name, price
FROM products
ORDER BY price ASC
LIMIT 5;

同順位を解決するキーの追加

境界値が同じ行が複数ある場合、データベースはその中から任意に選択します。一意な副キーを追加すると、結果が安定し、再現可能になります。

SELECT id, name, price
FROM products
ORDER BY price DESC, id
LIMIT 3;

フィルタリング後のTop-N

Top-Nはフィルタリング後の集合に対して適用されます。まずWHERE句で候補を絞り込み、その後で並べ替えて上限を設定します。

これにより、「Books」カテゴリで最も高価な商品3件を取得できます。

SELECT name, price
FROM products
WHERE category = 'Books'
ORDER BY price DESC
LIMIT 3;

集計後のTop-N

計算した値にも順位を付けられます。グループ化して集計し、その集計値で並べ替えてから上限を設定します。

これにより、全体で最も多く支出した顧客5人を返します。

SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
ORDER BY spent DESC
LIMIT 5;

同順位におけるRANKとROW_NUMBERの違い

ROW_NUMBER()は常に異なる番号を付けるため、同順位があっても正確にN行を返します。RANK()は同順位の行に同じ番号を付けるため、3位が同順位の場合、「上位3件」で3件を超える行が返されることがあります。

SELECT name, price,
       RANK() OVER (ORDER BY price DESC) AS rnk
FROM products;

グループごとのTop-N

各カテゴリの最上位の商品を取得するには、カテゴリごとにランキングを分割します。PARTITION BYを使うと、グループごとに番号付けが最初から始まります。

SELECT category, name, price
FROM (
  SELECT category, name, price,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY price DESC
         ) AS rn
  FROM products
) t
WHERE rn = 1;

グループごとに1行を取得するDISTINCT ON

PostgreSQLのDISTINCT ONは、グループごとに最上位の行を選ぶ簡潔な方法です。グループ化する列を指定し、その後で各グループ内の目的の行が先頭になるように並べ替えます。

SELECT DISTINCT ON (category)
       category, name, price
FROM products
ORDER BY category, price DESC;

WITH TIESで同順位を含める

上位3件に加えて、3位と同順位の行もすべて含めたい場合は、標準のFETCH ... WITH TIESを使います。3行に加えて、最後の行と同順位の行も返します。

SELECT name, price
FROM products
ORDER BY price DESC
FETCH FIRST 3 ROWS WITH TIES;

単一の最上位値

最高値または最低値の行を1行だけ取得する場合、ORDER BY ... LIMIT 1が最も簡単なパターンです。MAX()を使ったサブクエリよりも明確で、多くの場合は高速です。

-- The single most recent order
SELECT id, total, created_at
FROM orders
ORDER BY created_at DESC
LIMIT 1;

確認問題

上位3つの価格を取得したいのですが、3位が同順位の場合はすべて含めたいとします。

対話的なレッスンで答える

まとめ

これで、必要なあらゆる方法でTop-Nレコードを取得できるようになりました。

単純な上位・下位リストにはORDER BY ... LIMIT nを使います
安定した結果を得るには、一意な同順位解決キーを追加します
グループごとのTop-NにはROW_NUMBER()またはDISTINCT ONを使います
同順位の行を残すにはFETCH ... WITH TIESを使います

これで並べ替え、上限指定、Top-Nの学習は完了です。これからは、どのような結果セットにも実際の順序を付けられます。

SELECT name, price
FROM products
ORDER BY price DESC, id
LIMIT 3;

無料で開始

AI チューターと学ぶ SQL — 無料

ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。

コース: 46
レッスン: 183

無料で学習を始めるコース全体を表示

よくある質問