Список агрегирующих оконных функций

Список основных функций для использования в агрегирующих оконных функций:

1. AVG() - среднее
2. SUM() - сумма 
3. COUNT() - количество
4. MAX() - максимальное значение
5. MIN() - минимальное значение
6. MODE() - мода
7. PERCINTILE_CONT(дробь перцентиля) - перцентиль. Если в аргумент подставить 0.5, то получаем медиану;

Самое интересное здесь, если мы захотим посчитать внутри окна уникальные значения, то у нас это не получится, то есть конструкция COUNT(distinct column1)  over (partition by column2) работать не будет. О том, как это реализовать, обсудим чуть позже. 

Что еще важно знать про оконные функции:

1. Оконные функции нельзя использовать в условии where, потому что where это обращение к исходной таблице.  Если хотите отфильтроваться по результату оконки - пишите подзапрос. 

Допустим, нам нужно из прошлого примера, отобрать строки только для команд с рейтингом выше 70

select
    club,
    short_name,
    overall,
    avg(overall) over (partition by club) as avg_overall
from
    fifa_players_2018
where avg_overall > 70

Вернет ошибку avg_overall column does not exist.

Пишем подзапрос, и все прекрасно отрабатывает:

select
    *
from (
    select
        club,
        short_name,
        overall,
        avg(overall) over (partition by club) as avg_overall
    from
        fifa_players_2018
) as t
where avg_overall > 70

1. Внутри оконной функции можно делать преобразования (например, менять типы данных, преобразовывать аргумент функции)
2. Результат оконной функции можно комбинировать в вычислиниях или преобразованиях в SELECT

Давайте раозбьем на группы округленный до 10 (всегда в меньшую сторону) средний рейтинг команды:

• Если выше 80 то Top 
• Если от 70 до 80 то Good
• Остальные Not interesting

select
    club,
    short_name,
    overall,	
    case 
        when trunc(avg(overall) over (partition by club), -1) >= 80 then 'Top'
        when trunc(avg(overall) over (partition by club), -1) >= 70 then 'Good'
        else 'Not interesting'
    end rating_group,
    avg(overall) over (partition by club) as avg_overall
from
    fifa_players_2018

Как вы видите, результат оконной функции мы можем использовать в CASE WHEN конструкциях, а также преобразовывать сам результат оконной функции (в нашем примере мы использовали функцию TRUNC(), чтобы округлить до десяти).