Mysql查询获取每月n最高消费客户

SELECT customer_id, customer_name, MONTH(purchase_date) AS month, SUM(amount) AS total_amount
FROM customers
GROUP BY customer_id, MONTH(purchase_date)

SET @row_number = 0;
SET @current_month = '';

SELECT customer_id, customer_name, month, total_amount
FROM (
    SELECT customer_id, customer_name, month, total_amount,
        @row_number := IF(@current_month = month, @row_number + 1, 1) AS row_number,
        @current_month := month
    FROM (
        SELECT customer_id, customer_name, month, total_amount
        FROM (
            SELECT customer_id, customer_name, month, total_amount,
                ROW_NUMBER() OVER (PARTITION BY month ORDER BY total_amount DESC) AS rank
            FROM (
                SELECT customer_id, customer_name, month, SUM(amount) AS total_amount
                FROM customers
                GROUP BY customer_id, MONTH(purchase_date)
            ) AS monthly_total
        ) AS ranked_total
        WHERE rank <= n
    ) AS ranked_customers
) AS top_customers
ORDER BY month, row_number