BigQuery爱荷华酒类销售数据集2020-2021销售增长率查询无结果排查

问题根源 #

原查询中，t1从2020年的表提取年份，结果只能是2020；t2从2021年的表提取年份，结果只能是2021。用t1.year = t2.year做等值连接时，两个表没有匹配的年份值，因此返回空结果。

修正后的查询语句 #

要得到期望的结果，应该先将两张表的年度销售数据合并，再通过窗口函数计算增长率：

with annual_sales as (
    select
        extract(year from date) as year,
        sum(sale_dollars) as total_sales
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2020_sales_train`
    group by 1
    union all
    select
        extract(year from date) as year,
        sum(sale_dollars) as total_sales
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2021_sales_predict`
    group by 1
)
select
    year,
    total_sales,
    case
        when lag(total_sales) over(order by year) is null then 'N/A'
        else round((total_sales - lag(total_sales) over(order by year)) / lag(total_sales) over(order by year) * 100, 2) || '%'
    end as growth_rate
from annual_sales
order by year;

说明 #

• 使用union all合并2020和2021年的年度销售数据，得到包含两年数据的结果集。
• 通过lag(total_sales) over(order by year)获取上一年的销售额，计算增长率。2020年没有上一年数据，用N/A表示增长率。
• 最终结果会按年份排序，符合你期望的输出格式。

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