Redshift - Оконная функция значений за последний час

У меня есть таблица со столбцами, похожими на: 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

Этот запрос длится вечно, так как это перекрестное соединение, и в результате получается декартово произведение между таблицами. Любые предложения о том, как этого добиться? В таблице несколько миллионов событий, поэтому любые улучшения будут очень полезны!


postgresql sql amazon-redshift window-functions
person kevin.w.johnson    schedule 23.01.2020    source источник
comment
Прошлый час означает предыдущие 60 минут или предыдущий календарный час?   -  person Tim Biegeleisen    schedule 23.01.2020
comment
Средние предыдущие 60 минут.   -  person kevin.w.johnson    schedule 23.01.2020

Ответы (2)


arrow_upward
1
arrow_downward

Я не уверен, поддерживает ли Redshift предложение window range. Если это так, это должно быть так просто, как:

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) как для соединения, так и для подзапроса.

person GMB    schedule 23.01.2020
comment
Судя по тому, что я видел, это не так. - person kevin.w.johnson; 23.01.2020
comment
@ kevin.w.johnson: Я этого боялся ... В этом случае я действительно не вижу альтернативы, кроме соединения или коррелированного подзапроса. Я добавил к своему ответу коррелированное решение подзапроса (не уверен, будет ли оно работать быстрее, чем соединение). - person GMB; 23.01.2020

arrow_upward
1
arrow_downward

Если я правильно понимаю, возможно, мы сможем решить эту проблему в два этапа. Сначала вычислите количество в каждом магазине за каждый час, используя TO_CHAR, чтобы определить дату до часа. Затем выполните подзапрос и возьмите запаздывание этого счетчика, чтобы сгенерировать счетчик за предыдущий час.

WITH cte AS (
    SELECT *,
        TO_CHAR(event_datetime, 'YYYY-MM-DD HH24') AS event_hour,
        COUNT(event_id) OVER (PARTITION BY store_id,
                  TO_CHAR(event_datetime, 'YYYY-MM-DD HH24')) event_cnt
    FROM mtable
)

SELECT
   event_id,
   LAG(event_cnt) OVER (PARTITION BY store_id ORDER BY event_hour) prev_event_cnt
FROM cte;
person Tim Biegeleisen    schedule 23.01.2020
comment
Похоже, это близко. Если событие происходит в конце часа (то есть 59 минут), это пропустит все события, произошедшие в тот же час, не так ли? Я могу попробовать разбить на разделы поминутно и, возможно, включить последние 60. - person kevin.w.johnson; 23.01.2020
comment
@kevin Да, но на самом деле это требование, которое вы действительно указали в своем исходном вопросе. - person Tim Biegeleisen; 23.01.2020
comment
Ты прав. Я имел в виду предыдущие 60 минут вместо предыдущего часа. Я смогу работать с таким подходом! - person kevin.w.johnson; 23.01.2020
comment
Единственная проблема, которую он не решает, заключается в том, что если в конкретный час было ноль событий, он будет смотреть на последний час, когда произошло событие, чтобы получить счет, вместо того, чтобы говорить 0. - person kevin.w.johnson; 23.01.2020