ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка. Аналитика с выражением по условию?
15 сообщений из 15, страница 1 из 1
Задачка. Аналитика с выражением по условию?
    #40030327
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
Имеется некая функция, на вход получает число, на выходе - другое число.
Функция выполняет ряд простых действий (например сложение, умножение и т.п.) последовательно с нарастающим итогом. Порядок операций и доп. аргументы указаны в отдельной таблице. В сокращённом варианте выглядит это так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
function my_func(a_arg in number) return number is
  l_result number;
begin
  l_result := a_arg;
  for v in (
    with steps as (
      select '+' op, 2 arg, 1 step from dual 
      union all
      select '*' op, 3 arg, 2 step from dual
      union all
      select '/' op, 4 arg, 3 step from dual
    ) select * from steps order by step
  )
  loop
    l_result := 
      case v.op
        when '+' then l_result + v.arg
        when '*' then l_result * v.arg
        when '/' then l_result / v.arg
      end case;
  end loop;
  return l_result;
end;


Здесь steps - это и есть отдельная таблица, т.е. кол-во шагов и значения второго аргумента заранее не определены.

Можно ли это как-то выразить в SQL?


Если, скажем, заранее известно, что кол-во операций будет не больше трёх, я могу написать что-то типа такого:
Код: 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.
with 
  steps as (
    select '+' op, 2 arg, 1 step from dual 
    union all
    select '*' op, 3 arg, 2 step from dual
    union all
    select '/' op, 4 arg, 3 step from dual
  )
  , step0 as (select 1 l_result from dual)
  , step1 as (
    select nvl(
             (select case op when '+' then l_result + arg when '*' then l_result * arg when '/' then l_result / arg end
                from steps
               where step = 1),
             l_result
           ) l_result
      from step0
  ), step2 as (
    select nvl(
             (select case op when '+' then l_result + arg when '*' then l_result * arg when '/' then l_result / arg end
                from steps 
               where step = 2),
             l_result
           ) l_result
      from step1
  ), step3 as (
    select nvl(
             (select case op when '+' then l_result + arg when '*' then l_result * arg when '/' then l_result / arg end
                from steps 
               where step = 3),
             l_result
           ) l_result
      from step2
  )
  select * from step3
;



И это работает, но выглядит очень громоздко (кол-во операций в case на самом деле около 10).


Интуитивно хочется этот case "воткнуть" в аналитическую функцию с нарастающим итогом, но я не придумал, как подставлять результат вычисления с предыдущего шага в текущий.

Рекурсивного SQL в Oracle вроде как нет. С вариантом connect by level стройного и красивого ничего не получается придумать.
Есть у кого какие идеи? Или ну его?
...
Рейтинг: 0 / 0
22.12.2020, 22:58
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030336
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
delphinotes

Рекурсивного SQL в Oracle вроде как нет.


Очень даже есть - Recursive Subquery Factoring:

Код: 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.
with steps as (
               select '+' op, 2 arg, 1 step from dual union all
               select '*' op, 3 arg, 2 step from dual union all
               select '/' op, 4 arg, 3 step from dual
              ),
        r(
          l_result,
          step,
          last_step
         ) as (
                select  1 l_result,
                        0 step,
                        (select max(step) from steps) last_step
                  from  dual
               union all
                select  case s.op
                          when '+' then r.l_result + s.arg
                          when '*' then r.l_result * s.arg
                          when '/' then r.l_result / s.arg
                        end l_result,
                        s.step,
                        r.last_step
                  from  r,
                        steps s
                  where s.step = r.step + 1
              )
select  l_result
  from  r
  where step = last_step
/

  L_RESULT
----------
      2.25

SQL>



SY.
...
Рейтинг: 0 / 0
22.12.2020, 23:45
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030341
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
Еще есть вариант использовать вычисления в xmlquery/xmltable, которые стали существенно быстрее в новых версиях, но нужно использовать именно xquery-операторы, которые немного отличаются, например. +,-,* так и останутся, но деление / надо поменять на div.

Простой пример: в calc мы собираем полное выражение по всем шагам
Код: 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.
with 
 steps as (
    select '+' op, 2 arg, 1 step from dual 
    union all
    select '*' op, 3 arg, 2 step from dual
    union all
    select '/' op, 4 arg, 3 step from dual
 )
,calc(expr) as (
    select 
        rpad('(',count(*),'(') -- скобочки впереди
        ||
        listagg(
          decode(step,1,'$x')             -- в первом шаге добавляем исходную переменную $x
           || decode(op, '/',' div ', op) -- заменяем '/' на ' div '
           || arg || ')'                  -- скобочки в конце
         ) within group(order by step)
         as expr
    from steps
 )
select * from calc;

EXPR
----------------------------------------
((($x+2)*3) div 4)

И остается его только выполнить над данными:
 
от 1 до 10
Код: 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.
with 
 steps as (
    select '+' op, 2 arg, 1 step from dual 
    union all
    select '*' op, 3 arg, 2 step from dual
    union all
    select '/' op, 4 arg, 3 step from dual
 )
,calc(expr) as (
    select 
        rpad('(',count(*),'(') -- скобочки впереди
        ||
        listagg(
          decode(step,1,'$x')             -- в первом шаге добавляем исходную переменную $x
           || decode(op, '/',' div ', op) -- заменяем '/' на ' div '
           || arg || ')'                  -- скобочки в конце
         ) within group(order by step)
         as expr
    from steps
 )
,data(x) as (
    select level from dual connect by level<=10
 )
select
  data.x,
  calc.expr,
  xmlcast(
     xmlquery(calc.expr passing data.x as "x" returning content)
     as number
     ) res
from data, calc;

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
         X EXPR                                            RES
---------- ---------------------------------------- ----------
         1 ((($x+2)*3) div 4)                             2.25
         2 ((($x+2)*3) div 4)                                3
         3 ((($x+2)*3) div 4)                             3.75
         4 ((($x+2)*3) div 4)                              4.5
         5 ((($x+2)*3) div 4)                             5.25
         6 ((($x+2)*3) div 4)                                6
         7 ((($x+2)*3) div 4)                             6.75
         8 ((($x+2)*3) div 4)                              7.5
         9 ((($x+2)*3) div 4)                             8.25
        10 ((($x+2)*3) div 4)                                9

10 rows selected.
...
Рейтинг: 0 / 0
23.12.2020, 00:22
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030346
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
xtender

Простой пример: в calc мы собираем полное выражение по всем шагам


Не забываем про вычитание (знак минус окажется непосредственно за переменной):

 
Код: 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.
41.
with
 steps as (
    select '-' op, 2 arg, 1 step from dual
    union all
    select '*' op, 3 arg, 2 step from dual
    union all
    select '/' op, 4 arg, 3 step from dual
 )
,calc(expr) as (
    select
        rpad('(',count(*),'(') -- ???????? ???????
        ||
        listagg(
          decode(step,1,'$x')             -- ? ?????? ???? ????????? ???????? ?????????? $x
           || decode(op, '/',' div ', op) -- ???????? '/' ?? ' div '
           || arg || ')'                  -- ???????? ? ?????
         ) within group(order by step)
         as expr
    from steps
 )
,data(x) as (
    select level from dual connect by level<=1
 )
select
  data.x,
  calc.expr,
  xmlcast(
     xmlquery(calc.expr passing data.x as "x" returning content)
     as number
     ) res
from data, calc
/
with
*
ERROR at line 1:
ORA-19112: error raised during evaluation:
XVM-01008: [XPST0008] Invalid reference
1   ((($x-2)*3) div 4)
-      ^

SQL>




Исправляем:

 

Код: 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.
with
 steps as (
    select '-' op, 2 arg, 1 step from dual
    union all
    select '*' op, 3 arg, 2 step from dual
    union all
    select '/' op, 4 arg, 3 step from dual
 )
,calc(expr) as (
    select
        rpad('(',count(*),'(') -- ???????? ???????
        ||
        listagg(
          decode(step,1,'$x')             -- ? ?????? ???? ????????? ???????? ?????????? $x
           || decode(op, '/',' div ', ' ' || op || ' ') -- ???????? '/' ?? ' div '
           || arg || ')'                  -- ???????? ? ?????
         ) within group(order by step)
         as expr
    from steps
 )
,data(x) as (
    select level from dual connect by level<=1
 )
select
  data.x,
  calc.expr,
  xmlcast(
     xmlquery(calc.expr passing data.x as "x" returning content)
     as number
     ) res
from data, calc
/

         X EXPR                                  RES
---------- ------------------------------ ----------
         1 ((($x - 2) * 3) div 4)               -.75

SQL>




SY.
...
Рейтинг: 0 / 0
23.12.2020, 02:39
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030362
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
SY,

Круто, спасибо!
Жаль в 10ке не работает, зато в 11й - уже.
...
Рейтинг: 0 / 0
23.12.2020, 09:07
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030363
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
xtender,

Это вообще супер, оно работает даже быстрее рекурсивного. Спасибо!
...
Рейтинг: 0 / 0
23.12.2020, 09:17
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030380
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
Хм.. с выводами я поторопился, при использовании функций ceiling, floor и т.п. xmlquery начинает проседать, да и порядок следования аргументов меняется - собрать итоговое выражение в listagg наверное можно, но оно будет слишком громоздким. Остановлюсь на варианте с рекурсией как наиболее читаемой и более гибкой к расширению.

Ещё раз спасибо.
...
Рейтинг: 0 / 0
23.12.2020, 10:34
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030458
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
delphinotes,

объясните, что вы вообще делаете? почему именно пошагово? не проще было бы сразу указывать финальное выражение? Покажите, какие у вас там операторы есть и какие аргументы вы подставляете в arg?
...
Рейтинг: 0 / 0
23.12.2020, 13:47
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030553
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
xtender,

Это легаси, пытаюсь оптимизировать с обратной совместимостью. В подавляющем большинстве случаев конструктор выражений, который даётся на откуп пользователю, сводится к простому выражению... т.е. действительно, вместо произвольной последовательности выражений достаточно просто указать ряд полей - и всё легко считается в одной строке.
Но бывают исключения... сейчас это всё обрабатывается pl/sql-функцией и это одно из слабых мест при пакетной обработке входных данных (переключения контекста, да).
Операторы: + - * / mod, round, ceil, trunc, floor, least, greatest, кое-что из внутренней кухни... и для эстетов - произвольное выражение через execute immediate :)
Аргументы - это константы, в некоторых случаях на вход подаётся не одно значение, а два и операторы могут брать одно из них, или сразу оба.

Короче, топик больше из спортивного интереса, так то я склоняюсь к пересмотру этого "конструктора".
...
Рейтинг: 0 / 0
23.12.2020, 16:30
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030554
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
delphinotes

Операторы: + - * / mod, round, ceil, trunc, floor, least, greatest, кое-что из внутренней кухни... и для эстетов - произвольное выражение через execute immediate :)


Ну тогда забудь про XQUERY.

SY.
...
Рейтинг: 0 / 0
23.12.2020, 16:41
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030557
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
delphinotes
сейчас это всё обрабатывается pl/sql-функцией и это одно из слабых мест при пакетной обработке входных данных (переключения контекста, да).
Переключение контекста давно уже не является заметной проблемой.
А вот постоянный парсинг одного и того же выражения (что на sql, что на pl/sql) - это бесполезная трата времени.
Рассмотри предкомпиляцию выражения в готовый к исполнению анонимный блок. Это точно выстрелит.
...
Рейтинг: 0 / 0
23.12.2020, 16:48
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030576
delphinotes
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
delphinotes
Гость
Elic,

С юбилейным комментом!
...
Рейтинг: 0 / 0
23.12.2020, 17:55
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030583
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
еще SQL Macro можно рассмотреть с Oracle 19.7
...
Рейтинг: 0 / 0
23.12.2020, 18:08
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030598
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
xtender
еще SQL Macro можно рассмотреть с Oracle 19.7


SQL Macro прекрасная обертка на ODCI table interface и с ними не надо тратить кучу времени на писанину ODCITableDescribe, ODCITablePrepare, ODCITableFetch... но как и ODCI table interface "страдает" от soft parse и посему параметры придется передавать литералом.

SY.
...
Рейтинг: 0 / 0
23.12.2020, 18:37
| Ответить | Цитировать | Написать  
Задачка. Аналитика с выражением по условию?
    #40030613
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Хотя в данном случае SQL Macro хорошее решение т.к. число возвращаемых полей не меняется.\

SY.
...
Рейтинг: 0 / 0
23.12.2020, 19:02
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка. Аналитика с выражением по условию?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=29&tid=1880569]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 40ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 16ms
total:  152ms
созд. / загр.: 152ms
x
x
Закрыть


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