google-bigquery - 如何在 BQ 中使用 union 找到最高销售额

Question

我有两个表中的数据，使用联合我如何获得每年最高的销售额？

with table1 as(
select "ProductA" as Product, 80000 as units_sold,"2016" as year union all
select "ProductB" as Product, 75000 as units_sold,"2016" as year union all
select "ProductC" as Product, 15000 as units_sold,"2016" as year
),
table2 as(
select "ProductA" as Product, 60000 as units_sold,"2017" as year union all
select "ProductB" as Product, 120000 as units_sold,"2017" as year union all
select "ProductC" as Product, 70000 as units_sold,"2017" as year
)
select * from table1 union all select * from table2

如何使用联合得到如下答案？

Product units_sold  year     
ProductA    80000   2016     
ProductB    120000  2017

标签： google-bigquery

Answer 1

#standardSQL
WITH table1 AS(
  SELECT "ProductA" AS Product, 80000 AS units_sold,"2016" AS year UNION ALL
  SELECT "ProductB" AS Product, 75000 AS units_sold,"2016" AS year UNION ALL
  SELECT "ProductC" AS Product, 15000 AS units_sold,"2016" AS year
), table2 AS(
  SELECT "ProductA" AS Product, 60000 AS units_sold,"2017" AS year UNION ALL
  SELECT "ProductB" AS Product, 120000 AS units_sold,"2017" AS year UNION ALL
  SELECT "ProductC" AS Product, 70000 AS units_sold,"2017" AS year
)
SELECT ARRAY_AGG(STRUCT(Product, units_sold) ORDER BY units_sold DESC)[OFFSET(0)].*, year
FROM (
  SELECT Product, year, SUM(units_sold) units_sold 
  FROM (
    SELECT * FROM table1 UNION ALL 
    SELECT * FROM table2
  )
  GROUP BY Product, year
)
GROUP BY year
ORDER BY year