loading..
Русский    English
11:02
листать

Агрегатная функция от агрегатной функции стр. 2

Наверняка, вам встретятся решения подобных задач на основе сортировки с ограничением на число строк результирующего набора. Однако такие решения не являются легитимными с точки зрения стандарта языка и, как следствие, имеют различный синтаксис в разных реализациях. В качестве примера приведу решения нашей задачи в диалектах  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server и MySQL.

SQL Server
Консоль
Выполнить
  1. SELECT TOP 1 AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model 
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

MySQL
  1. SELECT AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model
  3. GROUP BY maker
  4. ORDER BY avg_price DESC
  5. LIMIT 1;

Оба этих решения берут только первую строку из отсортированного по убыванию набора средних цен.

У начинающих изучать SQL зачастую вызывает проблему определение производителя, для которого достигается искомый максимум/минимум. Другими словами, требуется найти максимальную среднюю цену и производителя, средняя цена ПК которого совпадает с этой максимальной средней ценой.

Нестандартными средствами эта задача решается фактически рассмотренным выше запросом:

Консоль
Выполнить
  1. SELECT TOP 1 maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model 
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

Использование maker в списке столбцов предложения SELECT вполне допустимо, т.к. по этому столбцу выполняется группировка. Однако тут имеется одна «ловушка». Она связана с тем, что максимум может достигаться для нескольких производителей, и в данной постановке задачи их нужно выводить всех, в то время как мы ограничиваем выборку только одной (первой) строкой. На этот случай диалект  T-SQL (Transact-SQL) — процедурное расширение языка SQL, используемое для программирования на стороне сервера в Microsoft SQL Server и Sybase ASE.T-SQL имеет дополнительную конструкцию WITH TIES. Логически правильное решение будет иметь вид:

Консоль
Выполнить
  1. SELECT TOP 1 WITH TIES maker, AVG(price) avg_price
  2. FROM Product P JOIN PC ON P.model = PC.model 
  3. GROUP BY maker
  4. ORDER BY avg_price DESC;

Однако, если иметь в виду проблему переносимости кода, то следует предпочесть решение, использующее стандартные средства.

Примечание:

На сайте SQL-EX.RU проблема переносимости кода возникла в связи с нашим намерением реализовать упражнения для различных СУБД. Реализация потребовала бы минимальных средств, если бы тестовые решения, используемые для проверки, работали бы на всех предполагаемых СУБД без изменения своего кода. Поэтому следование стандарту может являться одним из требований тех. задания на проект.

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