MySQL 8のウィンドウ関数ROW_NUMBER／RANKでランキングを出す方法｜新人エンジニア向けに解説

SELECT
    product_name,
    category_name,
    sales_amount,
    ROW_NUMBER() OVER (
        ORDER BY sales_amount DESC
    ) AS row_number_rank
FROM sales;

ROW_NUMBER() OVER (
    ORDER BY sales_amount DESC
)

SELECT
    sale_id,
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (
        ORDER BY sales_amount DESC, sale_id ASC
    ) AS row_number_rank
FROM sales;

SELECT
    product_name,
    category_name,
    sales_amount,
    RANK() OVER (
        ORDER BY sales_amount DESC
    ) AS rank_no
FROM sales;

SELECT
    product_name,
    category_name,
    sales_amount,
    DENSE_RANK() OVER (
        ORDER BY sales_amount DESC
    ) AS dense_rank_no
FROM sales;

SELECT
    product_name,
    category_name,
    sales_amount,
    RANK() OVER (
        PARTITION BY category_name
        ORDER BY sales_amount DESC
    ) AS category_rank
FROM sales;

RANK() OVER (
    ORDER BY sales_amount DESC
)

RANK() OVER (
    PARTITION BY category_name
    ORDER BY sales_amount DESC
)

SELECT
    product_name,
    category_name,
    sales_amount,
    category_rank
FROM (
    SELECT
        product_name,
        category_name,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY category_name
            ORDER BY sales_amount DESC, sale_id ASC
        ) AS category_rank
    FROM sales
) AS ranked_sales
WHERE category_rank <= 3
ORDER BY category_name, category_rank;

WHERE category_rank <= 3

WITH ranked_sales AS (
    SELECT
        product_name,
        category_name,
        sales_amount,
        ROW_NUMBER() OVER (
            PARTITION BY category_name
            ORDER BY sales_amount DESC, sale_id ASC
        ) AS category_rank
    FROM sales
)
SELECT
    product_name,
    category_name,
    sales_amount,
    category_rank
FROM ranked_sales
WHERE category_rank <= 3
ORDER BY category_name, category_rank;

ROW_NUMBER() OVER (
    PARTITION BY category_name
    ORDER BY sales_amount DESC, sale_id ASC
)

WITH ranked_sales AS (
    SELECT
        product_name,
        category_name,
        sales_amount,
        RANK() OVER (
            PARTITION BY category_name
            ORDER BY sales_amount DESC
        ) AS category_rank
    FROM sales
)
SELECT
    product_name,
    category_name,
    sales_amount,
    category_rank
FROM ranked_sales
WHERE category_rank <= 3
ORDER BY category_name, category_rank, sales_amount DESC;

SELECT
    product_name,
    sales_amount,
    RANK() OVER (
        ORDER BY sales_amount DESC
    ) AS rank_no
FROM sales
ORDER BY rank_no, product_name;

CREATE TABLE sales_details (
    detail_id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    category_name VARCHAR(100) NOT NULL,
    sales_amount INT NOT NULL,
    sold_at DATE NOT NULL
);

WITH product_sales AS (
    SELECT
        product_name,
        category_name,
        SUM(sales_amount) AS total_sales
    FROM sales_details
    GROUP BY product_name, category_name
),
ranked_product_sales AS (
    SELECT
        product_name,
        category_name,
        total_sales,
        RANK() OVER (
            ORDER BY total_sales DESC
        ) AS sales_rank
    FROM product_sales
)
SELECT
    product_name,
    category_name,
    total_sales,
    sales_rank
FROM ranked_product_sales
ORDER BY sales_rank, product_name;

WITH monthly_product_sales AS (
    SELECT
        DATE_FORMAT(sold_at, '%Y-%m') AS sales_month,
        product_name,
        SUM(sales_amount) AS total_sales
    FROM sales_details
    GROUP BY
        DATE_FORMAT(sold_at, '%Y-%m'),
        product_name
),
ranked_monthly_sales AS (
    SELECT
        sales_month,
        product_name,
        total_sales,
        RANK() OVER (
            PARTITION BY sales_month
            ORDER BY total_sales DESC
        ) AS monthly_rank
    FROM monthly_product_sales
)
SELECT
    sales_month,
    product_name,
    total_sales,
    monthly_rank
