在BigQuery中，每个id有效地获取限制在过去6个月内每个id的所有以前日期的数组
在BigQuery中，每个id有效地获取限制在过去6个月内每个id的所有以前日期的数组
提问于 2022-07-14 04:53:44
我有一个很大的桌子'DATES_EVENTS‘(20T)，看起来是这样的：
ID DATE
1 '2022-04-01'
1 '2022-03-02'
1 '2022-03-01'
2 '2022-05-01'
3 '2021-12-01'
3 '2021-11-11'
3 '2020-11-11'
3 '2020-10-01'我希望每一行得到所有过去的日期(每个用户)限制为6个月。
我想要的桌子：
ID DATE DATE_list
1 '2022-04-01' ['2022-04-01','2022-03-02','2022-03-01']
1 '2022-03-02' ['2022-03-02','2022-03-01']
1 '2022-03-01' ['2022-03-01']
2 '2022-05-01' ['2022-05-01']
3 '2021-12-01' ['2021-12-01','2021-11-11']
3 '2021-11-11' ['2021-11-11']
3 '2020-11-11' ['2020-11-11','2020-10-01']
3 '2020-10-01' ['2020-10-01']我对所有日期都有一个不受限制的解决办法：
SELECT
ID, DATE, ARRAY_AGG(DATE) OVER (PARTITION BY ID ORDER BY DATE) as DATE_list
FROM
DATES_EVENTS但在长达6个月的时间里，我没有一个有效的解决方案：
SELECT
distinct A.ID, A.DATE, ARRAY_AGG(B.DATE) OVER (PARTITION BY B.ID ORDER BY B.DATE) as DATE_list
FROM
DATES_EVENTS A
INNER JOIN
DATES_EVENTS B
ON
A.ID=B.ID
AND B.DATE BETWEEN DATE_SUB(A.DATE, INTERVAL 180 DAY) AND A.DATE
** ruffly a solution有人知道一个好的高效的方法来满足我的需求吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-07-14 05:15:16
考虑以下方法
select id, date, array(
select day
from t.date_list day
where day <= date
order by day desc
) as date_list
from (
select *, array_agg(date) over win as date_list
from dates_events
window win as (
partition by id
order by extract(year from date) * 12 + extract(month from date)
range between 5 preceding and current row
)
) t如果应用于问题中的样本数据，则输出为
如果(正如我在你的问题中所注意到的) 180天是合适的替代6个月-你可以使用下面的更简单的版本
select *, array_agg(date) over win as date_list
from dates_events
window win as (
partition by id
order by unix_date(date)
range between current row and 179 following
)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/72975366
复制相关文章
相似问题
腾讯云开发者
