雪花：通过滑动窗口（过去60分钟）的数据集汇总，该数据集的采样频率是不均匀的

Question

我有具有不均匀采样区的数据。我想以滚动/滑动为基础（过去60分钟）进行汇总数据。

为了实现小时平均值（由城市划分），我曾经遵循有效的代码。

SELECT *,
       AVG(VALUE) OVER (PARTITION BY CITY, DATE_AND_HOUR ORDER BY TIMESTAMP
FROM 
(
SELECT *,
       date_trunc('HOUR', TIMESTAMP) as DATE_AND_Hour

FROM SAMPLE_DATA
)

但是，我所需的输出如下：

我知道雪花不支持范围，并且我无法使用Windows功能中的哪个行进行指定，因为我的采样dist是不均匀的。

我在此页面上阅读了一些潜在的解决方案，但它们在雪花上不起作用：使用SQL窗口函数的最后N天数量 本质上，这是一个类似的问题。

Answer 1

首先，您在示例中显示为“平均”是“总和”，而您的第一个“上诉”结果包括“北京”结果。

您有两个选项，构建一个固定尺寸的窗口数据集（为每分钟构建部分），然后使用固定尺寸的窗框，或自加入并汇总这些窗口（如Felipe所示）。

如果您的数据非常密集，则可能会发现以前的性能更高，如果数据稀疏，则以后的方法应该更快，并且代码肯定会更快。

因此，简单的第一：

with data(city, timestamp, value) as (
    select column1, try_to_timestamp(column2, 'yyyy/mm/dd hh:mi'), column3 from values 
    ('beijing', '2022/05/25 10:33', 22),
    ('beijing', '2022/05/25 10:37', 20),
    ('beijing', '2022/05/25 11:36', 29),
    ('beijing', '2022/05/26 11:36', 28),
    ('beijing', '2022/05/26 10:00', 21),
    ('shanghai', '2022/05/26 11:00', 33),
    ('shanghai', '2022/05/26 11:46', 35),
    ('shanghai', '2022/05/26 12:40', 37)
)

select a.*
    ,avg(b.value) as p60_avg
    ,count(b.value)-1 as p60_count
    ,sum(b.value) as p60_sum
from data as a
left join data as b
    on a.city = b.city and b.timestamp between dateadd(hour, -1, a.timestamp) and a.timestamp
group by 1,2,3
order by 1,2

给予：

城市	时间戳	值	P60_AVG	P60_COUNT	P60_SUM
北京	2022-05-25 10：33：00.000	2	22	北京	2022-05-25
10	：37：37：00.000	20	21	1	42
22	22 ：36：00.000	29	24.5	1	49
北京	2022-05-26 10：00：00.000	21	21	0	21
北京	2022-05-26 11：36：00.000	28	0 28	0	28
上海	2022-05-26 11：00：00.000	33 33 33	33 33 33 33 33 33 33 33 33 33 33 33 33 33 33 33 33	0	33
上海	：00.000	35	34	1	68
上海	12：40：00.000	37	36	1	72

致密版本：

with data(city, timestamp, value) as (
    select column1, try_to_timestamp(column2, 'yyyy/mm/dd hh:mi'), column3 from values 
    ('beijing', '2022/05/25 10:33', 22),
    ('beijing', '2022/05/25 10:37', 20),
    ('beijing', '2022/05/25 11:36', 29),
    ('beijing', '2022/05/26 11:36', 28),
    ('beijing', '2022/05/26 10:00', 21),
    ('shanghai', '2022/05/26 11:00', 33),
    ('shanghai', '2022/05/26 11:46', 35),
    ('shanghai', '2022/05/26 12:40', 37)
), filled_time as (
    select city,
        dateadd(minute, row_number() over(partition by city order by null)-1, min_t) as timestamp
    from (
        select 
            city, min(timestamp) as min_t, max(timestamp) as max_t
        from data
        group by 1
    ), table(generator(ROWCOUNT => 10000))
    qualify timestamp <= max_t
)
select
    ft.city
    ,ft.timestamp
    ,avg(d.value) over (order by ft.timestamp ROWS BETWEEN 60 PRECEDING AND current row ) as p60_avg
from filled_time as ft
left join data as d
    on ft.city = d.city and ft.timestamp = d.timestamp
order by 1,2;

给予：

City	Timestamp	P60_AVGG GEN
2022-05-26 11： 46	2022-05-26 33：00.000	22
北京	2022-05-25 10：34：00.000	22
北京	2022-05-25 10：35：00.000	22
北京	2022-05-25 10：36：	22
北京	00.000 00.000	21
北京	2022-05-25 10：38：00.000	北京
2022-05-25	10：39：00.000	21
北京	2022-05-25 10：40：00.000	21
21	：00.000	北京
北京	2022-05-25 10：42：00.000	北京
2022-05-25	：43：00.000	21
北京北京	：44：00.000	21
北京	2022-05-25 10：45：	21
10	2022-05-25 10 00.000 -05-25 10：46：00.000	21
狙击...

那些“额外”的行可能会被合格倾倒

select
    ft.city
    ,ft.timestamp
    ,avg(d.value) over (order by ft.timestamp ROWS BETWEEN 60 PRECEDING AND current row ) as p60_avg
    --,count(b.value)-1 as p60_count
    --,sum(b.value) as p60_sum
from filled_time as ft
left join data as d
    on ft.city = d.city and ft.timestamp = d.timestamp
qualify d.value is not null
order by 1,2;

Answer 2

您可以通过自我加入来解决此问题：

with data as (
    select *
    from temp_fh_wikipedia.public.wikipedia_2020
    where title in ('San_Francisco', 'Los_Angeles')
    and wiki='en'
    and datehour > '2020-10-13'
)

select a.title, a.datehour, a.views, avg(b.views) avg_previous_5h
from data a
join (
    select *
    from data
) b
on a.title=b.title
and b.datehour between timestampadd(hour, -5, a.datehour) and a.datehour

group by 1, 2, 3
order by 1, 2
limit 100

如果您想要最后的X分钟，请更改“小时”“分钟”。