mysql 实现分组排名

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    sale_amount DECIMAL(10, 2),
    sale_date DATE
);

SELECT 
    product_id,
    sale_amount,
    RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) AS rank
FROM 
    sales;

SET @rank := 0;
SET @prev_product_id := NULL;

SELECT 
    product_id,
    sale_amount,
    rank
FROM (
    SELECT 
        product_id,
        sale_amount,
        @rank := IF(@prev_product_id = product_id, @rank + 1, 1) AS rank,
        @prev_product_id := product_id
    FROM 
        sales
    ORDER BY 
        product_id, sale_amount DESC
) AS ranked_sales;

SELECT 
    product_id,
    sale_amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY sale_amount DESC) AS rank
FROM 
    sales;