SQL ランキングを出す (RANK,OVER,PARTITION BY,DENSE_RANK)

SQL 目次 - ray88’s diary
【RANK関数】
RANK()関数は、指定した順序でデータをランク付けするための関数。
たとえば、売上の高い順に商品をランク付けしたい場合、RANK()関数を使用します。
【RANK関数のOVER句の役割】
ランキングの対象となるデータの範囲や順序を指定する役割があります。
【OVER句でのPARTITION BYの使用】
OVER句の中でPARTITION BYを使用することで、データを特定のカテゴリやグループに
分けて、各グループ内でランキングを行うことができます。

SELECT
  product_name,
  sales,
  RANK() OVER (ORDER BY sales DESC) AS ranking
FROM
  products;

SELECT
  city,
  product_name,
  sales,
  RANK() OVER (PARTITION BY city ORDER BY sales DESC) AS ranking_within_city
FROM
  products;

【DENSE_RANK()関数】
同じ価格の商品がある場合、それらの商品に同じランクを付け、次のランクはスキップせずに連続して割り当てます。
この修正により、1位、2位、3位といったように連続したランキングを得ることができます。

SELECT name,
       price,
       DENSE_RANK() OVER (ORDER BY price DESC) AS rank_1
FROM Products
ORDER BY rank_1;

【サンプルデータ作成SQL】

-- テーブルの作成
CREATE TABLE products (
  product_id SERIAL PRIMARY KEY, 
  city VARCHAR(32) NOT NULL,
  product_name VARCHAR(32) NOT NULL,
  sales INTEGER NOT NULL
);
-- サンプルデータの挿入
INSERT INTO products (city, product_name, sales) VALUES
('東京', '商品A', 50000),
('東京', '商品B', 75000),
('大阪', '商品C', 65000),
('大阪', '商品D', 60000),
('名古屋', '商品E', 55000),
('名古屋', '商品F', 70000),
('福岡', '商品G', 58000),
('福岡', '商品H', 62000),
('仙台', '商品I', 57000),
('仙台', '商品J', 53000);