如何在BigQuery中使用Union查询最高销售额及年度最高销售额

嘿，我来帮你搞定这两个BigQuery的Union查询问题！先把你没写完的示例数据表补全，方便后续演示：

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, 90000 AS units_sold, "2017" AS year
)

1. 如何在BigQuery中使用Union查询最高销售额？ #

思路很简单：先通过UNION ALL把两个表的所有数据合并，然后找出合并后数据里销售额最高的那条记录。这里用窗口函数RANK()更灵活——如果有多个产品销售额并列第一，它能全部展示出来，不会漏掉。

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, 90000 AS units_sold, "2017" AS year
),
combined_data AS (
  SELECT * FROM table1 UNION ALL SELECT * FROM table2
),
ranked_data AS (
  SELECT 
    *,
    RANK() OVER (ORDER BY units_sold DESC) AS sales_rank
  FROM combined_data
)
SELECT Product, units_sold, year
FROM ranked_data
WHERE sales_rank = 1;

这个查询会返回所有年份里销售额最高的产品（这里是2017年的ProductB，120000），如果有多个产品销售额相同且都是最高，都会被列出来。

2. 如何使用Union查询出每一年的最高销售额？ #

这个需求是按年份分组，找出每年的Top销售额产品。同样先合并两个表，然后用窗口函数按year分区排序，取每个分区的第一条（或并列的所有）记录。

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, 90000 AS units_sold, "2017" AS year
),
combined_data AS (
  SELECT * FROM table1 UNION ALL SELECT * FROM table2
),
ranked_data AS (
  SELECT 
    *,
    RANK() OVER (PARTITION BY year ORDER BY units_sold DESC) AS yearly_sales_rank
  FROM combined_data
)
SELECT Product, units_sold, year
FROM ranked_data
WHERE yearly_sales_rank = 1;

这个查询会返回2016年的ProductA（80000）和2017年的ProductB（120000），如果某一年有多个产品销售额并列最高，也会全部展示。

如果你只需要每年的最高销售额数值，不需要产品信息，也可以用更简洁的分组聚合：

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, 90000 AS units_sold, "2017" AS year
),
combined_data AS (
  SELECT * FROM table1 UNION ALL SELECT * FROM table2
)
SELECT year, MAX(units_sold) AS max_units_sold
FROM combined_data
GROUP BY year
ORDER BY year;

内容的提问来源于stack exchange，提问作者user475043