Другие примеры использования оконных функций

Использование оконных функций, как и CTE, помогает сократить объем кода. Вернемся к задаче, на которой мы демонстрировали преимущества использования CTE:

Используем следующий алгоритм. К результатам запроса, объединяющему все операции из 4 таблиц базы «Вторсырье», добавим столбец, который с помощью оконной функции MAX определит максимальную сумму. Затем мы отберем те строки, у которых сумма операции совпадает с этим максимальным значением:

SELECT max_sum, type, date, point   
FROM (  
        SELECT max(inc) over() AS max_sum, *  
        FROM (  
              SELECT inc, 'inc' type, date, point FROM Income   
              UNION ALL   
              SELECT inc, 'inc' type, date, point FROM Income_o   
              UNION ALL   
              SELECT out, 'out' type, date, point FROM Outcome_o   
              UNION ALL   
              SELECT out, 'out' type, date, point FROM Outcome   
             ) X   
     ) Y  
WHERE inc = max_sum;

Рассмотрим еще один пример.

Здесь, в отличие от предыдущих задач, требуется выполнить разбиение компьютеров на группы с одинаковым значением speed, которое мы реализуем с помощью предложения PARTITION BY. Именно скорость текущей строки таблицы и будет определять группу для вычисления среднего значения.

Решение

SELECT *, price - AVG(price) OVER(PARTITION BY speed) AS dprice   
FROM PC;

Другое решение этой задачи можно построить с помощью коррелирующего подзапроса.

SELECT *, price - (SELECT AVG(price) FROM PC AS PC1 WHERE PC1.speed = PC.speed) AS dprice   
FROM PC;

Рекомендуемые упражнения: 109, 134