SQL — подзапрос в агрегатной функции

Я использую базу данных Northwind, чтобы освежить свои навыки SQL, создавая более или менее сложные запросы. К сожалению, я не смог найти решение для моего последнего варианта использования: «Получите сумму пяти самых больших заказов для каждой категории в 1997 году».

Используемые таблицы:

Orders(OrderId, OrderDate)
Order Details(OrderId, ProductId, Quantity, UnitPrice)
Products(ProductId, CategoryId)
Categories(CategoryId, CategoryName)

Я пробовал следующий запрос

SELECT c.CategoryName, SUM(
  (SELECT TOP 5 od2.UnitPrice*od2.Quantity 
   FROM [Order Details] od2, Products p2
   WHERE od2.ProductID = p2.ProductID
   AND c.CategoryID = p2.CategoryID
   ORDER BY 1 DESC))
FROM [Order Details] od, Products p, Categories c, Orders o 
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName

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


sql sql-server-2012 subquery aggregate-functions northwind
person Thomas    schedule 01.05.2013    source источник
comment
Какую версию СУБД вы используете? Также ваш вопрос неверен, потому что заказ может относиться более чем к одной категории.   -  person Hamlet Hakobyan    schedule 01.05.2013
comment
Я использую MSSQL 2012. Если вы обратитесь к подзапросу, есть предложение WHERE — WHERE c.categoryID = p2.CategoryID — которое должно фильтровать только по одной категории.   -  person Thomas    schedule 01.05.2013

Ответы (4)


arrow_upward
32
arrow_downward

Подзапросы обычно не допускаются в агрегатных функциях. Вместо этого переместите совокупность внутри подзапроса. В этом случае вам понадобится дополнительный уровень подзапроса из-за top 5:

SELECT c.CategoryName,
  (select sum(val)
   from (SELECT TOP 5 od2.UnitPrice*od2.Quantity as val
         FROM [Order Details] od2, Products p2
         WHERE od2.ProductID = p2.ProductID
         AND c.CategoryID = p2.CategoryID
         ORDER BY 1 DESC
        ) t
  )
FROM [Order Details] od, Products p, Categories c, Orders o 
WHERE od.ProductID = p. ProductID
AND p.CategoryID = c.CategoryID
AND od.OrderID = o.OrderID
AND YEAR(o.OrderDate) = 1997
GROUP BY c.CategoryName, c.CategoryId
person Gordon Linoff    schedule 01.05.2013
comment
Спасибо, что сделал работу! Просто для информации... CategoryID также должен быть сгруппирован. Последняя строка должна выглядеть как GROUP BY c.CategoryName, c.CategoryID. Но спасибо за ваше время, этот запрос кажется мне действительно странным. Теперь нужно тщательно проанализировать... :) - person Thomas; 02.05.2013

arrow_upward
4
arrow_downward

Используйте CTE с ROW_NUMBER вместо чрезмерного подзапроса.

 ;WITH cte AS
 (
  SELECT c.CategoryName, od2.UnitPrice, od2.Quantity,
         ROW_NUMBER() OVER(PARTITION BY c.CategoryName ORDER BY od2.UnitPrice * od2.Quantity DESC) AS rn
  FROM [Order Details] od JOIN Products p ON od.ProductID = p.ProductID
                          JOIN Categories c ON p.CategoryID = c.CategoryID
                          JOIN Orders o ON od.OrderID = o.OrderID
  WHERE o.OrderDate >= DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101'), 0)
    AND o.OrderDate < DATEADD(YEAR, DATEDIFF(YEAR, 0, '19970101')+1, 0)
  )
  SELECT CategoryName, SUM(UnitPrice * Quantity) AS val
  FROM cte
  WHERE rn < 6
  GROUP BY CategoryName
person Aleksandr Fedorenko    schedule 01.05.2013

arrow_upward
3
arrow_downward

Это определенно проблема подзапроса. Здесь есть отличная статья (первоначально написанная для Access, но синтаксис идентичен), также orderdate = 1997 даст дату заказа на 1 января 1997 года - вам нужно datepart (year, orderdate) = 1997, как только вы получите (до пяти) строк, возвращенных для каждой категории, вы можете затем инкапсулировать строки вернулся и объединил их

person Ian P    schedule 01.05.2013
comment
Спасибо за ссылку. Вы правы, я отредактировал свой пост. Это должно быть YEAR(o.OrderDate) = '1997' - person Thomas; 01.05.2013
comment
Не могли бы вы привести пример инкапсуляции возвращаемых строк и их агрегирования? - person Thomas; 01.05.2013
comment
SELECT x.A, x.B, x.C, SUM(x.d) AS D FROM (Любой допустимый оператор выбора sql, содержащий столбцы a, b, c, d, e) x GROUP BY x.d - person Ian P; 01.05.2013
comment
Это также подзапрос, однако он очень простой, а первая пятерка, сгруппированная по подзапросу категории, представляет собой более сложное и мощное использование. Я не хотел запутать, используя одно и то же описательное слово. Для вас важнее осознавать всю мощь подзапросов. - person Ian P; 01.05.2013

arrow_upward
0
arrow_downward

Я столкнулся с очень похожей проблемой с подзапросом Access, где записи были отсортированы по дате. Когда я использовал агрегатную функцию «Последний», я обнаружил, что она прошла через все подзапросы и извлекла последнюю строку данных из таблицы Access, а не отсортированный запрос, как предполагалось. Хотя я мог бы переписать запрос, чтобы использовать агрегатную функцию в первом наборе скобок (как было предложено ранее), мне было проще сохранить результаты запроса в виде таблицы в базе данных, отсортированной в нужном мне порядке, а затем использовать «Последний " агрегатная функция для получения нужных мне значений. В будущем я запущу запрос на обновление, чтобы результаты оставались актуальными. Не эффективно, но эффективно.

person Peter Cleary    schedule 10.04.2015