Bigquery在数据集中列出每个国家的最大供应商

Question

我需要加入两个表，并按国家每年提取最大的供应商。

原始订单表如下所示：

country_name    date_local  vendor_id   gmv_local   
Taiwan       2012-10-02     2870          559.6     
Taiwan       2012-10-02     3812          573.5     
Singapore    2012-10-02     941           778.6     
Singapore    2014-10-02     13            120.6     
Thailand     2014-10-02     227           563.6     

此表与供应商表合并。

id  vendor_name             country_name
2870    A House             Taiwan
941     B House             Singapore
227     C House             Thailand

我想将"date_local“列中的年份提取为时间戳格式，其中年份列中的项目将从"2012-10-02”的原始日期格式中提取为"2012-01-01T00:00:00“。

然后，我想列出每个国家的gmv总量按年排名前2位的供应商。

生成的表应该如下所示：

year                    country_name    vendor_name     total_gmv
2012-01-01T00:00:00     Singapore        A House        1119.76
2012-01-01T00:00:00     Singapore        B House         819.63
2012-01-01T00:00:00     Taiwan           C House         119.6
2012-01-01T00:00:00     Taiwan           D House         119.6
2012-01-01T00:00:00     Bangkok          9 House         119.6
2014-01-01T00:00:00     Singapore        A House         2119.76
2014-01-01T00:00:00     Singapore        B House         1819.63
2014-01-01T00:00:00     Taiwan           C House         1019.6
2014-01-01T00:00:00     Taiwan           D House         919.6
2014-01-01T00:00:00     Bangkok          9 House         189.6

除其他外，这里还提供了一些以前的指导，我提出了以下查询：

SELECT
    Ord.country_name,
    vn.vendor_name,
    EXTRACT(year FROM date_local) AS year,
    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,
    EXTRACT(year FROM date_local)
QUALIFY ROW_NUMBER() OVER (PARTITION BY country_name, EXTRACT(year FROM date_local)
                           ORDER BY total_gmv DESC) <= 2
ORDER BY
    Ord.country_name DESC,
    total_gmv DESC;

但请继续获取以下错误消息:选择列表表达式引用列date_local，它既不分组也不聚合在4:23

所以不幸的是，我甚至看不出我是否得到了我想要的结果。

如有任何建议，将不胜感激。

Answer 1

with ORDERS (country_name,date_local,vendor_id,gmv_local) as(
select 'Taiwan', Date('2012-10-02'),     2870 ,         559.6     union all
select 'Taiwan',       Date('2012-10-02'),     3812,          573.5     union all
select 'Taiwan',       Date('2012-10-02'),     3813,          555.5     union all
select 'Singapore',    Date('2012-10-02'),     941,           778.6     union all
select 'Singapore',    Date('2014-10-02'),     13,            120.6     union all
select 'Thailand',     Date('2014-10-02'),     227,           563.6   
)
,vendor(id,  vendor_name, country_name) as(
select 2870,    'A House','Taiwan' union all
select 3812,    'D House','Taiwan' union all
select 3813,    'F House','Taiwan' union all
select 941,     'B House','Singapore' union all
select 13,   'E House','Singapore' union all
select 227,     'C House','Thailand'
)
select country_name,vendor_name,year,total_gmv from(
SELECT
    Ord.country_name,
    vn.vendor_name,
    EXTRACT(year FROM date_local) AS year,
    ROUND(SUM(Ord.gmv_local), 2) AS total_gmv,
     ROW_NUMBER() OVER (PARTITION BY country_name,EXTRACT(year FROM date_local)) rn
FROM ORDERS AS Ord
LEFT JOIN vendor AS vn
    ON Ord.vendor_id = vn.id
GROUP BY
    Ord.country_name,
    vn.vendor_name,
    EXTRACT(year FROM date_local)
ORDER BY
    Ord.country_name DESC,
    total_gmv DESC
)a where a.rn <= 2