SQL сравнивает агрегированные средние значения (Борей)

Мой вопрос относится, в частности, к базе данных Northwind, поэтому я не привожу воспроизводимый пример/набор данных.

Я хотел бы выбрать все продукты, чья цена за единицу больше средней цены за единицу для каждой категории, за исключением одной категории, к которой принадлежит данный продукт. Я пробовал два разных подхода, ни один из которых пока не дал желаемых результатов.

Этот возвращает агрегированный набор данных, но я не знаю, как (полагаю, используя?) сравнить каждую UnitPrice со средней ценой за единицу для всех категорий, кроме той, к которой принадлежит данный продукт.

select 
p.ProductName, 
UnitPrice, t.mean, t.CategoryID from Products as p
inner join
(select avg(UnitPrice) as mean, CategoryID from Products
group by CategoryID) as t
on p.CategoryID = t.CategoryID

Здесь я смог сравнить UnitPrice с суммарным средним значением всех категорий, не исключая той, к которой относится данный Товар.

SELECT x.ProductName, AVG(x.UnitPrice) AS average
FROM Products x
GROUP BY x.CategoryID, x.ProductName
Having AVG(x.UnitPrice)> 
(select AVG(UnitPrice) from Products)

Желаемые результаты должны выглядеть так

введите здесь описание изображения

Спасибо.


sql sql-server average northwind having-clause
person Yaahtzeck    schedule 05.10.2020    source источник
comment
Вы по-прежнему должны предоставлять образцы данных и ожидаемые результаты, чтобы вопрос оставался самостоятельным. Я, например, не собираюсь получать копию базы данных «Борей», чтобы ответить на ваш вопрос.   -  person Dale K    schedule 06.10.2020

Ответы (1)


arrow_upward
1
arrow_downward

Вы можете сформулировать это с помощью бокового соединения:

select p.*, a.avg_unitprice
from products p
cross apply (
    select avg(p1.unitprice) avg_unitprice
    from products p1
    where p1.categoryid <> p.categoryid
) a
where p.unitprice > a.avg_unitprice

Это сравнивает цену за единицу каждого продукта со средней ценой за единицу всех продуктов в других категориях.

Если, с другой стороны, вам нужны продукты, цена которых выше всех средних значений других категорий, то not exists кажется более подходящим:

select p.*
from products p
where not exists (
    select 1
    from products p1
    where p1.categoryid <> p.categoryid
    group by p1.categoryid
    having avg(p1.unitprice) >= p.unitprice
)
person GMB    schedule 05.10.2020
comment
Спасибо, результаты выглядят почти идеально! Не хватает только Алисы Баранины. Обратите внимание, что UnitPrice для этого Продукта составляет 39,00, тогда как среднее значение для категории 6 (к которой принадлежит Алиса Баранина) составляет 54,01. Именно поэтому наш запрос в данный момент отфильтровывает эту запись, ведь задача состоит не в том, чтобы сравнить UnitPrice X со средним значением для категории, к которой принадлежит X, а в том, чтобы сравнить UnitPrice X со всеми остальными средними, кроме этого конкретного - person Yaahtzeck; 06.10.2020
comment
@Yaahtzeck: я добавил предложение where в подзапрос, чтобы справиться с этим. - person GMB; 06.10.2020