Snowflake中用窗口函数统计±90天内ID出现次数

我需要编写查询语句，统计每个ID在当前行日期±90天窗口内的出现次数，希望通过窗口函数实现，是否可行？

现有实现（含模拟数据） #

WITH fake_data(id, DATE) as (
    SELECT * FROM VALUES
    -- 此ID仅访问1次
    (1, '2022-04-14'::date),
    -- 此ID访问3次
    (3, '2022-01-13'::date),
    (3, '2022-03-13'::date),
    (3, '2022-05-13'::date),
    -- 此ID访问多次
    (5, '2022-01-01'::date),
    (5, '2022-02-01'::date),
    (5, '2022-05-01'::date),
    (5, '2022-06-01'::date),
    (5, '2022-08-01'::date)
)
select * from (
    select 
    count_if("change" between -90 and 90) over (partition by ID, t1.DATE) "c",
    *
    from fake_data as t1
    left outer join lateral (
        select t1.DATE - t.DATE "change", t.DATE "t_DATE" 
        from fake_data AS t
        where t1.id = t.id and t1.DATE - t.DATE between -90 and 90
     ) as t2
    order by ID, t1.DATE, "change"
)
where "change" = 0;

查询结果 #

尝试的简化写法（存在问题） #

我希望简化成如下写法，但发现无法在PARTITION BY中使用当前行DATE的别名：

select
  count_if(DATE - d between -90 and 90) over (partition by id, DATE  as d) as "c",
  id,
  date
from fake_data;

解决方案：正确的窗口函数实现 #

可以用窗口函数直接实现，无需关联子查询。利用RANGE BETWEEN定义基于日期的窗口范围，按ID分组后，统计当前行日期±90天内的记录数：

WITH fake_data(id, DATE) as (
    SELECT * FROM VALUES
    (1, '2022-04-14'::date),
    (3, '2022-01-13'::date),
    (3, '2022-03-13'::date),
    (3, '2022-05-13'::date),
    (5, '2022-01-01'::date),
    (5, '2022-02-01'::date),
    (5, '2022-05-01'::date),
    (5, '2022-06-01'::date),
    (5, '2022-08-01'::date)
)
SELECT
    id,
    date,
    COUNT(*) OVER (
        PARTITION BY id
        ORDER BY date
        RANGE BETWEEN INTERVAL '-90' DAY PRECEDING AND INTERVAL '90' DAY FOLLOWING
    ) AS c
FROM fake_data
ORDER BY id, date;

说明 #

1. PARTITION BY id：按ID分组，确保只统计同一ID的记录
2. ORDER BY date：指定窗口内的排序依据，为RANGE范围定义提供基准
3. RANGE BETWEEN INTERVAL '-90' DAY PRECEDING AND INTERVAL '90' DAY FOLLOWING：定义窗口范围为当前行日期往前90天到往后90天
4. COUNT(*)：统计窗口内的记录总数，即当前行日期±90天内的ID出现次数

此写法逻辑简洁，执行效率远高于原关联子查询的实现，且结果与原查询完全一致。

内容的提问来源于stack exchange，提问作者Fredrik Erlandsson