loading..
Русский    English
06:35
листать

Комбинация детализированных и агрегированных данных

Пусть, помимо модели и цены принтера, требуется еще вывести максимальную и минимальную цену по всему множеству принтеров. Для новичка подобная задача зачастую представляет определенную сложность. Эта сложность состоит в дилемме: группировка или агрегация по всему множеству. Если использовать группировку, то максимум и минимум будут получены не по всей выборке, а для каждого подмножества, определяемого группировкой (в данном примере для каждой группы с одинаковой комбинацией значений {модель, цена}). Поскольку эта комбинация уникальна в таблице Printer учебной базы данных, то мы получим 3 совпадающих значения цены:

Консоль
Выполнить
  1. SELECT model, price, MIN(price) min_price, MAX(price) max_price
  2. FROM printer
  3. GROUP BY model, price;

Если же не использовать группировку, то мы можем получить только минимальное и максимальное значение, поскольку стандартный синтаксис запрещает (ввиду неоднозначности трактовки результата) использовать наряду с агрегатами детализированные данные, по которым не выполняется группировка:

Консоль
Выполнить
  1. SELECT MIN(price) min_price, MAX(price) max_price
  2. FROM printer;

Проблема разрешается довольно просто, причем не единственным способом. Так можно использовать подзапросы в предложении SELECT для каждого агрегатного значения. Это возможно, поскольку подзапрос вернет одно значение, а не набор:

Решение 1

Консоль
Выполнить
  1. SELECT model, price,
  2. (SELECT MIN(price) FROM Printer) min_price,
  3. (SELECT MAX(price) FROM Printer) max_price
  4. FROM printer;

Более эффективным приемом будет использование подзапроса для вычисления агрегатов в предложении FROM, наряду с декартовым произведением. Бояться декартового произведения в этом случае не нужно, т.к. подзапрос вернет только одну строку, которая и будет соединяться с каждой строкой детализированных данных.

Решение 2

Консоль
Выполнить
  1. SELECT model, price, min_price, max_price
  2. FROM printer CROSS JOIN
  3. (SELECT MIN(price) min_price, MAX(price) max_price
  4. FROM printer) X;

Почему мы утверждаем, что второй запрос будет эффективней? Дело в том, что в решении 1 подзапрос будет вычисляться дважды, а не один раз, как это делается во втором решении. Кроме того, если оптимизатор недостаточно "умный", подзапросы в первом решении будут вычисляться для каждой строки детализированных данных. Проверьте планы выполнения для своей СУБД.

Рассмотрим теперь задачу, когда агрегат зависит от текущей строки детализированных данных, например, такую.

Вывести номер модели и цену принтера, а также максимальную и минимальную цену на принтеры того же типа.

Попытаемся адаптировать для этой задачи рассмотренные выше подходы. В решении 1 подзапросы следует сделать коррелирующими :

Решение 1M

Консоль
Выполнить
  1. SELECT model, price, type,
  2. (SELECT MIN(price) FROM Printer P1 WHERE P1.type=P.type) min_price,
  3. (SELECT MAX(price) FROM Printer P1 WHERE P1.type=P.type) max_price
  4. FROM printer P;

В решении 2 мы можем воспользоваться нестандартным соединением CROSS APPLY (SQL Server), использующим коррелирующий подзапрос в предложении FROM.

 Решение 2M

Консоль
Выполнить
  1. SELECT model, price, P.type, min_price, max_price
  2. FROM Printer P CROSS APPLY
  3. (SELECT MIN(price) min_price, MAX(price) max_price
  4. FROM Printer P1
  5. WHERE P1.type=P.type) X;

Для наглядности в решения 1M и 2M добавлен столбец type.

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