UNPIVOT оператор — Oracle PL/SQL

В этой статье вы узнаете, как использовать UNPIVOT в Oracle PL/SQL с синтаксисом и примерами.

Описание

Оператор UNPIVOT в Oracle PL/SQL — это мощный инструмент для преобразования столбцов в строки, что особенно полезно при работе с нормализацией данных или подготовкой информации для отчетов.

Основные сценарии применения UNPIVOT:

Трансформация данных — преобразование широких таблиц с множеством столбцов в длинный формат
Нормализация структур — подготовка данных для хранения в реляционных моделях
Анализ показателей — удобное представление метрик для сравнения
Подготовка данных для визуализации — создание структур, подходящих для инструментов BI
Обработка сводных таблиц — преобразование PIVOT-представлений обратно в строки

Синтаксис

SELECT ...
FROM таблица
UNPIVOT [INCLUDE NULLS | EXCLUDE NULLS] (
значения_столбца
FOR имя_столбца_ключа
IN (столбец1 [AS алиас1], столбец2 [AS алиас2], ...)
)

Параметры или аргументы

Параметр Описание
INCLUDE NULLS/EXCLUDE NULLS - Включать или исключать NULL-значения (по умолчанию EXCLUDE NULLS).
значения_столбца - Имя столбца для значений преобразуемых столбцов.
имя_столбца_ключа - Имя столбца для имен преобразуемых столбцов.
IN (столбец1, столбец2) - Список столбцов для преобразования.

Поддержка версий

Оператор UNPIVOT доступен в следующих версиях Oracle:

Oracle 11g и выше

Примеры использования

Базовый пример

-- Исходная таблица с квартальными продажами
SELECT * FROM quarterly_sales;

-- Преобразование столбцов кварталов в строки
SELECT year, quarter, amount
FROM quarterly_sales
UNPIVOT (
  amount
  FOR quarter
  IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')
)
ORDER BY year, quarter;

-- Исходная таблица с квартальными продажами

SELECT * FROM quarterly_sales;

-- Преобразование столбцов кварталов в строки

SELECT year, quarter, amount

FROM quarterly_sales

UNPIVOT (

amount

FOR quarter

IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')

)

ORDER BY year, quarter;

Пример с несколькими метриками

-- Преобразование нескольких групп столбцов
SELECT product_id, metric_type, period, value
FROM product_stats
UNPIVOT (
  (value)
  FOR (metric_type, period)
  IN (
    (sales_q1, 'Q1') AS ('SALES', 'Q1'),
    (sales_q2, 'Q2') AS ('SALES', 'Q2'),
    (profit_q1, 'Q1') AS ('PROFIT', 'Q1'),
    (profit_q2, 'Q2') AS ('PROFIT', 'Q2')
  )
);

-- Преобразование нескольких групп столбцов

SELECT product_id, metric_type, period, value

FROM product_stats

UNPIVOT (

(value)

FOR (metric_type, period)

IN (

(sales_q1, 'Q1') AS ('SALES', 'Q1'),

(sales_q2, 'Q2') AS ('SALES', 'Q2'),

(profit_q1, 'Q1') AS ('PROFIT', 'Q1'),

(profit_q2, 'Q2') AS ('PROFIT', 'Q2')

)

);

Обработка NULL-значений

-- Включение строк с NULL-значениями
SELECT department_id, employee_type, count
FROM department_stats
UNPIVOT INCLUDE NULLS (
  count
  FOR employee_type
  IN (full_time AS 'FULL', part_time AS 'PART', contract AS 'CONT')
);

-- Включение строк с NULL-значениями

SELECT department_id, employee_type, count

FROM department_stats

UNPIVOT INCLUDE NULLS (

count

FOR employee_type

IN (full_time AS 'FULL', part_time AS 'PART', contract AS 'CONT')

);

Продвинутые техники

Динамический UNPIVOT

Для случаев, когда список столбцов неизвестен заранее, можно использовать динамический SQL:

DECLARE
  v_sql CLOB;
  v_columns CLOB;
BEGIN
  -- Получаем список столбцов из метаданных
  SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)
  INTO v_columns
  FROM user_tab_columns
  WHERE table_name = 'MONTHLY_METRICS'
    AND column_name LIKE 'M%'; -- Например, месяцы M01, M02 и т.д.
  
  -- Формируем динамический запрос
  v_sql := 'SELECT metric_id, month, value
            FROM monthly_metrics
            UNPIVOT (
              value
              FOR month
              IN (' || v_columns || ')
            )';
  
  -- Выполняем запрос (например, через EXECUTE IMMEDIATE или возвращая курсор)
  EXECUTE IMMEDIATE v_sql;
END;

DECLARE

v_sql CLOB;

v_columns CLOB;

BEGIN

-- Получаем список столбцов из метаданных

SELECT LISTAGG(column_name, ', ') WITHIN GROUP (ORDER BY column_id)

INTO v_columns

FROM user_tab_columns

WHERE table_name = 'MONTHLY_METRICS'

AND column_name LIKE 'M%'; -- Например, месяцы M01, M02 и т.д.

-- Формируем динамический запрос

v_sql := 'SELECT metric_id, month, value

FROM monthly_metrics

UNPIVOT (

value

FOR month

IN (' || v_columns || ')

)';

