给定如下表:
client_id date connections
---------------------------------------
121438297 2018-01-03 0
121438297 2018-01-08 1
121438297 2018-01-10 3
121438297 2018-01-12 1
121438297 2018-01-19 7
363863811 2018-01-18 0
363863811 2018-01-30 5
363863811 2018-02-01 4
363863811 2018-02-10 0
我正在寻找一种有效的方法来求和当前行(当前行包含在求和中(之后6天内发生的连接数,按client_id进行分区,这将导致:
client_id date connections connections_within_6_days
---------------------------------------------------------------------
121438297 2018-01-03 0 1
121438297 2018-01-08 1 5
121438297 2018-01-10 3 4
121438297 2018-01-12 1 1
121438297 2018-01-19 7 7
363863811 2018-01-18 0 0
363863811 2018-01-30 5 9
363863811 2018-02-01 4 4
363863811 2018-02-10 0 0
问题:
我不想添加所有丢失的日期,然后执行滑动窗口计数以下7行,因为我的表已经非常大了。
我使用的是Impala,不支持
range between interval '7' days following and current row
。
编辑:我正在寻找一个通用答案,考虑到我需要将窗口大小更改为更大的数字(例如30天以上(
这回答了问题的原始版本。
Impala并不完全支持range between
。不幸的是,这并没有留下太多选择。一种是使用带有大量显式逻辑的lag()
:
select t.*,
( (case when lag(date, 6) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 6) over (partition by client_id order by date)
else 0
end) +
(case when lag(date, 5) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 5) over (partition by client_id order by date)
else 0
end) +
(case when lag(date, 4) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 4) over (partition by client_id order by date)
else 0
end) +
(case when lag(date, 3) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 3) over (partition by client_id order by date)
else 0
end) +
(case when lag(date, 2) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 2) over (partition by client_id order by date)
else 0
end) +
(case when lag(date, 1) over (partition by client_id order by date) = date - interval 6 day
then lag(connections, 1) over (partition by client_id order by date)
else 0
end) +
connections
) as connections_within_6_days
from t;
不幸的是,这并不能很好地概括。如果你想多呆几天,你可能想问另一个问题。