Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт Полной Стоимости Кредита (ПСК) / 6 сообщений из 6, страница 1 из 1
15.08.2019, 09:04
    #39849585
ora0ra
ora0ra
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
Приветуськи!

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

Изначальный код выжатый из Delphi:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
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):
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
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:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
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й день оптимизирую, хз что ещё можно придумать...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2019, 09:27
    #39849596
Massa52
Massa52
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
ora0ra,
Замени
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
   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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2019, 11:21
    #39849651
Minamoto
Minamoto
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
ora0ra,

а) общий расчет у вас неправильный
Правило: "Процентная ставка базового периода определяется как наименьшее положительное решение уравнения:"
А у вас получается даже больше наибольшего отрицательного.
Смотрите:
Код: sql
1.
2.
3.
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 раз:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
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))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2019, 13:31
    #39849750
ora0ra
ora0ra
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
Massa52, Благодарствую! 4 сек, хорош. Да похоже я просмотрел этот кусок...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
15.08.2019, 13:35
    #39849753
ora0ra
ora0ra
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
Minamoto, Благодарю! Алгоритм воообще бомба! Ты гений! По шагам увеличивая точность.. просто мозг!!!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
16.08.2019, 03:42
    #39850103
nullin
nullin
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Расчёт Полной Стоимости Кредита (ПСК)
ora0raФункции GROUP BY, HAVING и агрегатные функции не разрешены в рекурсивной части рекурсивного обобщенного табличного выражения
Можно оконные, а потом фильтрануть по row_number() over(order by (select null))=1. Единственное, агрегат sum() без group by в случае пустой таблицы себя иначе ведет, чем с ним.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт Полной Стоимости Кредита (ПСК) / 6 сообщений из 6, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]