sql - Redshift - 过去一小时值的窗函数

Question

我有一个类似于以下列的表格:event_id , store_id , event_datetime .

对于每个事件，我试图弄清楚前一小时同一家商店发生了多少事件。我知道我可以运行如下查询:

SELECT 
    event_id, 
    COUNT(event_id) OVER (PARTITION BY store_id ORDER BY event_datetime 
                          ROWS BETWEEN 5 preceding and current row)
FROM mtable;

为了在商店获得 5 个前面的事件，但我需要这个数字是过去一小时内事件总数的变量。有没有办法做到这一点？否则，我必须将表格加入自己，例如:

SELECT 
    event_id, 
    COUNT(prevevents.event_id) 
FROM mtable m 
INNER JOIN mtable prevevents 
    ON prevevents.event_datetime BETWEEN (m.event_datetime - interval '1 hour') AND
       m.event_datetime

这个查询需要永远，因为它是一个交叉连接并导致表之间的笛卡尔积。关于如何实现这一目标的任何建议？表中有几百万个事件，因此任何改进都会非常有用!

Answer 1

我不确定 Redshift 是否支持范围窗口子句。如果是这样，这应该很简单:

SELECT 
    event_id, 
    COUNT(event_id) OVER (
        PARTITION BY store_id 
        ORDER BY DATE_PART(epoch, date_column)
        RANGE BETWEEN 3600 PRECEDING AND 1 PRECEDING
    ) cnt
FROM mtable;

如果此功能不可用，则我看不到连接或相关子查询的另一个选项:

select 
    t.event_id,
    (
        select count(*) 
        from mytable t1 
        where 
            t1.store_id = t.store_id 
            and t1.event_datetime >= t.event_datetime - interval '1 hour'
            and t1.event_datetime < t.event_datetime
    ) cnt
from mytable t

相关子查询的性能可能更好，因为它预先聚合了数据。对于连接或子查询，请确保您在 (store_id, event_datetime) 上有索引。 .