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

Откуда: Москва
Сообщений: 11
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4305.0 (X64) Apr 3 2014 17:55:58 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Есть некая маркетинговая акция в которой описаны варианты закупки.
Пример варианта:
В пакет должны входить не менее 3 уникальных [idProduct], из них обязательно 1 [idProduct] = 7.
Т.е. нужно получить цифру - максимальное количество определенного варианта закупки входящего в документ.

if object_id (N'tempdb..#testDoc') is not null drop table #testDoc;
create table #testDoc([idDoc] bigint, [idProduct] bigint, [qty] int);

insert into #testDoc([idDoc], [idProduct], [qty])
values	(15, 1, 6)
	,(15, 2, 4)
	,(15, 3, 8)
	,(15, 4, 9)
	,(15, 7, 15)
;
select * from #testDoc;


В нашем случае максимальным количеством будет ([idProduct] = 7 должна входить в каждый вариант)
idDocidProductqty
1578
1548
1538
1574
1514
1524
1571
1511
1541

В итоге нужно получить таблицу вида
idDocpromoQty
1513


Что-то ничего придумать не могу. Направьте в нужную сторону.
10 июл 14, 10:54    [16285108]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
BekrenevSS,

select a.idDoc, sum(a.qty) as promoQty
from #testDoc a
join #testDoc b on a.idDoc = b.idDoc
where b.idProduct = 7
goup by a.idDoc
having count( distinct a.idProduct) >=3 
10 июл 14, 11:10    [16285171]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
iap
Member

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

комбинаторика что ли?
(Количество сочетаний из COUNT(*) по 2)
+(Количество сочетаний из COUNT(*) по 3)
+...
+(Количество сочетаний из COUNT(*) по COUNT(*))
среди тех idDoc, у которых есть [idProduct]=7, но без [idProduct]=7?
BekrenevSS
нужно получить цифру
А если получится не цифра, а число?
10 июл 14, 11:11    [16285181]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
Онегин357
Member [заблокирован]

Откуда:
Сообщений: 267
gandjustas
BekrenevSS,

select a.idDoc, sum(a.qty) as promoQty
from #testDoc a
join #testDoc b on a.idDoc = b.idDoc
where b.idProduct = 7
goup by a.idDoc
having count( distinct a.idProduct) >=3 
в тестовых данных ТС, в одном idDoc - два раза есть idProduct=7,
и поэтому такой запрос "задвоит" количество
select a.idDoc, sum(a.qty) as promoQty
from #testDoc a
where exists(select 1 from #testDoc b where b.idProduct = 7 and b.idDoc=a.idDoc)
group by a.idDoc
having count( distinct a.idProduct) >=3 
10 июл 14, 11:28    [16285277]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
sqluserfromhell
Member

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

По условию вроде как 32 получается а не 13

with maxInDoc as (
	select idDoc, idProduct, max(qty) as maxQtyByProductInDoc from #testDoc
	group by iddoc, idProduct
)
select t.idDoc, SUM(t1.maxQtyByProductInDoc) as SumQty from maxInDoc t join maxInDoc t1 on t.idDoc = t1.idDoc and t.idProduct = 7 
group by t.idDoc
having count(*) >=3
10 июл 14, 11:52    [16285438]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
BekrenevSS
Member

Откуда: Москва
Сообщений: 11
gandjustas
BekrenevSS,

select a.idDoc, sum(a.qty) as promoQty
from #testDoc a
join #testDoc b on a.idDoc = b.idDoc
where b.idProduct = 7
goup by a.idDoc
having count( distinct a.idProduct) >=3 

Не, тестовый набор в таблице документов выглядит вот так
idDocidProductqty
15 1 6
15 2 4
15 3 8
15 4 9
15 7 15

Т.е. так как товаров с [idProduct] = 7 в документе 15, и этот [idProduct] = 7 должен входить в каждый пакет, то количество пакетов в документе уже никак не может быть больше 15. А у вас получилось 42.
10 июл 14, 11:57    [16285492]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
BekrenevSS
Member

Откуда: Москва
Сообщений: 11
iap
BekrenevSS,

комбинаторика что ли?

Возможно что-то из раздела "Перечислительной комбинаторики"...
10 июл 14, 11:59    [16285516]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
gandjustas
Member

Откуда:
Сообщений: 857
Блог
BekrenevSS
Т.е. так как товаров с [idProduct] = 7 в документе 15, и этот [idProduct] = 7 должен входить в каждый пакет, то количество пакетов в документе уже никак не может быть больше 15. А у вас получилось 42.


С трудом понимаю что вам нужно, попробую угадать:
select doc.idDoc, sum(doc.qty) as promoQty
from #testDoc doc
join (select a.idDoc
from #testDoc a
join #testDoc b on a.idDoc = b.idDoc
where b.idProduct = 7
goup by a.idDoc
having count( distinct a.idProduct) >=3) matchedDoc 
on doc.idDoc = matchedDoc.idDoc
where  doc.idProduct = 7
group by doc.idDoc
10 июл 14, 12:10    [16285625]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
Онегин357
Member [заблокирован]

Откуда:
Сообщений: 267
кажется я ТСа понял
сомневаюсь, что это можно сделать "красиво", даже с CTE,
а "итерационно" можно так
+
if object_id (N'tempdb..#testDoc') is not null drop table #testDoc;
create table #testDoc([idDoc] bigint, [idProduct] bigint, [qty] int);

insert into #testDoc([idDoc], [idProduct], [qty]) values	(15, 1, 6)
insert into #testDoc([idDoc], [idProduct], [qty]) values	(15, 2, 4)
insert into #testDoc([idDoc], [idProduct], [qty]) values	(15, 3, 8)
insert into #testDoc([idDoc], [idProduct], [qty]) values	(15, 4, 9)
insert into #testDoc([idDoc], [idProduct], [qty]) values	(15, 7, 15)
--insert into #testDoc([idDoc], [idProduct], [qty]) values	(16, 3, 3)
--insert into #testDoc([idDoc], [idProduct], [qty]) values	(16, 4, 9)
--insert into #testDoc([idDoc], [idProduct], [qty]) values	(16, 7, 15)
;
--select * from #testDoc;

-----------------------
declare @t table ([idDoc] bigint, [idProduct] bigint, [qty] int)
declare @min_qty table ([idDoc] bigint, [min_qty] int)
declare @result table ([idDoc] bigint, [idProduct] bigint, [qty] int)
declare @rc int

--
while 1=1
begin

	insert into @t ([idDoc], [idProduct], [qty] )
	select top 1 with ties [idDoc], [idProduct], [qty] 
	from #testDoc a
	where exists(select 1 from #testDoc b where b.[idProduct]=7 and b.[qty]>0 and b.[idDoc]=a.[idDoc])
	  and [idProduct]<>7
	  and [qty]>0
	order by (row_number()over(partition by [idDoc] order by [qty] desc)-1)/2
	
	--
	insert into @t ([idDoc], [idProduct], [qty] )
	select [idDoc], [idProduct], [qty]
	from #testDoc
	where [idProduct]=7

	--
	insert into @min_qty ([idDoc], [min_qty])
	select [idDoc], min(qty)
	from @t
	group by [idDoc]
	having count(*)=3

	--
	insert into @result ([idDoc], [idProduct], [qty] )
	select t.[idDoc], t.[idProduct], mq.[min_qty]
	from @t t inner join @min_qty mq on t.[idDoc]=mq.[idDoc]

	--
	if @@rowcount=0 break

	--
	update td
	set td.[qty]=td.[qty]-mq.[min_qty]
	from #testDoc td 
	inner join @t t on td.[idDoc]=t.[idDoc] and td.[idProduct]=t.[idProduct]
	inner join @min_qty mq on td.[idDoc]=mq.[idDoc]

	--
	delete from @t
	delete from @min_qty

end

--
select * from @result order by [idDoc], [qty] desc
--
select [idDoc], sum(qty) as promoQty 
from @result 
where [idProduct]=7
group by [idDoc]


idDoc                idProduct            qty
-------------------- -------------------- -----------
15                   4                    8
15                   3                    8
15                   7                    8
15                   1                    4
15                   2                    4
15                   7                    4
15                   1                    1
15                   4                    1
15                   7                    1

(9 row(s) affected)

idDoc                promoQty
-------------------- -----------
15                   13

(1 row(s) affected)
10 июл 14, 12:44    [16285954]     Ответить | Цитировать Сообщить модератору
 Re: Найти максимальное количество пакетов определенного наполнения в документе  [new]
BekrenevSS
Member

Откуда: Москва
Сообщений: 11
Онегин357,
Спасибо! Я тоже кроме цикла ничего не придумал.
На небольшом количестве тестовых данных выдает нужные результаты.
Буду пробовать прикручивать данную логику в работу.
11 июл 14, 13:27    [16291705]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить