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

Откуда: Оттуда
Сообщений: 103
Доброе время суток, уважаемые.

Недавно я столкнулся с задачей и хочу попросить у вас помощи в ее решении:
Необходимо вывести все покупки клиента (сумма, дата, наименование) от самой поздней к самой ранней, пока его кредит больше нуля.
В последнюю строку необходимо вывести не сумму покупки, а остаток кредита.

Даны 2 таблицы:

@Table1 – клиенты (Id_Client – идентификатор клиента, Value – размер кредита)
@Table2 – покупки (Id_Client – идентификатор клиента, Amount – сумма покупки, DocDate – дата покупки, Caption - наименование)

Тестовые данные:

DECLARE @Table1 table (Id_Client int, Value money)

INSERT INTO @Table1 (Id_Client, Value)
SELECT 1, 24
UNION SELECT 2, 13
UNION SELECT 3, 2
UNION SELECT 4, 5

DECLARE @Table2 table (Id_Client int, DocDate datetime, Amount money, Caption varchar(6))

INSERT INTO @Table2 (Id_Client, Amount, DocDate, Caption)
SELECT 1, 5, '2005-10-24 00:00:00', 'qho'
UNION SELECT 1, 9, '2005-10-19 00:00:00', 'wjo'
UNION SELECT 1, 3, '2005-10-22 00:00:00', 'eko'
UNION SELECT 1, 8, '2005-10-04 00:00:00', 'rlo'
UNION SELECT 1, 6, '2005-10-18 00:00:00', 'tzo'
UNION SELECT 1, 5, '2005-09-29 00:00:00', 'yxo'
UNION SELECT 2, 11, '2005-10-23 00:00:00', 'uco'
UNION SELECT 2, 6, '2005-10-21 00:00:00', 'ivo'
UNION SELECT 2, 45, '2005-10-18 00:00:00', 'obo'
UNION SELECT 3, 4, '2005-10-30 00:00:00', 'pno'
UNION SELECT 3, 2, '2005-10-28 00:00:00', 'amo'
UNION SELECT 4, 4, '2005-10-21 00:00:00', 'sqo'
UNION SELECT 4, 6, '2005-10-23 00:00:00', 'dwo'
UNION SELECT 4, 8, '2005-10-23 00:00:00', 'feo'
UNION SELECT 4, 9, '2005-10-23 00:00:00', 'gro'


Саму задачу я решил с помощью курсоров и с помощью циклов. Но есть потребность решить ее без применения обоих этих средств.
Предполагаю, что думать надо в сторону CTE, но с ним я не работал в таком разрезе.
Подскажите, пожалуйста, можно ли ее решить без применения курсоров и циклов? И если да, то как?


P.s. на всякий случай, вот код, который делает подсчет через циклы:
declare @a money=0,@i int=0,@t int=1, @Value money,@Amount money
while @i<=(select MAX(Id_Client) from @Table1)
begin 
    set @i=@i+1
    set @Value=(select Value from @Table1 where Id_Client=@i)
    while @t<=(select COUNT(*) from @Table2 where Id_Client=@i)
    begin
      set @Amount=(select Amount from (select 
                                        Amount,
                                        ROW_NUMBER() over(order by DocDate desc) as RN 
                                       from @Table2 
                                       where Id_Client=@i) as t 
                  where RN=@t)
       if  @Value>@a+@Amount select Id_Client,DocDate,Amount,Caption from 
             (select 
                Id_Client, 
                Amount,
                ROW_NUMBER() over(order by DocDate desc) as RN,
                DocDate,
                Caption 
              from @Table2 
              where Id_Client=@i) as t 
           where RN=@t
       else begin
		select Id_Client,DocDate,(@Value-@a) as Amount,Caption from 
                  (select 
                     Id_Client, 
                     ROW_NUMBER() over(order by DocDate desc) as RN,
                     DocDate,
                     Caption 
                   from @Table2 
                   where Id_Client=@i) as t2 
                 where RN=@t
		break
       end
       set @a=@a+@Amount
       set @t=@t+1 
end
set @a=0
set @t=1      
end 
21 июн 13, 22:59    [14468277]     Ответить | Цитировать Сообщить модератору
 Re: Перебор данных без курсора и цикла  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
