需求

假设我们有一个名为sales的表，我们想要按product分组，并为每个产品选择销售额最高的那一天

sales表

首先给每个产品按照销售量进行排名

SELECT product,
         sale_date,
         sales_amount,
         ROW_NUMBER() OVER (PARTITION BY product ORDER BY sales_amount DESC) AS row_num
  FROM sales

然后筛选出排名第一的数据

SELECT product, sale_date, sales_amount
FROM (
  SELECT product,
         sale_date,
         sales_amount,
         ROW_NUMBER() OVER (PARTITION BY product ORDER BY sales_amount DESC) AS row_num
  FROM sales
) AS subquery
WHERE row_num = 1;