FROM ranked_monthly_sales
ORDER BY sales_month, monthly_rank;

public class ProductRankingDto {

    private String productName;
    private String categoryName;
    private int salesAmount;
    private int ranking;

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public int getSalesAmount() {
        return salesAmount;
    }

    public void setSalesAmount(int salesAmount) {
        this.salesAmount = salesAmount;
    }

    public int getRanking() {
        return ranking;
    }

    public void setRanking(int ranking) {
        this.ranking = ranking;
    }
}

public List<ProductRankingDto> findProductRanking() {

    String sql =
            "SELECT "
          + "    product_name, "
          + "    category_name, "
          + "    sales_amount, "
          + "    RANK() OVER ( "
          + "        ORDER BY sales_amount DESC "
          + "    ) AS ranking "
          + "FROM sales "
          + "ORDER BY ranking, product_name";

    List<ProductRankingDto> rankingList = new ArrayList<>();

    try (Connection con = getConnection();
         PreparedStatement ps = con.prepareStatement(sql);
         ResultSet rs = ps.executeQuery()) {

        while (rs.next()) {
            ProductRankingDto dto = new ProductRankingDto();

            dto.setProductName(rs.getString("product_name"));
            dto.setCategoryName(rs.getString("category_name"));
            dto.setSalesAmount(rs.getInt("sales_amount"));
            dto.setRanking(rs.getInt("ranking"));

            rankingList.add(dto);
        }

        return rankingList;

    } catch (SQLException e) {
        throw new RuntimeException("商品ランキングの取得に失敗しました。", e);
    }
}

RANK() OVER (
    ORDER BY sales_amount DESC
) AS ranking

SELECT VERSION();

SELECT
    product_name,
    RANK() AS ranking
FROM sales;

良い例です。

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

SELECT
    product_name,
    ROW_NUMBER() OVER () AS ranking
FROM sales;

SELECT
    product_name,
    ROW_NUMBER() OVER (
        ORDER BY sales_amount DESC
    ) AS ranking
FROM sales;

SELECT
    product_name,
    sales_amount,
    ROW_NUMBER() OVER (
        ORDER BY sales_amount DESC
    ) AS ranking
FROM sales
WHERE ranking <= 3;

SELECT
    product_name,
    sales_amount,
    ranking
FROM (
    SELECT
        product_name,
        sales_amount,
        ROW_NUMBER() OVER (
            ORDER BY sales_amount DESC
        ) AS ranking
    FROM sales
) AS ranked_sales
WHERE ranking <= 3;

売上が同じ商品は同順位にするのか
同順位にした場合、次の順位を飛ばすのか
画面には必ず10件だけ表示するのか
10位が複数ある場合は全件表示するのか

CREATE INDEX idx_sales_category_amount
ON sales (category_name, sales_amount);

EXPLAIN
SELECT
    product_name,
    category_name,
    sales_amount,
    RANK() OVER (
        PARTITION BY category_name
        ORDER BY sales_amount DESC
    ) AS category_rank
FROM sales;

SELECT
    列名,
    RANK() OVER (
        ORDER BY ランキング基準列 DESC
    ) AS ranking
FROM テーブル名;

SELECT
    列名,
    グループ列,
    RANK() OVER (
        PARTITION BY グループ列
        ORDER BY ランキング基準列 DESC
    ) AS ranking
FROM テーブル名;

WITH ranked_data AS (
    SELECT
        列名,
        ROW_NUMBER() OVER (
            PARTITION BY グループ列
            ORDER BY ランキング基準列 DESC
        ) AS ranking
    FROM テーブル名
)
SELECT
    *
FROM ranked_data
WHERE ranking <= N;