loading..
Русский    English
14:52
листать

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

Использование оконных функций, как и 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
Тэги:
ALL AND AUTO_INCREMENT AVG battles CASE CAST CHAR CHARINDEX CHECK classes COALESCE CONSTRAINT Convert COUNT CROSS APPLY CTE DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DELETE DISTINCT DML EXCEPT EXISTS EXTRACT FOREIGN KEY FROM FULL JOIN GROUP BY Guadalcanal HAVING IDENTITY IN INFORMATION_SCHEMA INNER JOIN insert INTERSECT IS NOT NULL IS NULL ISNULL laptop LEFT LEFT OUTER JOIN LEN maker Больше тэгов
Учебник обновлялся
несколько дней назад
https://exchangesumo.com/obmen/OMG-KSTRUAH/ . Как бороться с конденсатом в теплице?
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.