如何在BigQuery中查询三年各年最高酒类销售额及关联字段

我正在使用BigQuery公开数据集bigquery-public-data.iowa_liquor_sales.sales，涉及字段包括：date(DATE)、sale_dollars(FLOAT)、item_description(STRING)、store_name(STRING)。

需要编写SQL查询，返回2019、2020、2021这三年中每年的最高销售额记录，同时包含对应的date、item_description和store_name字段。

目前能实现单年查询，但复制修改日期的方式过于繁琐：

SELECT date, sale_dollars, item_description, store_name
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE date between '2021-01-01' and '2021-12-31'
ORDER BY sale_dollars DESC
LIMIT 1

该查询返回2021年的结果如下：

尝试同时查询三年数据时，只能得到各年最高销售额数值，缺失关联字段：

SELECT
  (SELECT MAX(sale_dollars) FROM `bigquery-public-data.iowa_liquor_sales.sales` WHERE date between '2021-01-01' and '2021-12-31') as sale_2021,
  (SELECT MAX(sale_dollars) FROM `bigquery-public-data.iowa_liquor_sales.sales` WHERE date between '2020-01-01' and '2020-12-31') as sale_2020,
  (SELECT MAX(sale_dollars) FROM `bigquery-public-data.iowa_liquor_sales.sales` WHERE date between '2019-01-01' and '2019-12-31') as sale_2019

解决方案：使用窗口函数ROW_NUMBER() #

通过EXTRACT(YEAR FROM date)提取年份，再用ROW_NUMBER()按年份分组并按销售额降序排序，取每组的第一条记录（即当年最高销售额）：

WITH yearly_sales_ranked AS (
  SELECT
    date,
    sale_dollars,
    item_description,
    store_name,
    EXTRACT(YEAR FROM date) AS sale_year,
    ROW_NUMBER() OVER (PARTITION BY EXTRACT(YEAR FROM date) ORDER BY sale_dollars DESC) AS rank
  FROM `bigquery-public-data.iowa_liquor_sales.sales`
  WHERE EXTRACT(YEAR FROM date) IN (2019, 2020, 2021)
)
SELECT
  sale_year AS 年份,
  date AS 日期,
  sale_dollars AS 销售额,
  item_description AS 商品描述,
  store_name AS 门店名称
FROM yearly_sales_ranked
WHERE rank = 1
ORDER BY sale_year;

说明： #

1. CTE部分：先为每条记录标记所属年份，并按年份分组，对每组内的记录按sale_dollars降序排名，排名第一的就是当年销售额最高的记录。
2. 主查询：筛选出排名为1的记录，即可得到2019-2021年每年的最高销售额及关联字段。

备选方案：使用UNION ALL合并单年查询 #

如果不想用窗口函数，也可以将三个单年查询用UNION ALL合并，这种写法更直观，但效率略低于窗口函数（因为会扫描三次数据集）：

-- 2019年最高销售额
SELECT date, sale_dollars, item_description, store_name, 2019 AS sale_year
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE EXTRACT(YEAR FROM date) = 2019
ORDER BY sale_dollars DESC
LIMIT 1

UNION ALL

-- 2020年最高销售额
SELECT date, sale_dollars, item_description, store_name, 2020 AS sale_year
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE EXTRACT(YEAR FROM date) = 2020
ORDER BY sale_dollars DESC
LIMIT 1

UNION ALL

-- 2021年最高销售额
SELECT date, sale_dollars, item_description, store_name, 2021 AS sale_year
FROM `bigquery-public-data.iowa_liquor_sales.sales`
WHERE EXTRACT(YEAR FROM date) = 2021
ORDER BY sale_dollars DESC
LIMIT 1

ORDER BY sale_year;

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