我需要使用google中的iowa_liquor_sales_forecasting数据集上的两个表来了解2020年到2021年的销售增长率。

Question

我正在使用这个查询，但是没有显示数据。我的问题出什么问题了？

with t1 as
(
    select
        extract (year from date) as year,
        sum (sale_dollars) as sales_year0
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2020_sales_train`
    group by 1
),
t2 as
(
    select
        extract (year from date) as year,
        sum (sale_dollars) as sales_year1
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2021_sales_predict`
    group by 1
)
select *,
    round((sales_year1 - lag(sales_year0) over(order by t1.year asc)) / lag(sales_year0) over(order by t1.year asc) * 100,2)||'%' as growth_rate
from t1
join t2 on t1.year=t2.year

我的预期回报

year | total_sales | growth_rate |
2020 | ........... | ........... |
2021 | ........... | ........... |

Answer 1

JOIN本身(没有左，外，.)在BigQuery中是一个内连接，这意味着左边的表和右边的表必须共享一个条件才能产生结果。

目前，t1只有2020年，而t2只有2021年，导致连接条件join t2 on t1.year=t2.year为假。

从A内部连接B使用(x)

我想您要做的是计算growth_rate，下面是获取值的一种方法。

with
t1 as (
    select
        extract (year from date) as year_2020,
        sum (sale_dollars) as sales_year_2020
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2020_sales_train`
    group by 1
),
t2 as (
    select
        extract (year from date) as year_2021,
        sum (sale_dollars) as sales_year_2021
    from `bigquery-public-data.iowa_liquor_sales_forecasting.2021_sales_predict`
    group by 1
)
select
    *,
    -- round((year_2021 - year_2020) / year_2020 * 100, 3) as growth_rate  -- typo (wrong column names)
    round((sales_year_2021 - sales_year_2020) / sales_year_2020 * 100, 3) as growth_rate
from t1 join t2 on t1.year_2020 = (t2.year_2021 - 1)
;