Оператор SELECT

Получение итоговых значений

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

Консоль

Выполнить

SELECT model, price, MIN(price) min_price, MAX(price) max_price 
FROM printer
GROUP BY model, price;

select model, price, min(price) min_price, max(price) max_price from printer group by model, price;

    Схема

Нет схемы

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

Консоль

Выполнить

SELECT MIN(price) min_price, MAX(price) max_price 
FROM printer;

select min(price) min_price, max(price) max_price from printer;

    Схема

Нет схемы

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

Решение 1

Консоль

Выполнить

SELECT model, price,
(SELECT MIN(price) FROM Printer) min_price, 
(SELECT MAX(price) FROM Printer) max_price 
FROM printer;

select model, price, (select min(price) from Printer) min_price, (select max(price) from Printer) max_price from printer;

    Схема

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

Решение 2

Консоль

Выполнить

SELECT model, price, min_price, max_price 
FROM printer CROSS JOIN 
(SELECT MIN(price) min_price, MAX(price) max_price 
FROM printer) X;

select model, price, min_price, max_price from printer cross join (select min(price) min_price, max(price) max_price from printer) X;

    Схема

Нет схемы

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

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

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

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

Решение 1M

Консоль

Выполнить

SELECT model, price, type,
(SELECT MIN(price) FROM Printer P1 WHERE P1.type=P.type) min_price, 
(SELECT MAX(price) FROM Printer P1 WHERE P1.type=P.type) max_price 
FROM printer P;

select model, price, type, (select min(price) from Printer P1 where P1.type=P.type) min_price, (select max(price) from Printer P1 where P1.type=P.type) max_price from printer P;

    Схема

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

 Решение 2M

Консоль

Выполнить

SELECT model, price, P.type, min_price, max_price 
FROM Printer P CROSS APPLY 
(SELECT MIN(price) min_price, MAX(price) max_price 
FROM Printer P1
WHERE P1.type=P.type) X;

select model, price, P.type, min_price, max_price from Printer P cross apply (select min(price) min_price, max(price) max_price from Printer P1 where P1.type=P.type) X;

    Схема

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