Блог

Главная - Вопросы по программированию - Как обнулить результат в OVER(), если интервал не «заполнен»

Как обнулить результат в OVER(), если интервал не «заполнен»

#mysql #sql #average #aggregate-functions #window-functions

#mysql #sql #среднее #агрегатные функции #окно-функции

Вопрос:

Допустим, у меня есть следующий набор данных и запрос:

 WITH results as (
    SELECT 'DAL' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2011 as season, 10 as wins union
    SELECT 'DET' as team, 2012 as season, 4 as wins union
    SELECT 'DET' as team, 2013 as season, 7 as wins union
    SELECT 'DET' as team, 2014 as season, 11 as wins union
    SELECT 'DET' as team, 2015 as season, 7 as wins union
    SELECT 'DET' as team, 2016 as season, 9 as wins union
    SELECT 'DET' as team, 2017 as season, 9 as wins union
    SELECT 'DET' as team, 2018 as season, 6 as wins union
    SELECT 'DET' as team, 2019 as season, 3 as wins
) SELECT team, season, wins,
    AVG(wins) OVER (partition by team order by season rows between 2 preceding and current row) AS avg_wins_last_two_seasons
from results;

Это вернет следующие результаты:

 # team, season, wins, avg_wins_last_two_seasons
DAL, 2010, 6, 6.0000
DET, 2010, 6, 6.0000
DET, 2011, 10, 8.0000

Однако, если размер окна не «полный» — т. Е. Нет двух предыдущих строк — тогда я хотел бы, чтобы результаты были NULL вместо усреднения по уменьшенному размеру окна — например, для 2010 вычисления avg as [6]/1 = 6 , но я хочу, чтобы он вычислялся как: [NULL, NULL, 6] / 3 = NULL . Как мне это сделать?

Ответ №1:

Вы можете использовать row_number() и case выражение:

 select team, season, wins,
    case when row_number() over(partition by team order by season) > 2
        then avg(wins) over (
            partition by team 
            order by season 
            rows between 2 preceding and current row
        ) 
    end as avg_wins_last_two_seasons
from results;

Это гарантирует, что перед вычислением среднего значения по окну есть как минимум две предыдущие строки; если нет, то null вместо этого возвращается this .

Комментарии:

1. спасибо, здесь один вопрос: почему вы используете ROW_NUMBER() вместо COUNT() или два одинаковых в приведенном выше случае? Мой ответ тоже выглядит нормально?

2. @David542: да, row_number() и count() ведите себя индентично в этой ситуации ( row_number() в любом случае, это подсчет по сути). Однако вам не нужна рамка окна для row_number() (или count() ); вам просто нужно знать, есть ли хотя бы две предыдущие строки.

3. спасибо за отзыв. Когда вы говорите «Вам не нужен фрейм окна для …» — вы имеете в виду, что мы можем использовать фрейм по умолчанию, который есть unbounded preceding to current row ? итак, мы можем оставить его пустым, и это подразумевается, или вы имеете в виду что-то еще? Еще раз спасибо за отзыв!

4. @David542: да, рамка окна по умолчанию достаточно хороша row_number() , как показано в моем ответе.

Ответ №2:

@GMB имеет принятый ответ здесь, но вот небольшое изменение, использующее именованное окно для удобства чтения и показывающее оба параметра (нулевой и допускающий неполный размер окна):

 WITH results as (
    SELECT 'DAL' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2010 as season, 6 as wins union
    SELECT 'DET' as team, 2011 as season, 10 as wins union
    SELECT 'DET' as team, 2012 as season, 4 as wins union
    SELECT 'DET' as team, 2013 as season, 7 as wins union
    SELECT 'DET' as team, 2014 as season, 11 as wins union
    SELECT 'DET' as team, 2015 as season, 7 as wins union
    SELECT 'DET' as team, 2016 as season, 9 as wins union
    SELECT 'DET' as team, 2017 as season, 9 as wins union
    SELECT 'DET' as team, 2018 as season, 6 as wins union
    SELECT 'DET' as team, 2019 as season, 3 as wins
) SELECT team, season, wins
    ,ROUND(AVG(wins) OVER trailing_2, 1) AS avg_wins_trailing_2
    ,IF(COUNT(1) OVER trailing_2 < 3, NULL, ROUND(AVG(wins) OVER trailing_2,1))AS avg_wins_trailing_2_if_full_window
FROM results
WINDOW trailing_2 AS (
    PARTITION BY team
    ORDER BY season
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
);

# team, season, wins, avg_wins_trailing_2, avg_wins_trailing_2_if_full_window
DAL, 2010, 6, 6.0, null
DET, 2010, 6, 6.0, null
DET, 2011, 10, 8.0, null
DET, 2012, 4, 6.7, 6.7
DET, 2013, 7, 7.0, 7.0
Метки: