时间戳

首页 > 解决方案 > bigquery 窗口函数对其他列范围内的值求和

google-bigquery - bigquery 窗口函数对其他列范围内的值求和

问题描述

我想我需要一个窗口函数，但它太具体了，我找不到答案。

我有一张桌子：

datetime             col1  col2  sum_col    start                        end          col_1  col_2
2020-09-21 10:24:40          z    2      2020-09-21 10:24:40     2020-09-22 11:25:10    x      y
2020-09-21 10:24:50          z    2
2020-09-21 10:25:00     x    z    3
2020-09-21 10:25:10     x    z    4
....                   ...  ...   n
2020-09-22 11:24:40     x    y    4
2020-09-22 11:24:50     x    y    4
2020-09-22 11:25:00          y    3
2020-09-22 11:25:10          y    3

我想要的是检查 col_1 和 col_2 中的值，并在 col1 和 col2 中查找它们。我还想在开始和结束列范围内的日期时间列中查找它们。最后，我想对 sum_col 中属于前面解释的条件的值求和。

结果将是：

start                        end              col_1     col_2      sum(sum_col)
2020-09-21 10:24:40     2020-09-22 11:25:10     x          y           8+n

我希望我没有要求太多。我尝试了一些不起作用的不同方法，使用这个窗口功能我什至不知道从哪里开始。

标签： google-bigquery

解决方案

下面是我的看法：

with treat_data as (
  select 
    datetime_,
    col1,
    col2,
    sum_col,
    first_value(start_) over (order by datetime_ asc rows between unbounded preceding and current row) as start_, 
    first_value(end_) over (order by datetime_ asc rows between unbounded preceding and current row) as end_,
    first_value(col_1) over (order by datetime_ asc rows between unbounded preceding and current row) as col_1,
    first_value(col_2) over (order by datetime_ asc rows between unbounded preceding and current row) as col_2
  from
    data_
)
select
  start_,
  end_,
  col_1,
  col_2,
  sum(sum_col) as sum_col
from
  treat_data
where
  col1 = col_1 and
  col2 = col_2 and
  datetime_ between start_ and end_
group by
  1, 2, 3, 4

我使用 FIRST_VALUE() 来填充、和上的所有空值start，并使用表中显示的第一个值。endcol_1col_2

然后，一旦完成，就非常容易遵循：

  • 您设置提到的列，加上列的总和sum_col，以及
  • 使用 WHERE 子句声明您只想要那些行的总和
    • col1并且col_1是平等的，
    • col2并且col_2是相等的，并且
    • datetime介于start和之间end

使用您作为示例的 8 行后，输出如下：

start              | end               |col_1|col_2|sum_col
-------------------+-------------------+-----+-----+-------
2020-09-21T10:24:40|2020-09-22T11:25:10|x    |y    |8

推荐阅读