mysql - 聚集函数上的RANK OVER PARTITION BY

这里有三张桌子：
订单(ORDER_ID, CUSTOMER_ID, ORDER_DATE)
订单详情(ORDER_ID, PRODUCT_ID, PRICE, UNITS)
客户(CUSTOMER_ID, SIGNUP_DATE, COUNTRY)
我需要确定每个国家前三个月的收入。
这是我想到的问题：

SELECT c.country
    , MONTH(o.order_date) as _Month
    , SUM(od.price * od.units) AS revenue
    , RANK() OVER (PARTITION BY country, _Month ORDER BY revenue) AS Rank
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
JOIN order_detail od
WHERE o.order_id = od.order_id GROUP BY country, Month
HAVING Rank <= 3

因为我不确定RANK（）OVER函数中是否可以使用SUM(od.price * od.units) as revenue这个查询，因为它是一个聚合函数，所以它可以工作吗？

最佳答案

在支持窗口功能的数据库中，这通常被写为：

SELECT cm.*
FROM (SELECT c.country, MONTH(o.order_date) as Month, SUM(od.price * od.units) AS revenue,
             RANK() OVER (PARTITION BY country, MONTH(o.order_date) ORDER BY SUM(od.price * od.units) ) AS Rank
      FROM orders o LEFT JOIN
           customers c
           ON o.customer_id = c.customer_id  JOIN
           order_detail od
           ON o.order_id = od.order_id
      GROUP BY country, Month
     ) cm
HAVING Rank <= 3;

关于mysql - 聚集函数上的RANK OVER PARTITION BY，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/47761545/