-- Выполняем запрос (например, через EXECUTE IMMEDIATE или возвращая курсор)

EXECUTE IMMEDIATE v_sql;

END;

Комбинирование с PIVOT

-- Преобразование и обратное преобразование данных
WITH pivoted_data AS (
  SELECT *
  FROM (SELECT department_id, job_id, salary FROM employees)
  PIVOT (AVG(salary) FOR job_id IN ('IT_PROG' AS it, 'SA_REP' AS sales))
)
SELECT department_id, job_type, avg_salary
FROM pivoted_data
UNPIVOT (
  avg_salary
  FOR job_type
  IN (it AS 'IT', sales AS 'SALES')
);

-- Преобразование и обратное преобразование данных

WITH pivoted_data AS (

SELECT *

FROM (SELECT department_id, job_id, salary FROM employees)

PIVOT (AVG(salary) FOR job_id IN ('IT_PROG' AS it, 'SA_REP' AS sales))

)

SELECT department_id, job_type, avg_salary

FROM pivoted_data

UNPIVOT (

avg_salary

FOR job_type

IN (it AS 'IT', sales AS 'SALES')

);

UNPIVOT с агрегацией

-- Группировка после преобразования
SELECT product_category, metric_name, AVG(metric_value)
FROM product_metrics_wide
UNPIVOT (
  metric_value
  FOR metric_name
  IN (revenue AS 'REV', cost AS 'COST', margin AS 'MARGIN')
)
GROUP BY product_category, metric_name;

-- Группировка после преобразования

SELECT product_category, metric_name, AVG(metric_value)

FROM product_metrics_wide

UNPIVOT (

metric_value

FOR metric_name

IN (revenue AS 'REV', cost AS 'COST', margin AS 'MARGIN')

)

GROUP BY product_category, metric_name;

Часто задаваемые вопросы

Вопрос:

Как обработать столбцы с разными типами данных?

Ответ:

Oracle требует, чтобы все столбцы в предложении IN имели совместимые типы данных. Если типы разные, используйте явное приведение типов:

SELECT id, field_name, value
FROM mixed_data
UNPIVOT (
  value
  FOR field_name
  IN (
    TO_CHAR(number_field) AS 'NUMBER_FIELD',
    date_field AS 'DATE_FIELD',
    text_field AS 'TEXT_FIELD'
  )
);

SELECT id, field_name, value

FROM mixed_data

UNPIVOT (

value

FOR field_name

IN (

TO_CHAR(number_field) AS 'NUMBER_FIELD',

date_field AS 'DATE_FIELD',

text_field AS 'TEXT_FIELD'

)

);

Вопрос:

Есть ли ограничение на количество преобразуемых столбцов?

Ответ:

Теоретическое ограничение — 1000 столбцов, но на практике лучше не превышать несколько десятков из-за снижения производительности.

Вопрос:

Как преобразовать только часть столбцов таблицы?

Ответ:

В предложении IN укажите только нужные столбцы. Остальные останутся без изменений:

SELECT id, category, metric, value
FROM products
UNPIVOT (
  value
  FOR metric
  IN (price, weight, length)
);

SELECT id, category, metric, value

FROM products

UNPIVOT (

value

FOR metric

IN (price, weight, length)

);

Альтернативы UNPIVOT

Для версий Oracle до 11g или сложных сценариев можно использовать:

1. UNION ALL подход:

SELECT id, 'COL1' AS col_name, col1 AS value FROM table
UNION ALL
SELECT id, 'COL2' AS col_name, col2 AS value FROM table

SELECT id, 'COL1' AS col_name, col1 AS value FROM table

UNION ALL

SELECT id, 'COL2' AS col_name, col2 AS value FROM table

2. Модель WITH:

WITH long_data AS (
  SELECT id, 1 AS col_num, col1 AS value FROM table
  UNION ALL
  SELECT id, 2 AS col_num, col2 AS value FROM table
)
SELECT id, 
       DECODE(col_num, 1, 'COL1', 2, 'COL2') AS col_name,
       value
FROM long_data;

WITH long_data AS (

SELECT id, 1 AS col_num, col1 AS value FROM table

UNION ALL

SELECT id, 2 AS col_num, col2 AS value FROM table

)

SELECT id,

DECODE(col_num, 1, 'COL1', 2, 'COL2') AS col_name,

value

FROM long_data;

Производительность и лучшие практики

UNPIVOT обычно эффективнее, чем ручные решения с UNION ALL
Для больших таблиц добавляйте фильтрацию до UNPIVOT
Избегайте UNPIVOT для таблиц с сотнями столбцов
Используйте алиасы для понятных имен в результирующем наборе
Для сложных преобразований комбинируйте с другими операциями (JOIN, GROUP BY)

Заключение

Оператор UNPIVOT — это мощный инструмент для преобразования данных из широкого в длинный формат, что часто требуется для анализа и отчетности. Понимание его работы особенно важно при:

Подготовке данных для визуализации
Трансформации унаследованных систем
Нормализации денормализованных данных
Решении задач ETL

Для более сложных сценариев рассмотрите:

Динамический SQL для работы с изменяемыми структурами
Комбинацию с PIVOT для сложных преобразований
Использование временных таблиц для промежуточных результатов