Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Выборка по результатам группировки  [new]
скулье
Guest
Требуется получить список продаж и возвратов, при том продажи должны учитывать продавцов, склады и сами товары, а возвраты - только склады и товары, но продавцы могут быть любыми. При этом в выборку должны попасть только продавцы, продавшие товаров на определённую сумму, учитывая возвраты.

Пример:
Товар А со стоимостью 10 продан трижды одним продавцом с одного склада, и один раз был возвращён через другого продавца, сумма продаж до возврата удовлетворяла требования выборки, но после возврата перестала удовлетворять и в результатах ни одного из товаров, проданных этим продавцом в складе не должно быть видно, как и возвращённого другому продавцу товара.

declare @Sales table (SalePerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int);
declare @Refunds table (RefundPerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '002', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '003', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '002', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '003', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 20, 'item B', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '002', 20, 'item B', 1);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty) values ('abc', '003', -10, 'item A', 1);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '001', -10, 'item A', 1);

-- даже эта выборка не верна, потому что SalePerson меня реально интересует только для Sales, в Refunds может быть другой. А ещё в ней отсутствуют реальные количества товаров и цены:

select Person, WarehouseCode, Item from 
(
select SalePerson as Person, WarehouseCode, Amount, Item, Qty from @Sales
union all
select RefundPerson as Person, WarehouseCode, Amount, Item, Qty from @Refunds
) as X
group by Person, Item, WarehouseCode
having sum(Qty*Amount) >= 20


И не хотелось бы переусложнять запрос, потому что то, что здесь выглядит как @Sales и @Refunds на самом деле тоже немаленькие селекты. Что-то мне подсказывает, что должно быть какое-то изящное решение, но я его, увы, не вижу :(
22 авг 16, 13:55    [19573056]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
скулье,

Если я правильно понял, возврат должен быть вычтен из продаж каждого из продавших этот товар продавцов?

Такое не подходит?
select Saleperson,t.WarehouseCode,t.ITEM
from @Sales t left join @Refunds t1 on t.WarehouseCode=t1.WarehouseCode and t.Item=t1.Item
where t.amount*t.qty + coalesce(t1.amount*t1.qty,0)>=20
22 авг 16, 14:42    [19573364]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
Задача как то странно звучит...
В твоем примере ItemA продал со склада 003 и Bar и FOO, но был один возврат. Его нужно учесть у кого-то одного по идее, тогда первоначальная задача по выводу именно продавца, который продал на определенную сумму ItemA, решается.

автор
@Sales и @Refunds на самом деле тоже немаленькие селекты.

Может быть ты данные не правильно, исходя из условий задачи, подготовил?
22 авг 16, 15:07    [19573533]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
buven,

ОНО?
declare @Sales table (SalePerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int);
declare @Refunds table (RefundPerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '002', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '003', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '002', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '003', 10, 'item A', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '001', 20, 'item B', 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty) values ('bar', '002', 20, 'item B', 1);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty) values ('abc', '003', -10, 'item A', 1);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty) values ('Foo', '001', -10, 'item A', 1);

-- даже эта выборка не верна, потому что SalePerson меня реально интересует только для Sales, в Refunds может быть другой. А ещё в ней отсутствуют реальные количества товаров и цены:

SELECT * FROM 
(
	SELECT Person, A.WarehouseCode, A.Item, A.Amount - ISNULL(B.Amount, 0) AS Amount, A.Qty - ISNULL(B.Qty, 0) AS Qty FROM 
	(
		select Person, WarehouseCode, Item, SUM(Amount) AS Amount, SUM(Qty) AS Qty from 
		(
			select SalePerson as Person, WarehouseCode, Amount, Item, Qty from @Sales
		) as X
		group by Person, Item, WarehouseCode
	) A
	OUTER APPLY 
	(
		SELECT WarehouseCode, Item, Amount, Qty FROM 
		(
			select WarehouseCode, Item, SUM(Amount) AS Amount, SUM(Qty) AS Qty from 
			(
				select WarehouseCode, Amount, Item, Qty from @Refunds
			) as X
			group by Item, WarehouseCode
		) B
		WHERE B.Item = A.Item AND A.WarehouseCode = B.WarehouseCode
	) B
) C
WHERE (Qty*Amount) >= 20
22 авг 16, 15:14    [19573593]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
Но подозреваю, что для большей точности расчетов надо в возвратах иметь изначального продавца возвращаемого товара.
22 авг 16, 15:16    [19573609]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
a_voronin,

