Поворот (транспонирование) таблицы является не реляционной операцией, а операцией
работы с многомерными структурами данных.
В реляционных БД транспонирование это сортировка и/или агрегация.
В этом посте собраны различные способы транспонирования, каждый из которых
может использоваться как и в последних версиях Oracle, так и встречаться в
legacy.
Создадим
таблицу с курсами валют:
create table currency_rates
(as_of_date
date,
currency varchar2(3),
rate number
);
Заполним данными:
begin
insert into currency_rates
select date '2019-10-31' as as_of_date, 'USD' as currency, 24.8082 as rate from dual union all
select date '2019-10-31' as as_of_date, 'EUR' as currency, 27.4011 as rate from dual union all
select date '2019-10-31' as as_of_date, 'CHF' as currency, 24.9683 as rate from dual union all
select date '2019-11-30' as as_of_date, 'USD' as currency, 24.3675 as rate from dual union all
select date '2019-11-30' as as_of_date, 'EUR' as currency, 26.9584 as rate from dual union all
select date '2019-11-30' as as_of_date, 'CHF' as currency, 24.5608 as rate from dual union all
select date '2019-12-31' as as_of_date, 'USD' as currency, 23.6094 as rate from dual union all
select date '2019-12-31' as as_of_date, 'EUR' as currency, 26.2069 as rate from dual union all
select date '2019-12-31' as as_of_date, 'CHF' as currency, 23.9830 as rate from dual;
commit;
end;
Транспонируем таблицу:
💧 Left
Join
В данном
случае здесь самообъединение, но при объединении разных таблиц и приведении
данных к единому формату лучше совместить объединение и поворот в одном
запросе, чем собрать данные, а потом транспонировать. Объединение hash join,
которое в этом случае должно быть, и так достаточно дорогостоящее, чтобы
утяжелять его агрегированием.
select as_of_date,
usd,
eur,
chf
from (select r.as_of_date,
r1.rate as usd,
r2.rate as eur,
r3.rate as chf,
row_number () over (partition by r.as_of_date
order by r.as_of_date) as rn
from currency_rates r
left join (select as_of_date,
currency, rate from currency_rates where currency = 'USD')
r1
on r.as_of_date = r1.as_of_date
left join (select as_of_date,
currency, rate from currency_rates where currency = 'EUR')
r2
on r.as_of_date = r2.as_of_date
left join (select as_of_date,
currency, rate from currency_rates where currency = 'CHF') r3
on r.as_of_date = r3.as_of_date
)
where rn = 1;
💧 CROSS APPLY (OUTER APPLY) (Oracle 12c)
В данном
случае не имеет разницы: CROSS или OUTER.
select as_of_date,
usd,
eur,
chf
from (select r.as_of_date,
r1.rate as usd,
r2.rate as eur,
r3.rate as chf,
row_number () over (partition by r.as_of_date
order by r.as_of_date) as rn
from currency_rates r
cross apply (select as_of_date,
currency, rate from currency_rates r1
where currency = 'USD' and r.as_of_date
= r1.as_of_date) r1
cross apply (select as_of_date,
currency, rate from currency_rates r1
where currency = 'EUR' and r.as_of_date
= r1.as_of_date) r2
cross apply (select as_of_date,
currency, rate from currency_rates r1
where currency = 'CHF' and r.as_of_date
= r1.as_of_date) r3
)
where rn
= 1;
💧 Условное
агрегирование - через CASE или DECODE
select r.as_of_date,
max(case when r.currency
= 'USD' then r.rate end) as usd,
max(case when r.currency
= 'EUR' then r.rate end) as eur,
max(case when r.currency
= 'CHF' then r.rate end) as chf
from currency_rates r
group by r.as_of_date;
select r.as_of_date,
max(decode(r.currency, 'USD', r.rate, null)) as usd,
max(decode(r.currency, 'EUR', r.rate, null)) as eur,
max(decode(r.currency, 'CHF', r.rate, null)) as chf
from currency_rates r
group by r.as_of_date;
💧 PIVOT
В версии
11.1 в синтаксисе оператора SELECT появились фразы PIVOT и UNPIVOT, которые
выполняют транспонирование прозрачно и просто.
План запроса получается простой и эффективный - запрос будет хорошо выполняться
даже на больших объёмах.
Комментарии:
1. Первый select * from ( ) обязателен.
2. Внутри фразы pivot ( ) сначала должна быть агрегатная функция, даже
если фраза group by явно не указана. PIVOT выполнит неявный group by по всем
полям, кроме поля в агрегатной функции и полей после FOR. Это необходимо, чтобы
обработать ситуацию, когда там окажется несколько строк. Если строка одна -- не
важно, что писать, MIN, MAX, SUM, AVG...
3.В
операторе PIVOT требуется явно перечислить столбцы для вывода. Это серьезное
ограничение, т.к. для этого нужно знать характер данных.
Синтаксис:
SELECT .... -- выводится столбец (столбцы), значения из которого
формируют заголовки строк и значения из столбца, который указан и pivot_column
FROM table
-- здесь может быть подзапрос
PIVOT --
формирование пивот-таблицы
(
aggregate-function(column)
-- агрегатная функция, формирующая содержимое
сводной таблицы
FOR
pivot_column --
указывается столбец, уникальные значения в котором будут являться заголовками столбцов
IN (value1, value2,..., valuen) --
указываются конкретные значения в столбце type, которые следует использовать в качестве заголовков, т.к.
могут потребоваться не все
)
WHERE .....
Применяем:
select *
from currency_rates r
pivot (max(rate) as max_rate for currency in ('USD' as usd, 'EUR' as eur, 'CHF' as chf));
Если
необходимо агррегировать несколько столбцов или применять несколько агрегатных
функций, перечисляем в aggregate-function(column) через
запятую.
select *
from currency_rates r
pivot (max(rate) as max_rate, avg(rate) as avg_rate for currency in ('USD' as usd, 'EUR' as eur, 'CHF' as chf));
Посмотрим
план запроса:
Elapsed
time: 0,052 sec
| Id |
Operation |
Name |
Rows |
Bytes |
Cost |
Time |
| 0 |
SELECT STATEMENT |
| 3 |
51 |
3 |
00:00:01 |
| 1 |
. HASH GROUP BY
PIVOT |
| 3 |
51 |
3 |
00:00:01 |
| 2 |
.. TABLE ACCESS FULL
|
CURRENCY_RATES |
9 |
153 |
2 |
00:00:01 |
Сравним с
планом запроса условной агрегации (запрос см. выше):
Elapsed
time: 0,058 sec
| Id |
Operation |
Name |
Rows |
Bytes |
Cost |
Time |
| 0 |
SELECT STATEMENT |
| 3 |
51 |
3 |
00:00:01 |
| 1 |
. HASH GROUP BY
|
| 3 |
51 |
3 |
00:00:01 |
| 2 |
.. TABLE ACCESS FULL
|
CURRENCY_RATES |
9 |
153 |
2 |
00:00:01 |
Стоимость
запроса одинаковая, разницу во времени выполнения можно не учитывать. На
больших объемах время выполнения тоже будет приблизительно одинаково, выигрыша
нет, потому что массив сортируется и агрегируется одинаково. PIVOT субъективно
удобнее - сам выстроит уникальные названия столбцов, легче споровождается.
💧 UNPIVOT
Транспонируем
обратно:
select * from
(select *
from currency_rates r
pivot (max(rate) for currency in ('USD' as usd, 'EUR' as eur, 'CHF' as chf))
)
unpivot (rate for currency in(usd, eur, chf));
По умолчанию
используется предложение EXCLUDE NULLS. Чтобы вывести значения null необходимо
прописать INCLUDE NULLS.
select * from
(select *
from currency_rates r
pivot (max(rate) for currency in ('USD' as usd, 'EUR' as eur, 'CHF' as chf))
union all
select date '2020-01-01' as as_of_date, null as usd, 26.2078 as eur, null as chf from dual
)
unpivot include nulls (rate for currency in(usd, eur, chf));
Результат:
|
AS_OF_DATE
|
CURRENCY
|
RATE
|
|
12/31/2019
|
USD
|
23.6094
|
|
12/31/2019
|
EUR
|
26.2069
|
|
12/31/2019
|
CHF
|
23.983
|
|
11/30/2019
|
USD
|
24.3675
|
|
11/30/2019
|
EUR
|
26.9584
|
|
11/30/2019
|
CHF
|
24.5608
|
|
10/31/2019
|
USD
|
24.8082
|
|
10/31/2019
|
EUR
|
27.4011
|
|
10/31/2019
|
CHF
|
24.9683
|
|
1/1/2020
|
USD
|
|
|
1/1/2020
|
EUR
|
26.2078
|
|
1/1/2020
|
CHF
|
|
💧 PIVOT XML
Синтаксис
аналогичен PIVOT, но существуют два четких различия:
1. pivot
xml вместо просто pivot. Это создает вывод в формате XML.
2. Выражение
for содержит for currency in (any) вместо длинного списка
значений. XML позволяет использовать ключевое слово ANY, и не нужно
вводить значения.
select *
from currency_rates r
pivot xml (max(rate) as max_rate, avg(rate) as avg_rate for currency in (any));
Вместо можно
ANY использовать подзапрос с обязательным условием уникальности результатов,
поэтому distinct.
select *
from currency_rates r
pivot xml (max(rate) as max_rate, avg(rate) as avg_rate
for currency in (select distinct currency from currency_rates));
Результат выводится как XMLTYPE.
💧
ODCIVARCHAR2LIST, ODCINUMBERLIST, ODCIDATELIST
Для
разворота одной строки в столбец можно использовать табличные типы - уже
существующие в БД или созданные специально для конкретного случая.
select * from table(sys.odcivarchar2list('1','2','3'));
select * from table(sys.odcinumberlist('1','2','3'));
select * from table(sys.odcidatelist(to_date('01.02.2000', 'DD.MM.YYYY'),to_date('01.02.2010', 'DD.MM.YYYY'),to_date('01.02.2020', 'DD.MM.YYYY')));
Но ODCIVARCHAR2LIST, ODCINUMBERLIST, ODCIDATELIST это VARRAY и имеют ограничение 32767 элементов.
Если этого
недостаточно, поискать по своему вкусу подходящий тип можно здесь:
select *
from all_types
where typecode = 'COLLECTION';
Например:
select * from table(dbms_debug_vc2coll('1','2','3'))
или
select coll_type,
elem_type_name, type_name, length,
upper_bound
from all_coll_types
where owner = 'SYS'
and elem_type_name IN ('VARCHAR2', 'NUMBER')
order by coll_type,
elem_type_name, type_name;
Или если совсем все плохо, взять типы из пакета DBMS_SQL:
type
Number_Table is table of number index by binary_integer;
type Varchar2_Table is table of varchar2(4000) index by binary_integer;
type Date_Table is table of date index by binary_integer;
type Blob_Table is table of Blob index by binary_integer;
type Clob_Table is table of Clob index by binary_integer;
<...skip...>
и так далее
(см.в пакете).
При создании
своего типа VARRAY следует помнить ограничение Oracle в 2147483647 элементов,
иначе получим ошибку:
PLS-00325: non-integral numeric literal 2147483648 is inappropriate in this context
💧 LISTAGG
Подзапрос в
части for можно использовать только в PIVOT XML. Для pivot необходимо
заранее определить столбцы. Это можно сделать через нетипизированный курсор, но
для того, чтобы использовать результат транспонирования все равно придется
приводить к определенному типу, и тип данных и количество столбцов нужно знать
заранее.
Ниже
приведена функция, которая возвращает REF CURSOR.
create function
pivot_currency return sys_refcursor
is
rc sys_refcursor;
begin
for r in (
select
listagg (''''||currency||''' '||currency, ',') within group (order by 1) currency
from (
select distinct
currency from currency_rates)
)
loop
open rc for '
select * from (
select *
from currency_rates)
pivot (max(rate) as max_rate for currency in ('||r.currency||'))';
end loop;
if not rc%isopen
then
open rc for
select 'no data found'
msg from
dual; end if;
return rc;
end;
При желании
в дальнейшем можно использовать в pipeline функции с фетчем в типизированный
курсор.
Но основной
принцип остаётся прежним: столбцы в сете результата должны быть известны
парсеру SQL.
Есть еще
одно решение (пакет), предложенное Томом Кайтом для тех, кто еще работает на
версиях Oracle до 11.1 (подробно разбирается здесь).
Имеет смысл его сохранить на всякий случай, если найдется БД старой версии и с
ней придется работать, но ниже 11-й давно не встречалось, и использовать этот
пакет можно, например, ради спортивного интереса.
На практике
же там, где можно избежать динамического SQL, его следует избегать. Потому что
каждый раз выполняется парсинг самого запроса и план выполнения не сохраняется,
что на больших массивах увеличивает время выполнения и снижает продуктивность.
Спасибо, что
прочли. Надеюсь, будет полезно.
Источники:
0 Комментарии