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

Откуда:
Сообщений: 5
Приветуськи!

Люди добрые, помогите кто чем сможет..
Есть код для расчёта ПСК. Но работает он категорически медленно. Переписал его с делфи, там он ворочается намного быстрее.
50 сек в базе считает и <1 сек в делфи.
Улучшал как мог, максимум получилось ускорить до 21 сек, при помощи хранения циферок в varchar(max) и распарсиванием их.
Возможно я чего-то не учёл и можно как-то лучше написать код. В данный момент пытаюсь составить рекурсивный код на чистом sql, но красный текст Функции GROUP BY, HAVING и агрегатные функции не разрешены в рекурсивной части рекурсивного обобщенного табличного выражения "func". мешает пока что.

Изначальный код выжатый из Delphi:
+
SET NOCOUNT ON

declare @tbl table (N int, Summa money, e float, q int);

declare @s numeric(15,9) = 0.000001
       ,@x_m numeric(15,9) = 0
       ,@x numeric(15,9) = 1
       ,@i numeric(15,9) = 0
       ,@k int = 0
       ,@u int = 0

       ,@start int = 0
       
       ,@tbl_summa numeric(15,9) = 0
       ,@tbl_e numeric(15,9) = 0
       ,@tbl_q numeric(15,9) = 0

       ,@str varchar(max) = ''
       ,@result numeric(15,9) = 0;
       

insert into @tbl
select 0 as N, -215000.00 as Summa, 0 as e, 0 as q union
select 1, 5513.42, 0.433333, 0 union
select 2, 14705.00, 0.466667, 1 union
select 3, 14705.00, 0.466667, 2 union
select 4, 14705.00, 0.5, 3 union
select 5, 14705.00, 0.5, 4 union
select 6, 14705.00, 0.533333, 5 union
select 7, 14705.00, 0.566667, 6 union
select 8, 14705.00, 0.533333, 7 union
select 9, 14705.00, 0.566667, 8 union
select 10, 14705.00, 0.566667, 9 union
select 11, 14705.00, 0.6, 10 union
select 12, 14705.00, 0.6, 11 union
select 13, 14705.00, 0.633333, 12 union
select 14, 14705.00, 0.666667, 13 union
select 15, 14705.00, 0.666667, 14 union
select 16, 14705.00, 0.7, 15 union
select 17, 14705.00, 0.7, 16 union
select 18, 14705.00, 0.733333, 17 union
select 19, 14705.00, 0.766667, 18 union
select 20, 14705.00, 0.7, 19 union
select 21, 14705.00, 0.733333, 20 union
select 22, 14705.00, 0.733333, 21 union
select 23, 14705.00, 0.766667, 22 union
select 24, 14705.00, 0.766667, 23 union
select 25, 14705.00, 0.8, 24 union
select 26, 14705.00, 0.833333, 25 union
select 27, 14705.00, 0.833333, 26 union
select 28, 14705.00, 0.866667, 27 union
select 29, 14705.00, 0.866667, 28 union
select 30, 14705.00, 0.9, 29 union
select 31, 14705.00, 0.933333, 30 union
select 32, 14705.00, 0.866667, 31 union
select 33, 14705.00, 0.9, 32 union
select 34, 14705.00, 0.9, 33 union
select 35, 14705.00, 0.933333, 34 union
select 36, 14705.00, 0.933333, 35 union
select 37, 15885.80, 0.966667, 36;



select @u = count(*)-1 from @tbl;

while @x > 0
begin
   set @x_m = @x;
   set @x = 0;
   set @k = 0;

   while @k <= @u
   begin
      -- долгий кусок
      select @x = @x + t.Summa / ((1 + t.e * @i) * (POWER((1 + @i), t.q)))
        from @tbl t where t.N = @k;
      -- долгий кусок
      
      set @k = @k + 1;
   end;

   set @i = @i + @s;
end;

if @x > @x_m
   set @i = @i - @s;

set @result = Round(@i * 12 * 100, 3);

SET NOCOUNT OFF

print 'result=' + cast(@result as varchar(max))


Код проапргрейденный для добычи данных из varchar(max):
declare @tbl table (N int, Summa money, e float, q int);


insert into @tbl
select 0 as N, -215000.00 as Summa, 0 as e, 0 as q union
select 1, 5513.42, 0.433333, 0 union
select 2, 14705.00, 0.466667, 1 union
select 3, 14705.00, 0.466667, 2 union
select 4, 14705.00, 0.5, 3 union
select 5, 14705.00, 0.5, 4 union
select 6, 14705.00, 0.533333, 5 union
select 7, 14705.00, 0.566667, 6 union
select 8, 14705.00, 0.533333, 7 union
select 9, 14705.00, 0.566667, 8 union
select 10, 14705.00, 0.566667, 9 union
select 11, 14705.00, 0.6, 10 union
select 12, 14705.00, 0.6, 11 union
select 13, 14705.00, 0.633333, 12 union
select 14, 14705.00, 0.666667, 13 union
select 15, 14705.00, 0.666667, 14 union
select 16, 14705.00, 0.7, 15 union
select 17, 14705.00, 0.7, 16 union
select 18, 14705.00, 0.733333, 17 union
select 19, 14705.00, 0.766667, 18 union
select 20, 14705.00, 0.7, 19 union
select 21, 14705.00, 0.733333, 20 union
select 22, 14705.00, 0.733333, 21 union
select 23, 14705.00, 0.766667, 22 union
select 24, 14705.00, 0.766667, 23 union
select 25, 14705.00, 0.8, 24 union
select 26, 14705.00, 0.833333, 25 union
select 27, 14705.00, 0.833333, 26 union
select 28, 14705.00, 0.866667, 27 union
select 29, 14705.00, 0.866667, 28 union
select 30, 14705.00, 0.9, 29 union
select 31, 14705.00, 0.933333, 30 union
select 32, 14705.00, 0.866667, 31 union
select 33, 14705.00, 0.9, 32 union
select 34, 14705.00, 0.9, 33 union
select 35, 14705.00, 0.933333, 34 union
select 36, 14705.00, 0.933333, 35 union
select 37, 15885.80, 0.966667, 36
;


declare @s numeric(15,9) = 0.000001
       ,@x_m numeric(15,9) = 0
       ,@x numeric(15,9) = 1
       ,@i numeric(15,9) = 0
       ,@k int = 0
       ,@u int = 0

       ,@start int = 0
       
       ,@tbl_summa numeric(15,9) = 0
       ,@tbl_e numeric(15,9) = 0
       ,@tbl_q numeric(15,9) = 0
       
       ,@str varchar(max) = ''

       ,@str_s varchar(max) = ''
       ,@str_e varchar(max) = ''
       ,@str_q varchar(max) = ''
       ,@result numeric(15,9) = 0;
       

select @u = count(*)-1 from @tbl;


select @str_s = @str_s + dbo.LPAD(cast(t.Summa as numeric(10,4)), ' ', 15) + char(13) + char(10)
      ,@str_e = @str_e + dbo.LPAD(cast(t.e as numeric(15,9)), ' ', 15) + char(13) + char(10)
      ,@str_q = @str_q + dbo.LPAD(cast(t.q as numeric(3,0)), ' ', 15) + char(13) + char(10)
  from @tbl t;


while @x > 0
begin
   set @x_m = @x;
   set @x = 0;
   set @k = 0;

   while @k <= @u
   begin
      set @start = @k * 17

      set @tbl_summa = cast(substring(@str_s, @start + 1, 15) as numeric(10,4));
      set @tbl_e = cast(substring(@str_e, @start + 1, 15) as numeric(15,9));
      set @tbl_q = cast(substring(@str_q, @start + 1, 15) as numeric(3,0));

      set @x = @x + @tbl_summa / ((1 + @tbl_e * @i) * (POWER((1 + @i), @tbl_q)));

      set @k = @k + 1;
   end;

   set @i = @i + @s;
end;

if @x > @x_m
   set @i = @i - @s;

set @result = Round(@i * 12 * 100, 3);

print 'result=' + cast(@result as varchar(max))


Функция LPAD:
CREATE  FUNCTION [dbo].[LPAD] (@Str varchar(max), @SubStr varchar(max), @Len int)
RETURNS varchar(max)
AS
BEGIN
   declare @Result varchar(max);

   set @Result = right(replicate(@SubStr, @Len) + @Str, @Len);

   RETURN @Result;
END;


В общем, желательно скорость выполнения улучшить до 5 сек в идеале 1 сек. 3й день оптимизирую, хз что ещё можно придумать...

Сообщение было отредактировано: 15 авг 19, 11:17
15 авг 19, 09:04    [21949792]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт Полной Стоимости Кредита (ПСК)  [new]
Massa52
Member

Откуда:
Сообщений: 379
ora0ra,
Замени
   while @k <= @u
   begin
      -- долгий кусок
      select @x = @x + t.Summa / ((1 + t.e * @i) * (POWER((1 + @i), t.q)))
        from @tbl t where t.N = @k;
      -- долгий кусок
      
      set @k = @k + 1;
   end;

на

	select @x = SUM(t.Summa / ((1 + t.e * @i) * (POWER((1 + @i), t.q))))
	  from @tbl t 
15 авг 19, 09:27    [21949808]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт Полной Стоимости Кредита (ПСК)  [new]
Minamoto
Member

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

а) общий расчет у вас неправильный
Правило: "Процентная ставка базового периода определяется как наименьшее положительное решение уравнения:"
А у вас получается даже больше наибольшего отрицательного.
Смотрите:
SELECT SUM(t.Summa / ((1 + t.e * 0.059082000) * (POWER((CAST(1 AS bigint) + 0.059082000), t.q)))) from @tbl t
SELECT SUM(t.Summa / ((1 + t.e * 0.059083000) * (POWER((CAST(1 AS bigint) + 0.059083000), t.q)))) from @tbl t
SELECT SUM(t.Summa / ((1 + t.e * 0.059084000) * (POWER((CAST(1 AS bigint) + 0.059084000), t.q)))) from @tbl t

Т.е. правильный ответ - 0.059082000, а у вас получается 0.059084000.

б) вы очень маленькими шагами двигаетесь к решению. Каждый раз прибавляя по 0.000001, вы получаете 59 тысяч шагов. Нужно сначала делать крупное приближение, потом увеличивать точность.
Можно использовать метод половинного деления, я сделал через десятичный множитель - при каждом приближении увеличиваем точность в 10 раз:

declare @x numeric(15,9) = 1
       ,@i numeric(15,9) = 0
       ,@i_next numeric(15,9)
       ,@step int = 0;

IF (SELECT max(q) FROM @tbl AS t) > 95 SET @step = 1;

while @step <= 6
begin
   
   SET @i_next = @i + power(0.100000, @step);
   
   select @x = SUM(t.Summa / ((1 + t.e * @i_next) * (POWER((CAST(1 AS bigint) + @i_next), t.q))))
	  from @tbl t
      
    PRINT '@x: ' + CAST(@x AS varchar(max)) + ', @i: ' + CAST(@i AS varchar(max)) + ', @i_next: ' + CAST(@i_next AS varchar(max));
    
    IF @x < 0
        SET @step = @step + 1; 
    ELSE 
        SET @i = @i_next;
    
end;

print 'result=' + cast(Round(@i * 12 * 100, 3) as varchar(max))
15 авг 19, 11:21    [21949902]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт Полной Стоимости Кредита (ПСК)  [new]
ora0ra
Member

Откуда:
Сообщений: 5
Massa52, Благодарствую! 4 сек, хорош. Да похоже я просмотрел этот кусок...
15 авг 19, 13:31    [21950062]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт Полной Стоимости Кредита (ПСК)  [new]
ora0ra
Member

Откуда:
Сообщений: 5
Minamoto, Благодарю! Алгоритм воообще бомба! Ты гений! По шагам увеличивая точность.. просто мозг!!!
15 авг 19, 13:35    [21950065]     Ответить | Цитировать Сообщить модератору
 Re: Расчёт Полной Стоимости Кредита (ПСК)  [new]
nullin
Member

Откуда: pullin
Сообщений: 174
ora0ra
Функции GROUP BY, HAVING и агрегатные функции не разрешены в рекурсивной части рекурсивного обобщенного табличного выражения

Можно оконные, а потом фильтрануть по row_number() over(order by (select null))=1. Единственное, агрегат sum() без group by в случае пустой таблицы себя иначе ведет, чем с ним.
16 авг 19, 03:42    [21950638]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить