loading..
Русский    English
09:39
листать

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

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

Найти максимальную сумму прихода/расхода среди всех 4-х таблиц базы данных "Вторсырье", а также тип операции, дату и пункт приема, когда и где она была зафиксирована.


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

Консоль
Выполнить
  1. SELECT max_sum, type, date, point
  2. FROM (
  3. SELECT MAX(inc) over() AS max_sum, *
  4. FROM (
  5.   SELECT inc, 'inc' type, date, point FROM Income
  6.   UNION ALL
  7.   SELECT inc, 'inc' type, date, point FROM Income_o
  8.   UNION ALL
  9.   SELECT out, 'out' type, date, point FROM Outcome_o
  10.   UNION ALL
  11.   SELECT out, 'out' type, date, point FROM Outcome
  12. ) X
  13. ) Y
  14. WHERE inc = max_sum;

max_sum type date point
18000,00 inc 2001-10-02 00:00:00.000 3

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

Для каждого ПК из таблицы PC найти разность между его ценой и средней ценой на модели с таким же значением скорости ЦП.


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

Консоль
Выполнить
  1. SELECT *, price - AVG(price) OVER(PARTITION BY speed) AS dprice
  2. FROM PC;

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

Консоль
Выполнить
  1. SELECT *, price - (SELECT AVG(price) FROM PC AS PC1 WHERE PC1.speed = PC.speed) AS dprice
  2. FROM PC;

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

Bookmark and Share
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE Convert COUNT CROSS APPLY CTE DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INNER JOIN insert INTERSECT IS NOT NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker MAX MIN MONTH MySQL NOT Больше тэгов
Учебник обновлялся
месяц назад
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.
Rambler's Top100