ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вместо циклов
20 сообщений из 20, страница 1 из 1
sql вместо циклов
    #39938530
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
Друзья, подскажите пожалуйста как оптимизировать такую задачу. Есть таблица с полем VARCHAR2 и двумя полями типа VARRAY. В первом поле храниться имя, во втором - 512 исходных значений, в третье поле записываю результат функции. Функция делает преобразование Фурье и возвращает массив VARRAY. Такой подсчет идет по каждой строке. Строк очень много. Все работает и считает, но по причине использования циклов все это работает очень медленно.
Подскажите пожалуйста, возможно ли решить эту задачу SQLем (аналитическими функциями, конвеерными функциями)? В каком направлении копать?

Вот пример данных

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
NAM VAL         поле с результатом
A	1                    ?
	4                    ?
	8
	7
	9
	3
B	7
	4
	1
	2
	3
	9
......


В принципе я развернул данные без массива (varchar2 и number) и попробовал так, но опыта не хватает построить алгоритм подсчета

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
NAM VAL     поле с результатом
A	1              ?
A	4              ?
A	8
A	7
A	9
A	3
B	7
B	4
B	1
B	2
B	3
B	9



в функцию передаю поле VAL (массив)

Код: plsql
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.
CREATE OR REPLACE TYPE val_curr IS VARRAY (1024) OF number (10,3)

function give_fure(p_val_varr val_curr) return val_curr is
  l_val_re   val_curr := val_curr();
  l_val_im   val_curr := val_curr();
  l_val_out  val_curr := val_curr();
  l_pi       number(15, 14) default 3.14159265358979;
  l_rows_cnt pls_integer default p_val_varr.count;
begin
  l_val_re.extend(l_rows_cnt);
  l_val_im.extend(l_rows_cnt);
  l_val_out.extend(l_rows_cnt);
  for k in 1 .. l_rows_cnt loop
    for n in 1 .. l_rows_cnt loop
      l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
      l_val_im(k) := nvl(l_val_im(k), 0) - p_val_varr(n) * sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
    end loop;
  end loop;

  for n in 1 .. p_val_varr.count  loop
    l_val_out(n) := sqrt(power(l_val_re(n), 2) + power(l_val_im(n), 2));
  end loop;

  return l_val_out;
end;
...
Рейтинг: 0 / 0
18.03.2020, 09:17
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938552
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
shadow432,

На вскидку: нормализовать, убратв varray, и добавив number(10,3). Это уберёт цикл. Двойной цикл получится в результате декартова произведения.
...
Рейтинг: 0 / 0
18.03.2020, 09:48
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938559
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
crutchmaster, при таком раскладе получается на каждую строку из 512 записей декартово произведение дает 262144 записи. И я бы рад проверить такой алгоритм (скорее всего он будет работать быстрее), но я ума не приложу как мне прикрутить это декартово произведение к подсчетам Фурье l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt);
...
Рейтинг: 0 / 0
18.03.2020, 09:54
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938565
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
shadow432
но я ума не приложу как мне прикрутить это декартово произведение к подсчетам Фурье

Надо id массива, значение элемента, номер элемента, количество элементов. А потом связать таблицу саму с собой по id массива и посчитать.
shadow432
дает 262144 записи

Не прокатит - пили трехзвенку.
...
Рейтинг: 0 / 0
18.03.2020, 10:01
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938578
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
crutchmaster,

Такую структуру с массивом я выбрал сам для удобства и наглядности (это промежуточная временная таблица), но если использовать декартово произведение, то может имеет смысл сразу развернуть этот массив в общей таблице? Не понял что значит трехзвенка?


Если развернуть так, тогда декартово произведение даст 262144 записей.

A 1
A 4
A 8
........
512 строк
...
Рейтинг: 0 / 0
18.03.2020, 10:19
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938583
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
shadow432
развернуть этот массив в общей таблице?

Каким образом?
shadow432
Не понял что значит трехзвенка?

Вытащить данные из субд куда-нибудь (java/пистон/жс/c#/etc), посчитать, засунуть обратно.
shadow432
Если развернуть так, тогда декартово произведение даст 262144 записей

Да. Будет мегабайт 5-10 на запись. Херня, конечно, лучше сразу трехзвенка. Хотя, если это всё сразу сгрупировать или вставить, то должно быть норм. Но оверхед всё равно дикий.
...
Рейтинг: 0 / 0
18.03.2020, 10:27
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938591
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
crutchmaster,

Должно быть решение SQLем, BULK COLLECT/FORALLы. Просто нужно очень широкое видение возможностей ORACLE. Слишком большие объемы чтобы передавать во внешку. Не думаю что циклы в том же PYTONе будут работать гораздо быстрее. А с учетом передачи данных туда сюда...
В любом случае, спасибо за помощь! Буду копать дальше. Может асы подскажут еще.
...
Рейтинг: 0 / 0
18.03.2020, 10:38
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938599
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
shadow432
Слишком большие объемы чтобы передавать во внешку.

Передавай частями.
shadow432
Не думаю что циклы в том же PYTONе будут работать гораздо быстрее.

В питоне не будет. Во всём остальном - надо смотреть.
shadow432
А с учетом передачи данных туда сюда...

Если не по одной строчке, то тоже не должно быть особых тормозов в этом месте. Передаешь метров 100 сырых данных, считаешь, передаёшь обратно.
...
Рейтинг: 0 / 0
18.03.2020, 10:52
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938600
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
shadow432
Может асы подскажут еще

Оракл-спецефичное может что и есть.
...
Рейтинг: 0 / 0
18.03.2020, 10:53
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938642
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
shadow432,

Вы что каждый раз считаете?
раз посчитать и результат сохранить

1)
мож
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки


2)
l_val_out(n) := sqrt(power(l_val_re(n), 2) + power(l_val_im(n), 2));
понять в for k

3)
вместо l_val_out использовать re/im (p_val_varr сделать in/oit)


ps
врядли декартовое + группировка будут быстрее,
хотя пробовать надо
.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 11:55
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938648
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
вдогонку

делеть на l_rows_cnt можно за циклом
sum(a(i)/n)=sum(a(i))/n

.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 12:00
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938668
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
што-то такое получилось

Код: plsql
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.
with t(k,nam,val) as (
select  1,'A',	1 from dual union all
select  2,'A',	4 from dual union all
select  3,'A',	8 from dual union all
select  4,'A',	7 from dual union all
select  5,'A',	9 from dual union all
select  6,'A',	3 from dual union all
select  1,'B',	7 from dual union all
select  2,'B',	4 from dual union all
select  3,'B',	1 from dual union all
select  4,'B',	2 from dual union all
select  5,'B',	3 from dual union all
select  6,'B',	9 from dual)
select 
  t.*
-- ,(select sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))
,(select sqrt(
   power(sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
  -power(sum(t2.val*sin(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
  )
from t t2 where t2.nam=t.nam) res
from t
SQL> /

         K N        VAL        RES
---------- - ---------- ----------
         1 A          1 5.33333333
         2 A          4 5.33284082
         3 A          8 5.33136324
         4 A          7 5.32890051
         5 A          9 5.32545248
         6 A          3 5.32101892
         1 B          7 4.33333333
         2 B          4  4.3329257
         3 B          1 4.33170284
         4 B          2 4.32966489
         5 B          3 4.32681203
         6 B          9 4.32314456

12 rows selected.



.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 12:41
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938705
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
Stax,


1. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.

2. Поднял. Производительности не дало ни секунды.

3. Сделал IN OUT. Выигрыша в производительности нет.

4. Делить на cnt за циклом нельзя (п.1) Хотя опять же не исключаю что я что то не догоняю.


Сейчас попробую с декартовым переварить.
...
Рейтинг: 0 / 0
18.03.2020, 13:27
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938713
ln123
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ln123
Участник
shadow4321. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.


cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt) детерминировано для k, n, l_rows_cnt а т.к. у вас эти значения повторяются от вычисления к вычислению, то логично посчитать их один раз и при следующих вызовах функции обращаться к уже сохраненным значениям.
Так же не очень хорошо что у вас вычисления идут с типом number, стоит от этого уйти и попробовать Native compilation.
...
Рейтинг: 0 / 0
18.03.2020, 13:45
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938745
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
shadow432
Stax,


1. Нельзя вычислить один раз потому что методика преобразования Фурье такая. Расчет ведется для каждой строки с участием каждой последующей. Может я туплю? Поправьте пожалуйста если так.

2. Поднял. Производительности не дало ни секунды.

3. Сделал IN OUT. Выигрыша в производительности нет.

4. Делить на cnt за циклом нельзя (п.1) Хотя опять же не исключаю что я что то не догоняю.


Сейчас попробую с декартовым переварить.


1) раз посчитать для имени, пересчитивать токо когда поменяется (Вам виднее)

4) так нельзя?
for k in 1 .. l_rows_cnt loop
for n in 1 .. l_rows_cnt loop
l_val_re(k) := nvl(l_val_re(k), 0) + p_val_varr(n) * cos((2 * l_pi * (k - 1) * (n - 1)) ;
l_val_im(k) := nvl(l_val_im(k), 0) - p_val_varr(n) * sin((2 * l_pi * (k - 1) * (n - 1)) ;
end loop;
l_val_re(k) :=l_val_re(k) / l_rows_cnt;
l_val_im(k) :=l_val_im(k)/ l_rows_cnt;

end loop;

ps
,(select sqrt(
power(sum(t2.val*cos(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
-power(sum(t2.val*sin(2 *3.14 * (t.k - 1) * (t2.k - 1)))/count(*),2)
)
проверте результат с пл/скл (мож как всегда что-то напутал)

pss
на sin/cos времени тратится немного?
.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 14:34
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938748
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
ln123,

вычислить один раз и сохранить,а не для каждой строки

.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 14:37
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938828
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
ln123, Спасибо за ценное замечание. Поправил этот баг. В начале выполнения программы считаю отдельными циклами и засовываю эти значения в двумерный ассоциативный массив. Скорость выросла в 10 раз.
Native compilation никогда не использовал. Где почитать про это? Нужны будут права SYS я так понимаю?
...
Рейтинг: 0 / 0
18.03.2020, 16:59
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938836
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
shadow432
Поправил этот баг.




1)
мож
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки

.....
stax
...
Рейтинг: 0 / 0
18.03.2020, 17:17
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938849
shadow432
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
shadow432
Гость
Stax, по всей видимости именно расчет синусов и косинусов жрал большую часть времени. Вынес их расчет в двухмерный ассоциативный массив и код "задышал". Конечно, наверное можно еще оптимизировать, но такой результат для меня уже приемлем. Спасибо за помощь!
...
Рейтинг: 0 / 0
18.03.2020, 17:50
| Ответить | Цитировать | Написать  
sql вместо циклов
    #39938862
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Stax
cos((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
sin((2 * l_pi * (k - 1) * (n - 1)) / l_rows_cnt)
вычислить один раз и сохранить,а не для каждой строки

...а если еще вспомнить, что sin и cos периодические...
...
Рейтинг: 0 / 0
18.03.2020, 18:20
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / sql вместо циклов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=51&tid=1881448]:
 0ms
get settings:
 11ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 34ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 55ms
get tp. blocked users:
 2ms
others: 14ms
total:  152ms
созд. / загр.: 152ms
x
x
Закрыть


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