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

Агрегатная функция от агрегатной функции стр. 3
Приведем ниже несколько стандартных решений рассматриваемой задачи.

1. Использование предиката ALL в предложении WHERE

Консоль
Выполнить
  1. SELECT maker, avg_price 
  2. FROM (SELECT maker, AVG(price) avg_price
  3.       FROM Product P JOIN PC ON P.model=PC.model 
  4.       GROUP BY maker
  5.      ) X
  6. WHERE avg_price >= ALL(SELECT AVG(price) avg_price
  7.       FROM Product P JOIN PC ON P.model=PC.model 
  8.       GROUP BY maker
  9.        );

На естественном языке этот запрос звучит следующим образом: «Найти производителей, средняя цена на ПК у которых не меньше, чем средние цены у КАЖДОГО из производителей ПК».

2. Использование внутреннего соединения

Консоль
Выполнить
  1. SELECT maker, avg_price 
  2. FROM (SELECT maker, AVG(price) avg_price
  3.       FROM Product P JOIN PC ON P.model=PC.model 
  4.       GROUP BY maker
  5.       ) X JOIN 
  6.       (SELECT MAX(avg_price) max_price 
  7.        FROM (SELECT maker, AVG(price) avg_price
  8.         FROM Product P JOIN PC ON P.model=PC.model 
  9.              GROUP BY maker
  10.              ) X
  11.        ) Y ON avg_price = max_price;

Здесь мы соединяем подзапрос, определяющий производителей и средние цены на их ПК, с подзапросом, в котором определяется максимальная средняя цена. Соединение выполняется по условию равенства средней цены из первого подзапроса с максимальной ценой из второго.

3. Использование предиката ALL в предложении HAVING

Консоль
Выполнить
  1. SELECT maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model=PC.model 
  3. GROUP BY maker
  4. HAVING AVG(price) >= ALL(SELECT AVG(price) 
  5.        FROM Product P JOIN PC ON P.model=PC.model 
  6.        GROUP BY maker
  7.            );

Это решение отличается от первого варианта отсутствием «лишнего» запроса, который пришлось написать лишь затем, чтобы была возможность использовать алиас avg_price в предложении WHERE (смотри порядок обработки предложений оператора SELECT); с другой стороны, использование в предложении WHERE агрегатной функции также запрещено правилами языка.

Все приведенные стандартные решения выглядят тяжеловесными, хотя и будут работать практически во всех СУБД. Эта громоздкость объясняется повторением в коде фактически одного и того же запроса. Однако общие табличные выражения –  CTE (общее табличное выражение) позволяет в рамках запроса задать таблицу, на которую можно многократно ссылаться.CTE, которые были введены в последних версиях стандарта, позволяют многократно ссылаться на один раз сформулированный запрос. Например, решения 1, 3 с помощью CTE можно записать в таком виде:

Консоль
Выполнить
  1. WITH cte(maker, avg_price)
  2. AS (
  3. SELECT maker, AVG(price) avg_price
  4. FROM Product P JOIN PC ON P.model=PC.model 
  5. GROUP BY maker
  6.    )
  7. SELECT *
  8. FROM cte 
  9. WHERE  avg_price>= ALL(SELECT avg_price 
  10.                        FROM cte
  11.                        );

Замечу, что поддержка общих табличных выражений появилась в  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server 2005 и в PostgreSQL 8.4. 

Bookmark and Share
Страницы: 1 2 3
Тэги:
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 Больше тэгов
Учебник обновлялся
месяц назад
обмен с wex
©SQL-EX,2008 [Развитие] [Связь] [О проекте] [Ссылки] [Team]
Перепечатка материалов сайта возможна только с разрешения автора.