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

Question

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

I have a table:

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中寻找它们。我还想在开始和结束列的范围内的datetime列中查找它们。最后，我想对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

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

Answer 1

在我下面:

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、end、col_1和col_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