Если возвраты уже с минусом, то тогда вот так

A.Amount + ISNULL(B.Amount, 0) AS Amount, A.Qty + ISNULL(B.Qty, 0) AS Qty
22 авг 16, 15:17    [19573619]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

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

a_voronin
Но подозреваю, что для большей точности расчетов надо в возвратах иметь изначального продавца возвращаемого товара.


Совершенно точно не хватает ключа.

И у вас в запросе получается вот так:
Person WarehouseCode Item Amount Qty
bar 001 item A 20 4
bar 001 item B 20 1
bar 002 item B 20 1

Не бьется... )
22 авг 16, 15:44    [19573838]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
скулье
Guest
автор
В твоем примере ItemA продал со склада 003 и Bar и FOO, но был один возврат. Его нужно учесть у кого-то одного по идее, тогда первоначальная задача по выводу именно продавца, который продал на определенную сумму ItemA, решается.
Да, мой косяк. В оригинально таблице просто полно полей. Там есть ещё поле, позволяющее связать продажи и возвраты, я забыл его добавить. Пусть будет тогда вот так:

declare @Sales table (SalePerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int, SaleID int);
declare @Refunds table (RefundPerson varchar(100), WarehouseCode varchar(3), Amount float, Item varchar(50), Qty int, SaleID int);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '001', 10, 'item A', 1, 6);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '002', 10, 'item A', 1, 7);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '003', 10, 'item A', 1, 8);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '003', 20, 'item B', 1, 9);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('bar', '001', 10, 'item A', 1, 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('bar', '001', 10, 'item A', 1, 2);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '002', 10, 'item A', 1, 3);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('bar', '001', 10, 'item A', 1, 4);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('bar', '003', 10, 'item A', 1, 5);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('bar', '001', 20, 'item B', 1, 1);
insert into @Sales (SalePerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '002', 20, 'item B', 1, 3);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('abc', '003', -10, 'item A', 1, 8);
insert into @Refunds (RefundPerson, WarehouseCode, Amount, Item, Qty, SaleID) values ('Foo', '001', -10, 'item A', 1, 1);

---- это вся выборка, включая ненужные поля:

select * from (
select SalePerson as Person, WarehouseCode, Amount, Item, Qty, SaleID from @Sales
union all
select RefundPerson as Person, WarehouseCode, Amount, Item, Qty, SaleID from @Refunds
) as X order by WarehouseCode, SaleID, Person, Item


Результат предыдущей выборки, голубым подсвечены поля, которые должны присутствовать в результате (при том присутствовать "as is", без группировки и суммирования значений, но порядок роли не играет), с объяснениями почему так (не подсвеченные голубым поля присутствовать не должны), ограничение суммы в этом примере >=40

Картинка с другого сайта.
22 авг 16, 16:23    [19574227]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
скулье,

Пока в раздумьях мы далеко не ушли - возврат мажет быть частичным? Т.е. и SaleId=213 с QTY=5 возврат был на 3 штуки?
22 авг 16, 16:43    [19574386]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
скулье
Guest
автор
Пока в раздумьях мы далеко не ушли - возврат мажет быть частичным? Т.е. и SaleId=213 с QTY=5 возврат был на 3 штуки?
Нет, на текущий момент такой задачи нет. Да и вообще судя по тому, что я видел в БД, там всегда по одной штуке продажи. На всякий случай уж перестраховываюсь, просчитывая количество :)
22 авг 16, 16:58    [19574503]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
скулье
ограничение суммы в этом примере >=40
И почему срока №6 не попала в результат?
22 авг 16, 17:13    [19574593]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
скулье,