with cte as
(
 select
  *, row_number() over (partition by Id_Client order by DocDate, Amount) as rn
 from
  @Table2
)
select
 t2.Id_Client, t2.DocDate,
 case when t1.value - x.rt < 0 then t1.value - x.rt + t2.Amount else t2.Amount end as Amount,
 Caption
from
 cte t2 cross apply
 (select sum(Amount) from cte where Id_Client = t2.Id_Client and rn >= t2.rn) x(rt) join
 @Table1 t1 on t1.Id_Client = t2.Id_Client
where
 t1.value - x.rt + t2.Amount > 0
order by
 t2.Id_Client, t2.DocDate desc;
Только не факт, что это будет быстрее курсора...
22 июн 13, 00:09    [14468540]     Ответить | Цитировать Сообщить модератору
 Re: Перебор данных без курсора и цикла  [new]
aleks2
Guest
set dateformat ymd
DECLARE @Table1 table (Id_Client int, Value money) 

INSERT INTO @Table1 (Id_Client, Value) 
SELECT 1, 24 
UNION SELECT 2, 13 
UNION SELECT 3, 2 
UNION SELECT 4, 5 

DECLARE @Table2 table (Id_Client int, DocDate datetime, Amount money, Caption varchar(6)) 

INSERT INTO @Table2 (Id_Client, Amount, DocDate, Caption) 
SELECT 1, 5, '2005-10-24 00:00:00', 'qho' 
UNION SELECT 1, 9, '2005-10-19 00:00:00', 'wjo' 
UNION SELECT 1, 3, '2005-10-22 00:00:00', 'eko' 
UNION SELECT 1, 8, '2005-10-04 00:00:00', 'rlo' 
UNION SELECT 1, 6, '2005-10-18 00:00:00', 'tzo' 
UNION SELECT 1, 5, '2005-09-29 00:00:00', 'yxo' 
UNION SELECT 2, 11, '2005-10-23 00:00:00', 'uco' 
UNION SELECT 2, 6, '2005-10-21 00:00:00', 'ivo' 
UNION SELECT 2, 45, '2005-10-18 00:00:00', 'obo' 
UNION SELECT 3, 4, '2005-10-30 00:00:00', 'pno' 
UNION SELECT 3, 2, '2005-10-28 00:00:00', 'amo' 
UNION SELECT 4, 4, '2005-10-21 00:00:00', 'sqo' 
UNION SELECT 4, 6, '2005-10-23 00:00:00', 'dwo' 
UNION SELECT 4, 8, '2005-10-23 00:00:00', 'feo' 
UNION SELECT 4, 9, '2005-10-23 00:00:00', 'gro' 


;with
-- накопительные значения покупок
sums as(
  select t1.Id_Client, t1.Amount, t1.DocDate, t1.Caption, sum(t2.Amount) as TotalAmount
    from @Table2 t1 inner join @Table2 t2 on t1.Id_Client = t2.Id_Client and t1.DocDate >= t2.DocDate
  group by t1.Id_Client, t1.Amount, t1.DocDate, t1.Caption
)

-- покупки до исчерпания кредита
select s.Id_Client, s.Amount, s.DocDate, s.Caption,  t1.Value, 0 as ord
  from @Table1 t1 inner join sums s  on s.Id_Client = t1.Id_Client and s.DocDate <= (select max(s1.DocDate) from sums s1 where s1.Id_Client = t1.Id_Client and s1.TotalAmount <= t1.Value)
union all
-- остаток кредита
select t1.Id_Client, t1.Value-isnull(s.Amount, 0) as Amount, s.DocDate, 'остаток кредита',  t1.Value, 1 as ord
  from @Table1 t1 left outer join sums s on s.Id_Client = t1.Id_Client and s.DocDate = (select max(s1.DocDate) from sums s1 where s1.Id_Client = t1.Id_Client and s1.TotalAmount <= t1.Value)
order by s.Id_Client, s.DocDate, ord
22 июн 13, 06:38    [14468926]     Ответить | Цитировать Сообщить модератору
 Re: Перебор данных без курсора и цикла  [new]
Baccardi
Member

Откуда: Оттуда
Сообщений: 103
invm, aleks2, спасибо вам за помощь!
Судя по планам выполнения - через СТЕ получается ощутимо быстрее, чем через курсор. Но вот по сравнению с циклом прироста пока не заметил...
Еще раз благодарю за науку, посижу, по-разбираюсь.
22 июн 13, 09:03    [14468959]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить