top3.sql

OT Flash r56 · Online reference / 在线参考

WITH grouped AS (
  SELECT "区域", "类别", "产品", SUM("销售金额") AS "销售金额合计"
  FROM "电商销售数据"
  WHERE "订单状态" = '已完成' AND "币种" = 'CNY'
  GROUP BY "区域", "类别", "产品"
), ranked AS (
  SELECT "区域", "类别", "产品", "销售金额合计",
    ROW_NUMBER() OVER (
      PARTITION BY "区域", "类别"
      ORDER BY "销售金额合计" DESC, "产品" ASC
    ) AS "排名"
  FROM grouped
)
SELECT "区域", "类别", "产品", "销售金额合计", "排名"
FROM ranked WHERE "排名" <= 3
ORDER BY "区域", "类别", "排名"