Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
Здравствуйте!
Такой простой вопрос по учебной задаче: нужно написать запрос, который возвращает самый крупный заказ для каждого из продавцов за определенный год, т.е. в результатах запроса должны быть выведены следующие колонки: колонка с именем и фамилией продавца, номер заказа и его стоимость.
Вот такой запрос, естественно, работает.

select tbl.Seller,max(tbl.Price) as MaxPrice from
(select empl.LastName+' '+empl.FirstName as Seller, ord.OrderID as Number, ordDet.UnitPrice as Price
from Employees empl inner join Orders ord on ord.EmployeeID=empl.EmployeeID
inner join [Order Details] ordDet on ordDet.OrderID=ord.OrderID 
where ord.OrderDate>CONVERT(DATETIME,'01-01-1997',101)and ord.OrderDate<CONVERT(DATETIME,'01-01-1998',101))tbl
group by tbl.Seller


Но как добавить в выходную таблицу ещё номер заказа?
23 июл 12, 15:39    [12903350]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
_ч_
Member

Откуда:
Сообщений: 1440
rzn,

попробуйте так:

declare @dStart datetime = '1997-01-01'
declare @dFinish datetime = '1998-01-01'


select empl.LastName, empl.FirstName, ord.OrderID as Number, MaxPrice from(
select EmployeeID, MAX(Price) as MaxPrice from Employees
inner join Orders ord on ord.EmployeeID=empl.EmployeeID
where ord.OrderDate>@dStart and ord.OrderDate<@dFinish
group by EmployeeID)Tbl
inner join Employees as empl empl.EmployeeID = Tbl.EmployeeID
inner join Orders as ord on ord.EmployeeID=Tbl.EmployeeID
inner join [Order Details] as ordDet on ordDet.OrderID=ord.OrderID and ordDet.UnitPrice = MaxPrice
23 июл 12, 15:58    [12903554]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
_ч_,

Спасибо, уже лучше. :)
Однако возникла ещё проблема - выводится по несколько строк на одного продавца с одинаковыми ценами, но разными номерами договоров. Как этого избежать?
23 июл 12, 16:32    [12903897]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
_ч_
Member

Откуда:
Сообщений: 1440
rzn,

тогда структуры таблиц в студию
23 июл 12, 17:00    [12904116]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
_ч_,

Таблицы из небезизвестной базы Northwind:

Employees
[EmployeeID],[LastName],[FirstName],[Title],[TitleOfCourtesy],[BirthDate],[HireDate],[Address],[City],[Region],[PostalCode],[Country]
,[HomePhone],[Extension],[Photo],[Notes],[ReportsTo],[PhotoPath]
Orders
[OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity]
,[ShipRegion],[ShipPostalCode],[ShipCountry]
Order Details
[OrderID],[ProductID],[UnitPrice],[Quantity],[Discount]
23 июл 12, 17:26    [12904289]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
_ч_,

Тут собственно дело в том, что максимальных заказов(на одинаковую сумму)может быть несколько. Как вывести только один?
23 июл 12, 17:43    [12904428]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
Sergei.Agalakov
Member

Откуда:
Сообщений: 575
Возьмите последний (или первый) по OrderID
23 июл 12, 18:23    [12904677]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
Sergei.Agalakov,

А по-подробнее нельзя развернуть?
23 июл 12, 18:28    [12904694]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
hallabud
Member

Откуда: Киев
Сообщений: 245
Где такие учебные задачи задают?

DECLARE @year SMALLINT = 1998;

-- CTE для подсчета общей стоимости заказа
WITH OrderCosts (OrderID, OrderCost)
AS
(SELECT OrderID
	, SUM(Quantity * UnitPrice * (1 - Discount)) AS OrderCost
FROM dbo.[Order Details]
GROUP BY OrderID)
,
-- CTE для подсчета рангов
Rankings (Orderid, OrderCost, OrderYear, Fullname, Ranking)
AS
(SELECT OC.OrderID
	, CAST(OC.OrderCost AS DECIMAL (10,2))
	, YEAR(O.OrderDate) AS OrderYear
	, E.FirstName + ' ' + E.LastName AS 'Fullname'
	, RANK() OVER(PARTITION BY E.FirstName + ' ' + E.LastName, YEAR(O.OrderDate) ORDER BY OC.OrderCost DESC) AS Ranking
FROM OrderCosts OC
	JOIN dbo.Orders AS O
	ON OC.OrderID = O.OrderID
	JOIN dbo.Employees AS E
	ON O.EmployeeID = E.EmployeeID)

-- вывод результата
SELECT Orderid
	, OrderCost
	, OrderYear
	, Fullname
FROM Rankings
WHERE Ranking = 1 AND OrderYear = @year;
23 июл 12, 18:35    [12904721]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
hallabud
Member

Откуда: Киев
Сообщений: 245
а если страшно, что может быть несколько одинаковых самых крупных заказов, то вместо функции RANK() можно поставить ROW_NUMBER()
23 июл 12, 18:45    [12904781]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
Bator
Member

Откуда: Порт пяти морей
Сообщений: 439
если ориентироваться на то, что написано в первом посте, то как-то так
select
  empl.LastName+' '+empl.FirstName as Seller, ord.OrderID as Number, ord.UnitPrice as Price
from
  Employees empl
  cross apply(select top 1
                    ord.OrderID, ordDet.UnitPrice 
                  from
                    Orders ord
                    inner join [Order Details] ordDet on ordDet.OrderID=ord.OrderID 
                  where ord.EmployeeID=empl.EmployeeID
                     and ord.OrderDate >='19970101' and ord.OrderDate < '19980101'
                   order by ordDet.UnitPrice desc) as ord


но чудится мне, что надо там всю сумму по заказу учитывать...
23 июл 12, 18:51    [12904802]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
hallabud
Member

Откуда: Киев
Сообщений: 245
Конечно надо. UnitPrice - это цена единицы товара, а не сумма заказа. А автор пишет, что ему надо вывести самый крупный заказ.
23 июл 12, 18:56    [12904827]     Ответить | Цитировать Сообщить модератору
 Re: Агреватные функции и группировка  [new]
rzn
Member

Откуда:
Сообщений: 12
Bator,
спасибо, то что нужно.
26 июл 12, 00:04    [12917027]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить