top3-query.json

OT Flash r56 · Online reference / 在线参考

{
  "name": "dfc_table",
  "arguments": {
    "action": "query",
    "ontologyId": "<ONTOLOGY_ID>",
    "sql": "WITH grouped AS (\n  SELECT \"区域\", \"类别\", \"产品\", SUM(\"销售金额\") AS \"销售金额合计\"\n  FROM \"电商销售数据\"\n  WHERE \"订单状态\" = '已完成' AND \"币种\" = 'CNY'\n  GROUP BY \"区域\", \"类别\", \"产品\"\n), ranked AS (\n  SELECT \"区域\", \"类别\", \"产品\", \"销售金额合计\",\n    ROW_NUMBER() OVER (\n      PARTITION BY \"区域\", \"类别\"\n      ORDER BY \"销售金额合计\" DESC, \"产品\" ASC\n    ) AS \"排名\"\n  FROM grouped\n)\nSELECT \"区域\", \"类别\", \"产品\", \"销售金额合计\", \"排名\"\nFROM ranked WHERE \"排名\" <= 3\nORDER BY \"区域\", \"类别\", \"排名\"",
    "maxRows": 100
  }
}