ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / PIVOT
18 сообщений из 18, страница 1 из 1
PIVOT
    #39747640
leprechaun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
leprechaun
Гость
Подскажите, как одним селектом развернуть таблицу. Мне просто нужно значения в колонки.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
--работает
select * from(
select NAME  from hr_operating_units)
pivot
(max (NAME) for NAME  IN ('A', 'B'))

--нет
select * from(
select NAME  from hr_operating_units)
pivot
(max (NAME) for NAME  IN (select NAME from hr_operating_units))
...
Рейтинг: 0 / 0
13.12.2018, 21:29
| Ответить | Цитировать | Написать  
PIVOT
    #39747643
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
leprechaunМне просто нужно значения в колонки.


Доку кури. Список PIVOT значений в кляузе FOR это список литералов (за исключением XML вeрсии, но это тебе ничего не даст).

SY.
...
Рейтинг: 0 / 0
13.12.2018, 21:37
| Ответить | Цитировать | Написать  
PIVOT
    #39747664
leprechaun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
leprechaun
Гость
Ну а массив туда же можно вставить?

А какие еще способы развернуть таблицу чтобы колонки стали столбцами. Вообще пока проблема для меня построить сводную таблицу с неизвестным (зависящим от выборки) количеством колонок
...
Рейтинг: 0 / 0
13.12.2018, 23:15
| Ответить | Цитировать | Написать  
PIVOT
    #39747669
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
leprechaunА какие еще способы развернуть таблицу чтобы колонки стали столбцами. Вообще пока проблема для меня построить сводную таблицу с неизвестным (зависящим от выборки) количеством колонок

Ну тогда ODCI table interface тебе в руки. Видел готoвые пакеты на просторах интернета. А вообще это нужно в репoртах и все reporting tools это прекрасно умеют.

SY.
...
Рейтинг: 0 / 0
14.12.2018, 00:05
| Ответить | Цитировать | Написать  
PIVOT
    #39747679
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
SYНу тогда ODCI table interface тебе в руки.
Для указанной цели реально использовать можно будет только с 18с.
...
Рейтинг: 0 / 0
14.12.2018, 01:48
| Ответить | Цитировать | Написать  
PIVOT
    #39747694
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andrey_anonymousреально использовать можно будет только с 18с.Если про PTF, то это не то. Там можно явно написать columns со списком полей, но чем это отличается от перечисления в pivot?
И odcitable и ptf должны узнать поля в методе describe, то есть на этапе парса. Можно было бы передавать параметр-курсор и в describe профетчить строки в коллекцию колонок. Но предполагаю, что describe увидит null даже в случае литерально-явного курсорного выражения cursor(...).
Если только передавать параметром количество колонок и именовать их поле1, поле2,... Но, опять же, передаваемое значение должно вычисляться на этапе парса, то есть не может быть подзапросом или биндом (plsql-переменной). Это не принципиально отличается от перечисления имен.
...
Рейтинг: 0 / 0
14.12.2018, 07:06
| Ответить | Цитировать | Написать  
PIVOT
    #39747758
leprechaun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
leprechaun
Гость
Как я пока понял (ранее не пользовался) PIVOT пригоден для работы с тремя колонками

Вот если мне нужно получить счет на пересечении организации и операции

select * from(
select NAME, ORG_ID, CODE_COMBINATION_ID
from ar_receivables_trx_all
where CODE_COMBINATION_ID IS NOT NULL)
pivot
(max (CODE_COMBINATION_ID) for ORG_ID IN ('222','333','444'))

Так и не успел проверить на условии IN массив заработает? должен по идее, тогда всё ок.

В 1с да такая таблица двумя щелчками строится, у тут я хз всякие репорт билдеры не использую, паблишер, а там надо или кучу кода писать генерировать xml в цикле или явно обозначать количество колонок COL1, COL2.. с запасом.
Но такой код стыдно писать.
...
Рейтинг: 0 / 0
14.12.2018, 10:24
| Ответить | Цитировать | Написать  
PIVOT
    #39747847
leprechaun
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
leprechaun
Гость
PL/SQL: ORA-56901: в качестве значений pivot|unpivot не разрешается использовать неконстантное выражение

Прийдется что ли переменную конкатенировать
...
Рейтинг: 0 / 0
14.12.2018, 12:31
| Ответить | Цитировать | Написать  
PIVOT
    #39747882
Фотография env
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
env
Участник
leprechaunВ 1с да такая таблица двумя щелчками строится

Месье путает тёплое с мягким.
...
Рейтинг: 0 / 0
14.12.2018, 13:14
| Ответить | Цитировать | Написать  
PIVOT
    #39747935
merch
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
merch
Участник
Каждые пол года, где-то в укромном уголке, ночью, под светом тусклой лампы сидит оракловый пользователь.
Пишет запросы и добирается до пивота.

Поигравшись с константами ему приходит озарение. Он начинает бить по клавишам, и пытаться получить динамически список литералов. Бьется, откладывает, снова берется...

Но вот наступает 3 ночи.
Мельком глянув в доку (а может и нет), он открывает sql.ru и начинает рассказывать, как на своем калькуляторе он бы сделал такое за ужином одной правой рукой и даже не напрягаясь...
...
Рейтинг: 0 / 0
14.12.2018, 14:14
| Ответить | Цитировать | Написать  
PIVOT
    #39747982
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
-2-andrey_anonymousреально использовать можно будет только с 18с.Если про PTF, то это не то. Там можно явно написать columns со списком полей, но чем это отличается от перечисления в pivot?
И odcitable и ptf должны узнать поля в методе describe, то есть на этапе парса.
Как раз полиморфные не обязаны на этапе парса, если я правильно понял доку - тем и отличаются от существующих реализаций.
Руками еще не пробовал.
...
Рейтинг: 0 / 0
14.12.2018, 15:31
| Ответить | Цитировать | Написать  
PIVOT
    #39748000
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
-2-Но, опять же, передаваемое значение должно вычисляться на этапе парса, то есть не может быть подзапросом или биндом (plsql-переменной). Это не принципиально отличается от перечисления имен.

Давным-давно баловался вызовом SYS.DBMS_SHARED_POOL.PURGE в ODCITableClose чтобы ODCITableDescribe вызвалась при cледующем выполнении, но это всe баловство.

SY.
...
Рейтинг: 0 / 0
14.12.2018, 15:50
| Ответить | Цитировать | Написать  
PIVOT
    #39748055
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andrey_anonymousКак раз полиморфные не обязаны на этапе парса, если я правильно понял доку - тем и отличаются от существующих реализаций.Концепция describe-fetch-close непоколебима. Во время парса вызывается describe с передачей параметров статически таблица(в функции доступна как коллекция имяполя+тип), статически колонки (коллекция строк), прочие аргументы могут быть нестатическими, но тогда в describe попадет null.
Сформировать набор полей по фактическим данным нельзя. Более того, в PTF нельзя по нормальному и сами строки формировать, только через мультипликатор существующей. Смысла в формировании значений на основании других полей или размножении строк не более нуля. Все это делается более декларативными средствами sql (unpivot, lateral).
Может накопление счетчиков по мере обработки строк, типа аналитических функций? Омрачается тем, что partition/order задается на весь набор и для применения нескольких ptf нужен каскад из них, каждый раз оборачивая в with. Требуется осторожное применение параллелизма, так как в каждой параллели свой набор порций, а мерджа в ptf нет. Ну и сам набор для накопления определяется не синтаксисом, а оптимизатором. Оптимизатор может выбрать применение фильтров из where как до передачи порции в функцию, так и после. Odciaggregate для аналитики выглядит более удачным вариантом.
...
Рейтинг: 0 / 0
14.12.2018, 17:29
| Ответить | Цитировать | Написать  
PIVOT
    #39748088
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
-2-,

Думаю вaжно уточнить только во время hard парса вызывается describe. Вот если бы Oracle добавил к hard парсу флаг "ODCI Table Interface" который бы проверялся при soft парсе и если да то перенаправлядся на hard парс (примерно ту же логику я и пытался реализовать через SYS.DBMS_SHARED_POOL.PURGE но что-то там не сросталось, сейчас уже не помню что).

SY.
...
Рейтинг: 0 / 0
14.12.2018, 18:01
| Ответить | Цитировать | Написать  
PIVOT
    #39748096
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
-2-Во время парса вызывается describe с передачей параметров статически таблица(в функции доступна как коллекция имяполя+тип), статически колонки (коллекция строк), прочие аргументы могут быть нестатическими, но тогда в describe попадет null.
Т.е. ничего не изменилось кроме появления спец. типа table?
-2-Сформировать набор полей по фактическим данным нельзя.
Это грустно.
-2-Более того, в PTF нельзя по нормальному и сами строки формировать, только через мультипликатор существующей. Смысла в формировании значений на основании других полей или размножении строк не более нуля. Все это делается более декларативными средствами sql (unpivot, lateral).

Вот тут не понял. Pipelined хороши, например, в роли каскадных "фильтров-обогатителей-трансформаторов".
Формирование новых строк - есть вполне разумные применения.
Я делал под целевой merge (формирование scd2-like структур из GGшных наборов изменений с некоторыми межстрочными вычислениями) и insert all (однопроходное формирование разнородных многуровневых агрегатов прямо из потока фактов).
SQL-ем это, конечно, можно, но по факту очень громоздко и сложно сопровождается.
Из серьезных минусов - необходимость генерации шаблонного текста под каждый датасет, эта проблема в PTF точно решена.

Формирование значений из других полей - вот нашел примерчик формирования JSON на PTF - удобно
https://oracle-base.com/articles/18c/polymorphic-table-functions-18c

-2-Требуется осторожное применение параллелизма, так как в каждой параллели свой набор порций
Ну в обычных pipelined то же самое, это, промеждупрочим, создает сложности с организацией эффективного параллельного процесса на больших объемах ввиду общего ограничения на количество наборов slave-процессов.
Решение этой проблемы мне неизвестно.
...
Рейтинг: 0 / 0
14.12.2018, 18:12
| Ответить | Цитировать | Написать  
PIVOT
    #39748171
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andrey_anonymousPipelined хороши, например, в роли каскадных "фильтров-обогатителей-трансформаторов".
Формирование новых строк - есть вполне разумные применения. Фильтр из пайплайна, конечно можно сделать, выставив мультипликатор строки в ноль. Из полей a и б получить поле синус(а+б) - PTF не нужен.
Приведенный пример с json тому показатель. То же самое можно получить просто указав в списке выборки json_object. Чтобы передать в ptf поля из разных таблиц - пиши with.
И за примерами рекомендую библиотеку livesql.

andrey_anonymousФормирование новых строк - есть вполне разумные применения.PTF не может формировать строки, может только размножать. А если на вход не пришло строк, то и размножать нечего. Оно даже итоги вставить не пригодно, потому как нельзя определить, что группа, чьи итоги, закончилась. Unpivot, lateral, rigth и partition join, grouping sets с подобными задачами справляются.

andrey_anonymousмежстрочными вычислениямиЕсли межстрочные - это то, что я называю накопительными счетчиками, то озвучил отношение в сравнении с odciaggregate. В PTF порционная обработка накладывет определенную алгоритмическую ответственность на релизацию. Многие ошибаются даже в реализации поиска слова в lob при чтении его кусками. В PTF проще обеспечить произвольность по составу обрабатываемых полей, но сомневаюсь, что "межстрочные вычисления" универсально применимы к произвольным полям.

andrey_anonymousнеобходимость генерации шаблонного текста под каждый датасетcursor в качестве параметра-датасета и dbms_sql для разбора произвольного запроса в обработчике. Да, собственно, это же самое нужно делать и в PTF.describe. В нем рекорды аналогичны dbms_sqlевским, только пока в документации много ошибок, так как ptf-хрень никто не пользуется.
...
Рейтинг: 0 / 0
14.12.2018, 20:40
| Ответить | Цитировать | Написать  
PIVOT
    #39748413
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Демонстрация динамической колокольни.
 
Код: 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.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
create or replace package ptf_ncol_p is 
function describe(
  tbl in out dbms_tf.table_t,  
  ncol       number
) return     dbms_tf.describe_t; 

procedure fetch_rows(
  ncol       number
); 
end ptf_ncol_p; 
/

create or replace package body ptf_ncol_p is
function describe(
  tbl in out dbms_tf.table_t,
  ncol       number
) return     dbms_tf.describe_t is
  cols       dbms_tf.columns_new_t;
begin
  if ncol is null then
    cols(1) := dbms_tf.column_metadata_t(
                 name    => 'COLNULL',
                 type    => dbms_tf.type_varchar2,
                 max_len => 30);
  else
    for i in 1..ncol loop
      cols(i) := dbms_tf.column_metadata_t(
                 name    => 'COL'||i,
                 type    => dbms_tf.type_varchar2,
                 max_len => 30);
    end loop;
  end if;
  return dbms_tf.describe_t(new_columns => cols);
end describe;

procedure fetch_rows(
  ncol       number
) is
  nrow       number := 0;
  env        dbms_tf.env_t;
  col        dbms_tf.tab_varchar2_t;
begin
  env := dbms_tf.get_env();
  dbms_tf.xstore_get('nrow', nrow);
  for c in 1..env.put_columns.count loop
    for r in 1..env.row_count loop
      col(r) := 'c='||c||'/'||ncol||' r='||(nrow + r);
    end loop;
    dbms_tf.put_col(c, col);
    col.delete();
  end loop;
  dbms_tf.xstore_set('nrow', nrow + env.row_count);
end fetch_rows;

end ptf_ncol_p;
/

create or replace function ptf_ncol(
  tab    table,
  ncol   number
) return table 
pipelined table polymorphic using ptf_ncol_p; 
/

Имена полей формируются как COLномер по переданному в ptf значению. В добавленных полях выводится: с=номерполя/заданополей r=номерстроки
Код: 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.
with t as (select level l from dual connect by level<=5)
select *
from ptf_ncol(t, 2);

         L COL1                           COL2                          
---------- ------------------------------ ------------------------------
         1 c=1/2 r=1                      c=2/2 r=1                     
         2 c=1/2 r=2                      c=2/2 r=2                     
         3 c=1/2 r=3                      c=2/2 r=3                     
         4 c=1/2 r=4                      c=2/2 r=4                     
         5 c=1/2 r=5                      c=2/2 r=5                     

var n number
exec :n := 3;

with t as (select level l from dual connect by level<=5)
select *
from ptf_ncol(t, :n);

         L COLNULL                        -- describe
---------- ------------------------------
         1 c=1/3 r=1                      -- fetch_rows
         2 c=1/3 r=2                     
         3 c=1/3 r=3                     
         4 c=1/3 r=4                     
         5 c=1/3 r=5                     

with t as (select level l from dual connect by level<=5)
select *
from ptf_ncol(t, (select 2 from dual));

ORA-03113: end-of-file on communication channel
-- ORA-07445: exception encountered: core dump [subsr1()+64] [SIGSEGV] [ADDR:0x28] [PC:0x34E6B30] [Address not mapped to object] []


SYДумаю вaжно уточнить только во время hard парса вызывается describe. Вот если бы Oracle добавил к hard парсу флаг "ODCI Table Interface" который бы проверялся при soft парсе и если да то перенаправлядся на hard парс (примерно ту же логику я и пытался реализовать через SYS.DBMS_SHARED_POOL.PURGE но что-то там не сросталось, сейчас уже не помню что).Хардпарс все равно не видит динамических значений, а другое статическое значение и так приведет к хардпарсу. Если включить cursor_sharing, то либо найдется курсор с таким же статическим значением, либо будет использован результат парса c null.
Код: 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.
42.
43.
44.
alter session set cursor_sharing=exact;
alter system flush shared_pool;

with t as (select level l from dual connect by level<=3) select * from ptf_ncol(t, 1);

	 L COL1
---------- ------------------------------
	 1 c=1/1 r=1
	 2 c=1/1 r=2
	 3 c=1/1 r=3

with t as (select level l from dual connect by level<=3) select * from ptf_ncol(t, 3);

	 L COL1 			  COL2				 COL3
---------- ------------------------------ ------------------------------ ------------------------------
	 1 c=1/3 r=1			  c=2/3 r=1			 c=3/3 r=1
	 2 c=1/3 r=2			  c=2/3 r=2			 c=3/3 r=2
	 3 c=1/3 r=3			  c=2/3 r=3			 c=3/3 r=3

alter session set cursor_sharing=force;

with t as (select level l from dual connect by level<=3) select * from ptf_ncol(t, 1);

	 L COL1
---------- ------------------------------
	 1 c=1/1 r=1
	 2 c=1/1 r=2
	 3 c=1/1 r=3

with t as (select level l from dual connect by level<=3) select * from ptf_ncol(t, 2);

	 L COLNULL
---------- ------------------------------
	 1 c=1/2 r=1
	 2 c=1/2 r=2
	 3 c=1/2 r=3

with t as (select level l from dual connect by level<=3) select * from ptf_ncol(t, 3);

	 L COL1 			  COL2				 COL3
---------- ------------------------------ ------------------------------ ------------------------------
	 1 c=1/3 r=1			  c=2/3 r=1			 c=3/3 r=1
	 2 c=1/3 r=2			  c=2/3 r=2			 c=3/3 r=2
	 3 c=1/3 r=3			  c=2/3 r=3			 c=3/3 r=3
...
Рейтинг: 0 / 0
15.12.2018, 16:40
| Ответить | Цитировать | Написать  
PIVOT
    #39748422
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
-2-Хардпарс все равно не видит динамических значений


Конечно, но это и не нужно. Для PIVOT FOR нам нужно знать имя поля, или, для более общего случая, SELECT получения списка значений кляузы FOR (в виде литерала естественно). SELECT мы выполним в describe и получим список значений с помощью которого сконструируем конечный SELECT, т.e. сам PIVOT. Проблема в том что мы передаем тот-же литерал-SELECT, просходит soft parse и describe уже не вызывается а значения (или их количество) возвращаемые литерал-SELECTом могли измениться.
В принципе можно тупо в SQL*Plus генерить PIVOTи тут-же выполнять сгенерированный скрипт. Или:

Код: plsql
1.
2.
3.
4.
5.
6.
variable v_cur refcursor
begin
    open :v_cur for 'сгeнерированный PIVOT SQL';
end;
/
print v_cur



Но хотелось-бы чего-то систeмного, хотя, как уже не раз говорилось, все-это есть в reporting tools.

SY.
...
Рейтинг: 0 / 0
15.12.2018, 17:47
| Ответить | Цитировать | Написать  
18 сообщений из 18, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / PIVOT
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39747664&tid=1883031]:
 0ms
get settings:
 11ms
get forum list:
 20ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 181ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 1ms
others: 250ms
total:  536ms
созд. / загр.: 536ms
x
x
Закрыть


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