PIVOT в Oracle SQL — как превращать строки в столбцы для аналитики

PIVOT в Oracle SQL. Введение

PIVOT в Oracle SQL позволяет трансформировать строки в столбцы, агрегируя данные по определённым категориям. Это мощный инструмент для сводной аналитики и отчетности, позволяющий на лету менять представление данных в табличном виде.

🖋️ Синтаксис оператора PIVOT

SELECT *
FROM (
  SELECT столбец_1, столбец_2, значение
  FROM таблица
)
PIVOT (
  агрегатная_функция(значение)
  FOR столбец_2 IN (значение1, значение2, ...)
);

Пример:

SELECT * FROM (
  SELECT department_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('IT_PROG', 'SA_REP', 'FI_ACCOUNT')
);

🔄 Где используется PIVOT в Oracle SQL

Построение сводных таблиц
Преобразование категориальных строк в столбцы
Создание табличных отчётов
Упрощение многомерной аналитики
Группировка значений по категориям

📊 Примеры использования PIVOT в Oracle SQL (1–50 из 50)

Поворот зарплат по job_id:

SELECT * FROM (
  SELECT department_id, job_id, salary FROM employees
)
PIVOT (
  SUM(salary) FOR job_id IN ('IT_PROG', 'SA_REP', 'FI_ACCOUNT')
);

Подсчёт количества сотрудников по департаментам:

SELECT * FROM (
  SELECT job_id, department_id FROM employees
)
PIVOT (
  COUNT(department_id) FOR department_id IN (10, 20, 30)
);

Сумма продаж по категориям:

SELECT * FROM (
  SELECT category, region, amount FROM sales
)
PIVOT (
  SUM(amount) FOR category IN ('Books', 'Electronics', 'Clothing')
);

Максимальное значение по кварталам:

SELECT * FROM (
  SELECT year, quarter, revenue FROM financials
)
PIVOT (
  MAX(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);

Поворот количества заказов по статусу:

SELECT * FROM (
  SELECT order_id, status FROM orders
)
PIVOT (
  COUNT(order_id) FOR status IN ('NEW', 'SHIPPED', 'RETURNED')
);

Количество заказов по клиентам:

SELECT * FROM (
  SELECT customer_id, status FROM orders
)
PIVOT (
  COUNT(status) FOR status IN ('NEW', 'PROCESSING', 'CANCELLED')
);

Средняя оценка по категориям продуктов:

SELECT * FROM (
  SELECT product_id, category, rating FROM reviews
)
PIVOT (
  AVG(rating) FOR category IN ('Food', 'Electronics', 'Clothing')
);

Сумма трат по месяцам:

SELECT * FROM (
  SELECT user_id, TO_CHAR(purchase_date, 'MON') AS month, amount FROM purchases
)
PIVOT (
  SUM(amount) FOR month IN ('JAN', 'FEB', 'MAR')
);

Количество регистраций по странам:

SELECT * FROM (
  SELECT country, gender FROM users
)
PIVOT (
  COUNT(gender) FOR gender IN ('M', 'F')
);

Минимальная цена по регионам:

SELECT * FROM (
  SELECT region, product_id, price FROM products
)
PIVOT (
  MIN(price) FOR region IN ('North', 'South', 'West')
);

Количество задач по статусам:

SELECT * FROM (
  SELECT team, status FROM tasks
)
PIVOT (
  COUNT(*) FOR status IN ('TO_DO', 'DOING', 'DONE')
);

Средняя зарплата по должностям:

SELECT * FROM (
  SELECT department_id, job_title, salary FROM employees
)
PIVOT (
  AVG(salary) FOR job_title IN ('Engineer', 'Manager', 'Clerk')
);

Продажи по странам:

SELECT * FROM (
  SELECT country, year, amount FROM sales
)
PIVOT (
  SUM(amount) FOR year IN (2022, 2023)
);

Сводка заказов по типу оплаты:

SELECT * FROM (
  SELECT store_id, payment_type, order_id FROM orders
)
PIVOT (
  COUNT(order_id) FOR payment_type IN ('CASH', 'CARD', 'ONLINE')
);

Максимальная скорость доставки:

SELECT * FROM (
  SELECT courier_id, region, delivery_time FROM deliveries
)
PIVOT (
  MAX(delivery_time) FOR region IN ('North', 'South')
);

Среднее количество участников по мероприятиям:

SELECT * FROM (
  SELECT event_id, day, attendees FROM schedule
)
PIVOT (
  AVG(attendees) FOR day IN ('Monday', 'Tuesday')
);

Расходы по типу транспорта:

SELECT * FROM (
  SELECT department, transport_type, cost FROM expenses
)
PIVOT (
  SUM(cost) FOR transport_type IN ('Taxi', 'Train', 'Flight')
);

Количество повторных покупок:

SELECT * FROM (
  SELECT customer_id, month, is_repeat FROM purchases
)
PIVOT (
  COUNT(is_repeat) FOR is_repeat IN ('Y', 'N')
);

Статистика по источникам трафика:

SELECT * FROM (
  SELECT campaign, source, clicks FROM marketing
)
PIVOT (
  SUM(clicks) FOR source IN ('Email', 'Social', 'Search')
);

Количество книг по жанрам:

SELECT * FROM (
  SELECT genre, year, book_id FROM library
)
PIVOT (
  COUNT(book_id) FOR year IN (2020, 2021, 2022)
);

Количество новых пользователей по месяцам:

SELECT * FROM (
  SELECT user_id, TO_CHAR(reg_date, 'MON') AS month FROM users
)
PIVOT (
  COUNT(user_id) FOR month IN ('JAN', 'FEB', 'MAR')
);

Количество тикетов по приоритетам:

SELECT * FROM (
  SELECT team_id, priority FROM support_tickets
)
PIVOT (
  COUNT(*) FOR priority IN ('Low', 'Medium', 'High')
);

Поворот по дням недели:

SELECT * FROM (
  SELECT employee_id, TO_CHAR(work_date, 'DAY') AS weekday, hours FROM timesheets
)
PIVOT (
  SUM(hours) FOR weekday IN ('MONDAY', 'TUESDAY', 'WEDNESDAY')
);

Продажи по категориям:

SELECT * FROM (
  SELECT product_id, category, sales FROM product_sales
)
PIVOT (
  SUM(sales) FOR category IN ('Tech', 'Fashion', 'Food')
);

Время ожидания по агентам:

SELECT * FROM (
  SELECT agent_id, day, wait_time FROM call_logs
)
PIVOT (
  AVG(wait_time) FOR day IN ('MON', 'TUE', 'WED')
);

Сводка по направлениям доставки:

SELECT * FROM (
  SELECT hub_id, direction, package_id FROM shipping
)
PIVOT (
  COUNT(package_id) FOR direction IN ('Inbound', 'Outbound')
);

Продуктивность по неделям:

SELECT * FROM (
  SELECT employee_id, week, tasks_done FROM productivity
)
PIVOT (
  SUM(tasks_done) FOR week IN (1, 2, 3, 4)
);

Сводка по сменам:

SELECT * FROM (
  SELECT worker_id, shift, hours FROM shifts
)
PIVOT (
  SUM(hours) FOR shift IN ('Morning', 'Evening', 'Night')
);

Сводка результатов опроса:

SELECT * FROM (
  SELECT respondent_id, answer, question_id FROM survey
)
PIVOT (
  COUNT(answer) FOR answer IN ('Yes', 'No', 'Maybe')
);

Поворот данных по версии приложения:

SELECT * FROM (
  SELECT user_id, app_version, usage_time FROM app_usage
)
PIVOT (
  AVG(usage_time) FOR app_version IN ('1.0', '2.0', '3.0')
);

Поворот по валютам:

SELECT * FROM (
  SELECT branch_id, currency, amount FROM transactions
)
PIVOT (
  SUM(amount) FOR currency IN ('USD', 'EUR', 'GBP')
);

Отчёт по меткам задач:

SELECT * FROM (
  SELECT task_id, tag, effort FROM task_tags
)
PIVOT (
  SUM(effort) FOR tag IN ('Urgent', 'Normal', 'Optional')
);

Анализ кликов по устройствам:

SELECT * FROM (
  SELECT campaign_id, device_type, clicks FROM ad_stats
)
PIVOT (
  SUM(clicks) FOR device_type IN ('Desktop', 'Mobile', 'Tablet')
);

Количество инцидентов по типу:

SELECT * FROM (
  SELECT incident_id, type FROM incidents
)
PIVOT (
  COUNT(*) FOR type IN ('Security', 'Hardware', 'Software')
);

Поворот по категориям клиентов:

SELECT * FROM (
  SELECT client_id, client_type, order_value FROM orders
)
PIVOT (
  SUM(order_value) FOR client_type IN ('VIP', 'Regular', 'New')
);

Сводка по источникам данных:

SELECT * FROM (
  SELECT record_id, source, count FROM log_sources
)
PIVOT (
  SUM(count) FOR source IN ('Internal', 'External', 'ThirdParty')
);

Количество проектов по фазам:

SELECT * FROM (
  SELECT project_id, phase FROM project_phases
)
PIVOT (
  COUNT(*) FOR phase IN ('Planning', 'Execution', 'Closure')
);

Анализ по типу подписки:

SELECT * FROM (
  SELECT user_id, subscription_type, duration FROM subscriptions
)
PIVOT (
  AVG(duration) FOR subscription_type IN ('Free', 'Pro', 'Enterprise')
);

Расходы по каналам рекламы:

SELECT * FROM (
  SELECT ad_id, channel, cost FROM marketing_budget
)
PIVOT (
  SUM(cost) FOR channel IN ('TV', 'Radio', 'Web')
);

Количество посещений по времени суток:

SELECT * FROM (
  SELECT visitor_id, time_of_day, visit_id FROM visits
)
PIVOT (
  COUNT(visit_id) FOR time_of_day IN ('Morning', 'Afternoon', 'Evening')
);

Анализ обращений по категориям:

SELECT * FROM (
  SELECT request_id, category, duration FROM support_requests
)
PIVOT (
  AVG(duration) FOR category IN ('Billing', 'Technical', 'General')
);

Сводка ошибок по уровню:

SELECT * FROM (
  SELECT error_id, level, count FROM error_logs
)
PIVOT (
  SUM(count) FOR level IN ('Info', 'Warning', 'Critical')
);

Время реакции по дням недели:

SELECT * FROM (
  SELECT team_id, TO_CHAR(response_time, 'DAY') AS day_name, duration FROM responses
)
PIVOT (
  AVG(duration) FOR day_name IN ('MONDAY', 'TUESDAY', 'WEDNESDAY')
);

Сумма заказов по регионам:

SELECT * FROM (
  SELECT region, order_id, total FROM orders
)
PIVOT (
  SUM(total) FOR region IN ('East', 'West', 'Central')
);

Количество регистраций по каналам:

SELECT * FROM (
  SELECT user_id, signup_channel FROM users
)
PIVOT (
  COUNT(*) FOR signup_channel IN ('Organic', 'Referral', 'Ads')
);

Анализ по стадиям продаж:

SELECT * FROM (
  SELECT opportunity_id, stage, value FROM crm_data
)
PIVOT (
  SUM(value) FOR stage IN ('Prospect', 'Qualified', 'Closed')
);

Количество поездок по маршрутам:

SELECT * FROM (
  SELECT trip_id, route, fare FROM transportation
)
PIVOT (
  COUNT(*) FOR route IN ('Northbound', 'Southbound')
);

Поворот количества покупок по возрасту:

SELECT * FROM (
  SELECT user_id, age_group, purchases FROM demographics
)
PIVOT (
  SUM(purchases) FOR age_group IN ('18-25', '26-35', '36-50')
);

Сводка по категориям инвентаря:

SELECT * FROM (
  SELECT item_id, category, quantity FROM inventory
)
PIVOT (
  SUM(quantity) FOR category IN ('Office', 'IT', 'Furniture')
);

Количество возвратов по причинам:

SELECT * FROM (
  SELECT return_id, reason, amount FROM returns
)
PIVOT (
  COUNT(*) FOR reason IN ('Defective', 'Wrong item', 'No longer needed')
);

🧩 Заключение

PIVOT — это удобный инструмент для создания сводных таблиц в SQL, превращая строки в столбцы и делая отчёты наглядными. Он избавляет от множества ручных манипуляций и позволяет сосредоточиться на аналитике.

💡 Запомни:

PIVOT работает только с агрегатными функциями
Вложенный подзапрос — обязательный
IN (...) задаёт список будущих столбцов
Имена столбцов можно задать явно через AS