有没有办法在“分组依据"之后仅显示查询的前 2/n 个?国家?- 大查询 SQL [英] Is there a way to show only the top 2/nth of a query after "group by" country? - bigquery SQL
问题描述
我正在对 Google Big query 进行查询,我加入了 2 个表并创建了一个新列total gmv";使用SUM"为了代表总收入,现在我只想在我的查询中显示前 2 个供应商 GROUP BY 国家.
I am doing a query on Google Big query, I have joined the 2 tables and created a new column "total gmv" using "SUM" to represent the total revenue, now I wanted to show only the top 2 vendors , GROUP BY country in my query.
我设法按国家/地区和供应商名称显示 total_gmv 组,但我想过滤以仅显示每个国家/地区的前 2 个供应商.
I manage to show total_gmv group by COUNTRY and vendor_name, but I would like to filter to show top 2 vendors for each country only.
我使用的代码
SELECT Ord.country_name, vn.vendor_name, round(sum(Ord.gmv_local),2) as total_gmv FROM ORDERS as Ord
left join `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` as vn
ON Ord.vendor_id = vn.id
GROUP BY Ord.country_name, vn.vendor_name
ORDER BY Ord.country_name, total_gmv desc
country_name | vendor_name | total_gmv |
---|---|---|
新加坡 | 一所房子 | 1583.25 |
新加坡 | B 屋 | 1236.35 |
新加坡 | C 屋 | 1153.27 |
新加坡 | D 屋 | 596.21 |
香港 | H 屋 | 1888.75 |
香港 | K 屋 | 755.78 |
曼谷 | BB 屋 | 936.12 |
有没有办法只显示每个国家/地区的前 2 个供应商?
我的目标表应该是这样的,只显示前 2 个
我正在使用 Google bigquery,它似乎是TOP 2"功能不起作用?
I am using Google bigquery and it seems the "TOP 2" function doesn't work?
country_name | vendor_name | total_gmv |
---|---|---|
新加坡 | 一所房子 | 1583.25 |
新加坡 | B 屋 | 1236.35 |
香港 | H 屋 | 1888.75 |
香港 | K 屋 | 755.78 |
曼谷 | BB 屋 | 936.12 |
推荐答案
使用ROW_NUMBER
:
WITH cte AS (
SELECT Ord.country_name, vn.vendor_name, ROUND(SUM(Ord.gmv_local), 2) AS total_gmv,
ROW_NUMBER() OVER (PARTITION BY Ord.country_name
ORDER BY SUM(Ord.gmv_local) DESC) rn
FROM ORDERS AS Ord
LEFT JOIN `primeval-falcon-306603.foodpanda_BI_Exercise.Vendors` AS vn
ON Ord.vendor_id = vn.id
GROUP BY Ord.country_name, vn.vendor_name
)
SELECT country_name, vendor_name, total_gmv
FROM cte
WHERE rn <= 2
ORDER BY country_name, total_gmv DESC;
这篇关于有没有办法在“分组依据"之后仅显示查询的前 2/n 个?国家?- 大查询 SQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!