И мне кажется 3-ю строчку вы по ошибке выделили синим?
Там же not ok for Foo вроде как и не должны мы ее показывать.
22 авг 16, 17:16    [19574617]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
скулье
Guest
В строке 6 другой SalesPerson. А строка 3 попала в выборку потому что "Foo" - это RefundPerson, связанный по SaleID с продажей со строки 1 (Item A)
22 авг 16, 17:17    [19574624]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
Задача мягко говоря не реляционная имхо...
Если позволит версия сервера:
-- ищем продавца и склад на котором у него все получилось
with rec1 (Saleperson,WarehouseCode) as (
		select Saleperson,t.WarehouseCode
			from @Sales t 
				left join @Refunds t1 on t.WarehouseCode=t1.WarehouseCode 
									and  t.Item=t1.Item and t.SaleID=t1.SaleID
		where t.QTY - coalesce(t1.QTY,0) <> 0
		group by Saleperson,t.WarehouseCode
		having SUM((t.QTY - coalesce(t1.QTY,0)) * t.AMOUNT)>=40
),
-- ищем ID продаж, на которых он сделал план
rec2 (SaleId,WarehouseCode,Saleperson) as (
		select SaleID,t.WarehouseCode,t.Saleperson 
		from @sales t  join rec1 t1 
		on t.Saleperson=t1.Saleperson and t.WarehouseCode=t1.WarehouseCode
)
--приводим в нужный вид
select * from @Sales where SaleID in (select SaleID from rec2)
union
-- добавляем возвраты, которые не помешали сделать план
select * from @Refunds where SaleID in (select SaleID from rec2)


Не получилось изящно...
22 авг 16, 17:48    [19574898]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
Может пойти с самого начала?
Есть подозрение, что мы неверно поставили себе задачу.
Где данные потом используются? Какова изначальная структура БД?
22 авг 16, 18:00    [19574936]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
invm
Member

Откуда: Москва
Сообщений: 9913
with s as
(
 select
  *,
  sum(Amount) over (partition by WarehouseCode, SalePerson) as TotalAmount
 from
  (
   select SalePerson, WarehouseCode, Amount, Item, Qty, SaleID from @Sales
   union all
   select
    s.SalePerson, r.WarehouseCode, r.Amount, r.Item, r.Qty, r.SaleID
   from
    @Refunds r cross apply
    (select top (1) SalePerson from @Sales where WarehouseCode = WarehouseCode and SaleID = r.SaleID) s
  ) t
)
select
 SalePerson, WarehouseCode, Amount, Item, Qty, SaleID
from
 s
where
 TotalAmount >= 40
order by
 WarehouseCode, SalePerson;
22 авг 16, 18:12    [19574993]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
buven
Member

Откуда:
Сообщений: 792
invm,
Интересно, как себя поведут наши с вами запросы, как только мы добавим миллионов 10 продаж и пару миллионов возвратов :)
22 авг 16, 18:17    [19575010]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4974
buven
invm,
Интересно, как себя поведут наши с вами запросы, как только мы добавим миллионов 10 продаж и пару миллионов возвратов :)


При правильных индексах поведут себя нормально. А при колумсторах ещё лучше.
22 авг 16, 19:56    [19575335]     Ответить | Цитировать Сообщить модератору
 Re: Выборка по результатам группировки  [new]
скулье
Guest
Большое спасибо за наглядные примеры! Задача, собственно, такова: при преодолении некоторого порога продаж продавец должен быть поощрён, но дело в том, что если продать даже на очень большую сумму, но всё проданное вернут - выгоды никакой не будет, значит и поощрения тоже. К тому же возвраты товара можно производить и другому продавцу, не обязательно продавшему товар, для этого есть ID продажи, чтобы знать, какой продавец совершил продажу. Вот, собственно, и всё.
Ещё раз благодарю за помощь!
З.Ы. Да, проблем с производительностью не предвидится - все ключевые поля проиндексированы.
22 авг 16, 20:56    [19575575]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить