Блог

У меня есть таблица SQL в следующем виде:

Результат timestamp user_id
1 125 1
1 125 0
2 125 1
2 144 0
3 156 1
3 125 1

и т.д….

Если я хочу найти скользящее среднее значение результатов пользователей, я могу использовать:

 SELECT user_id, timestamp, 
AVG(result) OVER (PARTITION BY user_id ORDER BY timestamp ASC ROWS UNBOUNDED PRECEDING) 
FROM table
 

Если у пользователя был только один результат на метку времени, это работает нормально, но если у пользователя есть несколько результатов в одной метке времени, он будет усредняться по результатам на этой метке времени плюс метки времени перед ним.

Итак, мой вопрос в том, как я могу убедиться, что оно усредняет только результаты, которые произошли, когда временная метка меньше текущей временной метки?

Заранее спасибо

По сути, вам нужен другой столбец, чтобы разорвать связи, например, первичный ключ таблицы, например, — в противном случае одноранговые строки рассматриваются все вместе в среднем по окну. Предполагая id :

 select t.*
    avg(result) over(partition by user_id order by timestamp, id) as avg_result
from mytable t
 

Обратите внимание, что rows спецификация фрейма здесь не нужна (как в вашем исходном запросе).

Если у вас нет такого столбца, мы можем сгенерировать его с row_number() помощью , что гарантирует, что каждому номеру присваивается только один раз. Однако результат не является согласованным, то есть при наличии связей одной и той же строке может быть присвоен другой номер строки во время последовательного выполнения одного и того же запроса:

 select t.*
    avg(result) over(partition by user_id order by timestamp, rn) as avg_result
from (
    select t.*,
        row_number() over(partition by user_id order by timestamp) as rn
    from mytable t
) t
 

Как насчет этого?

 SELECT [timestamp]
,   [user_id]
,   [result]
,   [final_avg] = (
        CONVERT(FLOAT, SUM([result]) OVER(PARTITION BY [user_id], [timestamp] ORDER BY [timestamp], [user_id], [result] ROWS UNBOUNDED PRECEDING))
        / COUNT([timestamp]) OVER(PARTITION BY [user_id], [timestamp] ORDER BY [timestamp], [user_id], [result] RANGE UNBOUNDED PRECEDING))
FROM table
ORDER BY [timestamp], [user_id], [result]
 

Результат:

 timestamp   user_id result  final_avg
1           125     0       0
1           125     1       0.5
2           125     1       1
2           144     0       0
3           125     1       1
3           156     1       1
 

Вы можете использовать спецификацию рамки окна. В стандартном SQL это было бы:

 SELECT user_id, timestamp, 
       AVG(result) OVER (PARTITION BY user_id
                         ORDER BY timestamp ASC
                         RANGE BETWEEN UNBOUNDED PRECEDING AND INTERVAL '1 SECOND' PRECEDING
                        ) 
FROM table;
 

Редактировать:

В BigQuery вы бы сделали:

Это стандартная функциональность SQL, но не все базы данных поддерживают ее.

 SELECT user_id, timestamp, 
       AVG(result) OVER (PARTITION BY user_id
                         ORDER BY UNIX_SECONDS(timestamp) ASC
                         RANGE BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
                        ) 
FROM table;
 

Предполагается, что секунды являются достаточной степенью детализации для различения предыдущих времен.