MySQL
Динамическая сводная таблица с использованием подготовленного заявления

Un-pivot динамический набор столбцов на основе условия

Следующий пример - очень полезная основа, когда вы пытаетесь преобразовать данные транзакции в неперевернутые данные для целей BI / отчетности, где размеры, которые должны быть не подвержены повороту, могут иметь динамический набор столбцов.

В нашем примере мы полагаем, что таблица необработанных данных содержит данные оценки сотрудников в виде отмеченных вопросов.

Таблица необработанных данных следующая:

create table rawdata

(
 PersonId VARCHAR(255)
,Question1Id INT(11)
,Question2Id INT(11)
,Question3Id INT(11)
)  

Таблица rawdata является временной таблицей как частью процедуры ETL и может иметь различное количество вопросов. Цель состоит в том, чтобы использовать одну и ту же процедуру без поворота для произвольного количества Вопросов, а именно для столбцов, которые будут не поворачиваться.

Ниже приведен пример игрушечной таблицы:

Известный статический способ отключить данные в MYSQL - это использовать UNION ALL:

create table unpivoteddata

(

 PersonId VARCHAR(255)
,QuestionId VARCHAR(255)
,QuestionValue INT(11)

);

INSERT INTO unpivoteddata SELECT PersonId, 'Question1Id' col, Question1Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question2Id' col, Question2Id 
FROM rawdata
UNION ALL
SELECT PersonId, 'Question3Id' col, Question3Id 
FROM rawdata; 

В нашем случае мы хотим определить способ отказа от произвольного количества столбцов QuestionId. Для этого нам нужно выполнить подготовленный оператор, который представляет собой динамический выбор нужных столбцов. Чтобы иметь возможность выбирать, какие столбцы должны быть не развернуты, мы будем использовать оператор GROUP_CONCAT, и мы выберем столбцы, для которых тип данных имеет значение «int». В GROUP_CONCAT мы также включаем все дополнительные элементы нашей инструкции SELECT для выполнения.

set @temp2 = null; 

SELECT GROUP_CONCAT(' SELECT ', 'PersonId',',','''',COLUMN_NAME,'''', ' col     ',',',COLUMN_NAME,' FROM rawdata' separator ' UNION ALL' ) FROM     INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'rawdata' AND DATA_TYPE = 'Int' INTO     @temp2;

select @temp2;

В другом случае мы могли бы выбрать столбцы, имена столбцов которых соответствуют шаблону, например, вместо

DATA_TYPE = 'Int'

использование

COLUMN_NAME LIKE 'Question%'

или что-то подходящее, которое можно контролировать через фазу ETL.

Подготовленное заявление завершается следующим образом:

set @temp3 = null;

select concat('INSERT INTO unpivoteddata',@temp2) INTO @temp3;

select @temp3;

prepare stmt FROM @temp3;
execute stmt;
deallocate prepare stmt;

Таблица неавторизованных данных следующая:

SELECT * FROM unpivoteddata

Выбор столбцов в соответствии с условием, а затем создание подготовленного оператора - эффективный способ динамически разворачивать данные.