google-bigquery - BigQuery - 从特定日期开始的滚动年份乘以具有递减系数的列

Question

我的表中有 2 列；时间戳（从 2020-01-01 00:00:00 开始，到 2050-12-31 23:00:00 结束，间隔 1 小时）和每小时的生产数据。我想将该生产数据同比降低 0.5%。第一年的生产数据不会改变。在第二年，它将开始退化（乘以全年相同的值）。

如果我想从 2021-08-01 00:00:00 开始对生产列应用降级，我该怎么做？例如，第一年的日期将包括 2021-08-01 00:00:00 和 2022-07-31 23:00:00 之间，产量将乘以 1。在第二年 (2022-08-01 00 :00:00 - 2023-07-31 23:00:00) 产量将乘以 0.995..

标签： google-bigquery

Answer 1

退化的第一年的系数为 0.995，第二年的系数为 0.995*0.995，等等。您可以将问题分为两部分：

1. 指定具有相同退化级别的行组（g下面的列）和
2. 将生产数据乘以预先计算的系数。

询问：

#standardSql
with sample as (
select  timestamp '2020-01-01 00:00:00' as t, 100 as d union all
select  timestamp '2020-08-01 00:00:00', 100 union all
select  timestamp '2021-01-01 00:00:00', 100 union all
select  timestamp '2021-07-31 23:00:00', 100 union all
select  timestamp '2021-08-01 00:00:00', 100 union all
select  timestamp '2022-01-01 00:00:00', 100 union all
select  timestamp '2022-08-01 00:00:00', 100 
), partial_result as (
  select t,d, div(greatest(date_diff(cast(t as date), cast('2020-08-01' as date), month),0),12) as g from sample
)
select t,d, d*pow(0.995,g) as newd
from partial_result

newd可以在更新语句中使用。

请注意，如果您想对第 n 级的值进行四舍五入并从前一级别的舍入值计算 n+1 级值，则此方法不可用。