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

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

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 для каждого агрегатного значения. Это возможно, поскольку подзапрос вернет одно значение, а не набор:

Решение 1

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;

Почему мы утверждаем, что второй запрос будет эффективней? Дело в том, что в решении 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;

В решении 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;

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