Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		Продано 15 граблей и 5 лопат из пяти партий. 6 граблей и одна лопата волевым решением руководства были отнесены к неликвидам. Как вывести данные о продажах и неликвидах в разрезе партий, считая неликвидными партии с более ранним приходом? 
create table sales (good char(10),deldate date, qty int, cost money,price money);
insert sales values ('rake', '01/01/01',5,100,110),('rake', '02/01/01',3,105,110),('rake', '03/01/01',7,102,110),
('spade', '04/01/01',2,10,15),('spade', '05/01/01',3,12,15);
create table surplus (good char(10), qty int);
insert surplus values ('rake',6),('spade',1);

sales
good		deldate		qty	cost	 price
rake      	2001-01-01 	5	100,00	110,00
rake      	2001-02-01 	3	105,00	110,00
rake      	2001-03-01 	7	102,00	110,00
spade     	2001-04-01 	2	10,00 	15,00
spade     	2001-05-01	3	12,00 	15,00

surplus
good		qty
rake      	6
spade     	1

В результате должно получиться:
good		deldate		qty	cost	 price	 is_surplus
rake      	2001-01-01	 5	100,00	110,00	1
rake      	2001-02-01	 1	105,00	110,00	1
rake      	2001-02-01	 2	105,00	110,00	0
rake      	2001-03-01	 7	102,00	110,00	0
spade     	2001-04-01	 1	10,00 	15,00	1
spade     	2001-04-01	 1	10,00 	15,00	0
spade     	2001-05-01	 3	12,00 	15,00	0
14 май 13, 13:49    [14292589]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
 		butthead,

if object_id('tempdb..#t') is not null
  drop table #t

select s1.*
     , isnull( sum( s2.qty ), 0 ) + 1      as begQ
     , isnull( sum( s2.qty ), 0 ) + s1.qty as endQ
  into #t
  from sales s1
    left join sales s2 on s1.good = s2.good and  s1.deldate > s2.deldate
  group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price

select t.good, t.deldate, t.qty, t.cost, t.price, 1 as is_surplus
  from #t t
    inner join surplus s on t.good = s.good
    where s.qty >= t.endQ
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 as is_surplus
  from #t t
    inner join surplus s on t.good = s.good
    where s.qty <  t.begQ
union all
select t.good, t.deldate, s.qty - t.begQ + 1, t.cost, t.price, 1 as is_surplus
  from #t t
    inner join surplus s on t.good = s.good
  where s.qty >= t.begQ
    and s.qty <  t.endQ
union all
select t.good, t.deldate, t.endQ - s.qty, t.cost, t.price, 0 as is_surplus
  from #t t
    inner join surplus s on t.good = s.good
  where s.qty >= t.begQ
    and s.qty <  t.endQ
order by good, deldate, is_surplus desc
14 май 13, 15:00    [14293246]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		hoha_ftf,

Спасибо огромное! Но это как-то слишком...
14 май 13, 15:05    [14293283]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
Remind
Member

Откуда: UK
Сообщений: 523
 		Слишком это торговать неликвидом :)
14 май 13, 17:48    [14294692]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		Remind,

Слишком - это работать продавцом в компании, где система мотивации меняется ежемесячно и бонусы, и немаленькие штрафы зависят от "волевых решений" задним числом.. Но не все герои еще разбежались.. Посчитать зп по бумажке манагеры давно не могут, многие параметры берутся без возможности проверки по данным, выданным системой. И для программистов лучше, чтобы система считала правильно, дабы не быть побитыми..
14 май 13, 18:17    [14294923]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		А через табличную переменную хуже будет? Может через CTE эффективнее можно? Это будет или часть составного запроса, но вероятно лучше будет вынести как отдельный view. В табличке sales около миллиона строк, surplus пара тысяч строк. 
declare @t table(good char(10),deldate date, qty int, cost money,price money, 
begQ int,endQ int)
insert @t
select s1.*
     , isnull( sum( s2.qty ), 0 ) + 1      as begQ
     , isnull( sum( s2.qty ), 0 ) + s1.qty as endQ
  
  from sales s1
    left join sales s2 on s1.good = s2.good and  s1.deldate > s2.deldate
  group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price

select t.good, t.deldate, t.qty, t.cost, t.price, 1 as is_surplus
  from @t t
    inner join surplus s on t.good = s.good
    where s.qty >= t.endQ
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 as is_surplus
  from @t t
    inner join surplus s on t.good = s.good
    where s.qty <  t.begQ
union all
select t.good, t.deldate, s.qty - t.begQ + 1, t.cost, t.price, 1 as is_surplus
  from @t t
    inner join surplus s on t.good = s.good
  where s.qty >= t.begQ
    and s.qty <  t.endQ
union all
select t.good, t.deldate, t.endQ - s.qty, t.cost, t.price, 0 as is_surplus
  from @t t
    inner join surplus s on t.good = s.good
  where s.qty >= t.begQ
    and s.qty <  t.endQ
order by good, deldate, is_surplus desc
14 май 13, 22:37    [14295911]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
 		А вообще самое лучшее (для легкости запросов) - хранить begQ и endQ в таблице Sales. Один раз посчитать для существующих данных, а для новых считать при вставке.
15 май 13, 08:13    [14296709]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
hoha_ftf
Member

Откуда: ЕКБ
Сообщений: 42
 		Точнее даже одно поле begQ
15 май 13, 08:15    [14296714]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		Инсерты убрал, стало еще страшнее..
Хочу попробовать обойтись пока без добавления полей в таблицу.

select t.good,t.deldate,ss.qty,1 as is_surplus
 from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty) t,surplus s,sales ss
 where t.good = s.good and s.qty >= t.endQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,ss.qty,0 as is_surplus
 from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty) t,surplus s,sales ss 
 where t.good = s.good and s.qty < t.begQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,s.qty - t.begQ + 1,1 as is_surplus
 from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty) t,surplus s
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
union all
select t.good,t.deldate,t.endQ - s.qty,0 as is_surplus
 from (select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty) t,surplus s 
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
order by good,deldate,is_surplus desc
15 май 13, 12:55    [14298542]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		через with
with t(good,deldate,begQ,endQ)as
(select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty)
select t.good,t.deldate,ss.qty,1 as is_surplus
 from t,surplus s,sales ss
 where t.good = s.good and s.qty >= t.endQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,ss.qty,0 as is_surplus
 from t,surplus s,sales ss 
 where t.good = s.good and s.qty < t.begQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,s.qty - t.begQ + 1,1 as is_surplus
 from t,surplus s
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
union all
select t.good,t.deldate,t.endQ - s.qty,0 as is_surplus
 from t,surplus s 
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
order by good,deldate,is_surplus desc
15 май 13, 13:27    [14298834]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
aleks2
Guest
butthead
через with
with t(good,deldate,begQ,endQ)as
(select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty)
select t.good,t.deldate,ss.qty,1 as is_surplus
 from t,surplus s,sales ss
 where t.good = s.good and s.qty >= t.endQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,ss.qty,0 as is_surplus
 from t,surplus s,sales ss 
 where t.good = s.good and s.qty < t.begQ and ss.good=t.good and ss.deldate=t.deldate
union all
select t.good,t.deldate,s.qty - t.begQ + 1,1 as is_surplus
 from t,surplus s
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
union all
select t.good,t.deldate,t.endQ - s.qty,0 as is_surplus
 from t,surplus s 
 where s.qty >= t.begQ and s.qty < t.endQ and t.good = s.good
order by good,deldate,is_surplus desc


butthead
В табличке sales около миллиона строк, surplus пара тысяч строк.


И ты мечтаешь, шоб 
(select s1.good,s1.deldate,isnull( sum( s2.qty ),0 ) + 1 as begQ,isnull( sum( s2.qty ),0 ) + s1.qty as endQ 
 from sales s1 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good,s1.deldate,s1.qty)

выполнилось 4 (четыре) раза?

Лучше освой написание JOIN-ов.
15 май 13, 13:34    [14298915]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		Реализовал пока через табличную переменную, близко к вот этому. Добавил еще один union на случай, когда товар отсутствует в таблице неликвидов.
declare @t table(good char(10),deldate date, qty int, cost money,price money, begQ int)
insert @t
select s1.* , isnull( sum( s2.qty ), 0 ) begQ
 from sales s1
 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price
select t.good, t.deldate, t.qty, t.cost, t.price, 1 is_surplus
 from @t t,surplus s
 where s.qty >= t.begQ+t.qty and t.good = s.good
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus
 from @t t ,surplus s 
 where t.good = s.good and s.qty < t.begQ 
union all
select t.good, t.deldate, s.qty - t.begQ, t.cost, t.price, 1 is_surplus
 from @t t,surplus s 
 where s.qty > t.begQ and s.qty < t.begQ+t.qty and t.good = s.good
union all
select t.good, t.deldate, t.begQ+t.qty - s.qty, t.cost, t.price, 0 is_surplus
 from @t t,surplus s 
 where s.qty >= t.begQ and t.good = s.good and s.qty < t.begQ+t.qty
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus
 from @t t
 where t.good not in (select good from surplus)
order by good, deldate, is_surplus desc
15 май 13, 23:23    [14302446]     Ответить | Цитировать Сообщить модератору
 Re: Объединить и поделить.. Помогите пожалуйста  [new]
butthead
Guest 		На реальных данных with у меня в 1,5-2 раза быстрее работает.
with t(good,deldate,qty,cost,price,begQ)as(
select s1.*, sum(isnull(s2.qty,0)) begQ
 from sales s1
 left join sales s2 on s1.good = s2.good and s1.deldate > s2.deldate
 group by s1.good, s1.deldate, s1.qty, s1.cost, s1.price)

select t.good, t.deldate, t.qty, t.cost, t.price, 1 is_surplus
 from t,surplus s
 where s.qty >= t.begQ+t.qty and t.good = s.good
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus
 from t ,surplus s 
 where t.good = s.good and s.qty < t.begQ 
union all
select t.good, t.deldate, s.qty - t.begQ, t.cost, t.price, 1 is_surplus
 from t,surplus s 
 where s.qty > t.begQ and s.qty < t.begQ+t.qty and t.good = s.good
union all
select t.good, t.deldate, t.begQ+t.qty - s.qty, t.cost, t.price, 0 is_surplus
 from t,surplus s 
 where s.qty >= t.begQ and t.good = s.good and s.qty < t.begQ+t.qty
union all
select t.good, t.deldate, t.qty, t.cost, t.price, 0 is_surplus
 from t
 where t.good not in (select good from surplus)
order by good, deldate, is_surplus desc
16 май 13, 11:55    [14304370]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить