ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт Полной Стоимости Кредита (ПСК)
6 сообщений из 6, страница 1 из 1
Расчёт Полной Стоимости Кредита (ПСК)
    #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:04
| Ответить | Цитировать | Написать  
Расчёт Полной Стоимости Кредита (ПСК)
    #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, 09:27
| Ответить | Цитировать | Написать  
Расчёт Полной Стоимости Кредита (ПСК)
    #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, 11:21
| Ответить | Цитировать | Написать  
Расчёт Полной Стоимости Кредита (ПСК)
    #39849750
ora0ra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora0ra
Гость
Massa52, Благодарствую! 4 сек, хорош. Да похоже я просмотрел этот кусок...
...
Рейтинг: 0 / 0
15.08.2019, 13:31
| Ответить | Цитировать | Написать  
Расчёт Полной Стоимости Кредита (ПСК)
    #39849753
ora0ra
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ora0ra
Гость
Minamoto, Благодарю! Алгоритм воообще бомба! Ты гений! По шагам увеличивая точность.. просто мозг!!!
...
Рейтинг: 0 / 0
15.08.2019, 13:35
| Ответить | Цитировать | Написать  
Расчёт Полной Стоимости Кредита (ПСК)
    #39850103
nullin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
nullin
Гость
ora0raФункции GROUP BY, HAVING и агрегатные функции не разрешены в рекурсивной части рекурсивного обобщенного табличного выражения
Можно оконные, а потом фильтрануть по row_number() over(order by (select null))=1. Единственное, агрегат sum() без group by в случае пустой таблицы себя иначе ведет, чем с ним.
...
Рейтинг: 0 / 0
16.08.2019, 03:42
| Ответить | Цитировать | Написать  
6 сообщений из 6, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Расчёт Полной Стоимости Кредита (ПСК)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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