Тип таблицы CONNECT PIVOT

Этот тип таблицы может быть использован для преобразования результата другой таблицы или представления (называемой исходной таблицей) в сводную таблицу по столбцам «pivot» и «facts». Сводная таблица — это удобный инструмент для отчётов, который сортирует и суммирует (по умолчанию) независимо от исходной структуры данных в исходной таблице.

Например, предположим, что у вас есть следующая таблица «Expenses»:

Преобразование содержимого таблицы, используя поля «Кто» и «Неделя» для левых столбцов, и поле «Что» для верхних заголовков, а также суммирование полей «Сумма» для каждой ячейки в новой таблице, даёт следующий желаемый результат:

Обратите внимание, что SQL позволяет получить тот же результат, но по-другому, используя предложение «group by»:

select who, week, what, sum(amount) from expenses
       group by who, week, what;

Однако нет способа получить представленную выше сводную структуру только с помощью SQL. Даже использование встроенного SQL-программирования для некоторых СУБД не совсем просто и автоматизировано.

Тип сводной таблицы CONNECT делает это намного проще.

Использование типа сводных таблиц

Чтобы получить результат, показанный в примере выше, просто определите его как новую таблицу с помощью оператора:

create table pivex
engine=connect table_type=pivot tabname=expenses;

Теперь вы можете использовать её как любую другую таблицу, например, для отображения результата, показанного выше, просто скажите:

select * from pivex;

Реализация типа сводной таблицы CONNECT выполняет большую часть работы по преобразованию исходной таблицы:

1. Нахождение столбца «Facts», по умолчанию последний столбец исходной таблицы. Нахождение столбцов «Facts» или «Pivot» работает только для сводных таблиц на основе таблиц. Они не работают для сводных таблиц на основе представлений или srcdef, для которых они должны быть явно указаны.
2. Нахождение столбца «Pivot», по умолчанию оставшийся столбец.
3. Выбор агрегатной функции для использования, «SUM» по умолчанию.
4. Построение и выполнение «Group By» по столбцу «Facts», получение результата в памяти.
5. Получение всех уникальных значений в столбце «Pivot» и определение столбца «Data» для каждого.
6. Распространение результата промежуточной таблицы памяти в итоговую таблицу.

Если требуется другой результат, доступны параметры Create Table для изменения значений по умолчанию, используемых Pivot. Например, если мы хотим отобразить средние расходы для каждого человека и продукта, распределённые по столбцам для каждой недели, используйте следующий оператор:

create table pivex2
engine=connect table_type=pivot tabname=expenses
option_list='PivotCol=Week,Function=AVG';

Теперь, сказав:

select * from pivex2;

Будет отображена результирующая таблица:

Ограничение столбцов в сводной таблице

Предположим, что нам нужна сводная таблица расходов, суммирующая расходы всех людей и продуктов независимо от недели покупки. Это можно сделать, просто удалив столбец недели из списка столбцов в таблице pivex.

alter table pivex drop column week;

Результат, который мы получаем от новой таблицы:

Примечание: Ограничение столбцов также необходимо, когда исходная таблица содержит дополнительные столбцы, которые не должны быть частью сводной таблицы. Это особенно верно для ключевых столбцов, которые препятствуют правильному группированию.

Синтаксис оператора CREATE TABLE для сводных таблиц

Оператор CREATE TABLE для сводных таблиц использует следующий синтаксис:

create table pivot_table_name
[(column_definition)]
engine=CONNECT table_type=PIVOT
{tabname='source_table_name' | srcdef='source_table_def'}
[option_list='pivot_table_option_list'];

Определение столбцов имеет два набора столбцов:

1. Набор столбцов, принадлежащих исходной таблице, не включая столбцы «facts» и «pivot».
2. Столбцы «Data», получающие значения агрегированных столбцов «facts», имеющие названия из значений столбца «pivot». Они обозначаются опцией «flag».

Доступные опции и подопции для сводных таблиц:

• : Эти опции должны быть указаны в OPTION_LIST.

Дополнительные параметры доступа

Существует четыре случая, когда Pivot должен вызывать сервер, содержащий исходную таблицу, или на котором должен быть выполнен оператор SrcDef:

1. Исходная таблица не является таблицей CONNECT. 2. Указана опция SrcDef. 3. Исходная таблица находится на другом сервере. 4. Столбцы не указаны.

По умолчанию Pivot пытается вызвать текущий сервер, используя host=localhost, user=root без пароля и port=3306. Однако это может не соответствовать требованиям, особенно если у локального пользователя root есть пароль, в этом случае может появиться сообщение об ошибке «access denied» при создании или использовании сводной таблицы.

Укажите опции host, user, password и/или port в списке параметров, чтобы переопределить параметры подключения по умолчанию, используемые для доступа к исходной таблице, получения спецификаций столбцов, выполнения сгенерированного запроса group by или SrcDef.

Определение сводной таблицы

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

1. Из существующей таблицы или представления. 2. Непосредственно указав SQL-оператор, возвращающий результат в Pivot.

Определение сводной таблицы из исходной таблицы

Стандартная опция таблицы tabname используется для указания имени исходной таблицы или представления.

Для таблиц внутренний Group By будет генерироваться внутри, за исключением случаев, когда опция GROUPBY задана как true. Делайте это только тогда, когда таблица или представление имеет правильный формат GROUP BY.

Непосредственное определение источника сводной таблицы в SQL

В качестве альтернативы, внутренний источник может быть непосредственно определён с помощью опции SrcDef, которая должна иметь правильный формат group by.

Как мы видели выше, правильная сводная таблица создаётся из внутренней промежуточной таблицы, полученной в результате выполнения оператора GROUP BY. Во многих случаях проще или желательно указать это напрямую при создании сводной таблицы. Это может быть потому, что источник является результатом сложного процесса, включающего фильтрацию и/или объединение таблиц.

Для этого используйте опцию SrcDef, часто заменяя все остальные опции. Например, предположим, что в первом примере нас интересуют только недели 4 и 5. Мы можем, конечно, отобразить это, используя:

select * from pivex where week in (4,5);

Однако, что если эта таблица огромна? В этом случае правильный способ сделать это — определить сводную таблицу следующим образом:

create table pivex4
engine=connect table_type=pivot
option_list='PivotCol=what,FncCol=amount'
SrcDef='select who, week, what, sum(amount) from expenses
where week in (4,5) group by who, week, what';

Если ваша исходная таблица содержит миллионы записей, а вы планируете преобразовать только небольшую часть из них, это существенно повлияет на производительность. Кроме того, вы имеете полную свободу использовать выражения, скалярные функции, псевдонимы, объединения, условия where и having в вашем SQL-запросе. Единственное ограничение заключается в том, что вы несете ответственность за то, чтобы результат этого запроса имел правильный формат для обработки сводки.

Использование SrcDef также позволяет использовать выражения и/или скалярные функции. Например:

create table xpivot (
Who char(10) not null,
What char(12) not null,
First double(8,2) flag=1,
Middle double(8,2) flag=1,
Last double(8,2) flag=1)
engine=connect table_type=PIVOT
option_list='PivotCol=wk,FncCol=amnt'
Srcdef='select who, what, case when week=3 then ''First'' when
week=5 then ''Last'' else ''Middle'' end as wk, sum(amount) *
6.56 as amnt from expenses group by who, what, wk';

Теперь запрос:

select * from xpivot;

Выведет результат:

Примечание 1: чтобы избежать нескольких строк с одинаковыми значениями фиксированных столбцов, в SrcDef обязательно следует размещать столбец сводки в конце списка group by.

Примечание 2: в операторе создания SrcDef обязательно необходимо присваивать псевдонимы столбцам, содержащим выражения, чтобы они распознавались другими опциями.

Примечание 3: в операторе выбора SrcDef кавычки необходимо экранировать, поскольку весь оператор передаётся MariaDB в кавычках. В качестве альтернативы, укажите его в двойных кавычках.

Примечание 4: Мы могли бы оставить CONNECT для определения столбцов. Однако, поскольку они определены по отсортированным именам, столбец Средний был помещен в конец.

Указание столбцов, соответствующих столбцу сводки

Эти столбцы должны быть названы по значениям, присутствующим в столбце «pivot». Например, предположим, что у нас есть следующая таблица pet:

Преобразование его с использованием Порода в качестве столбца сводки выполняется с помощью:

create table pivet
engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1';

Это даёт результат:

Кстати, это вам о чём-то говорит? Это показывает, что сводные таблицы в некотором роде выполняют обратные действия по сравнению с таблицами OCCUR.

Мы можем альтернативно определить конкретно столбцы таблицы, но что произойдёт, если столбец Сводки содержит значения, не соответствующие столбцу «данных»? Существует три случая в зависимости от указанных параметров и флагов.

Первый случай: Если не указано никаких конкретных параметров, это ошибка, и при попытке отобразить таблицу запрос прервётся с сообщением об ошибке, указывающим, что было встречено несоответствующее значение. Обратите внимание, что поскольку список столбцов формируется при создании таблицы, это может произойти, если в исходную таблицу вставлены строки, содержащие новые значения для столбца сводки. В этом случае следует пересоздать таблицу или вручную добавить новые столбцы в сводную таблицу.

Второй случай: был указан параметр accept. Например:

create table xpivet2 (
name varchar(12) not null,
dog int not null default 0 flag=1,
cat int not null default 0 flag=1)
engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1,Accept=1';

Ошибка не будет поднята, и несоответствующие значения будут проигнорированы. Эта таблица будет отображена как:

Третий случай: был указан столбец «dump» со значением флага, равным 2. Все несоответствующие значения будут добавлены в этот столбец. Например:

create table xpivet (
name varchar(12) not null,
dog int not null default 0 flag=1,
cat int not null default 0 flag=1,
other int not null default 0 flag=2)
engine=connect table_type=pivot tabname=pet
option_list='PivotCol=race,groupby=1';

Эта таблица будет отображена как:

Преобразование больших исходных таблиц

Это иногда может быть рискованно. Если столбец сводки содержит слишком много различных значений, результирующая таблица может иметь слишком много столбцов. Во всех случаях, процесс поиска уникальных значений при создании таблицы или выполнении группировки при её использовании может быть очень долгим и иногда может завершиться неудачно из-за недостатка памяти.

Ограничения с помощью условия where должны применяться к исходной таблице при создании сводной таблицы, а не к самой сводной таблице. Это можно сделать, создав промежуточную таблицу или используя в качестве источника представление или параметр srcdef.