Сводные таблицы Greenplum

Всем привет!

Каждый пользователь различных СУБД сталкивался с потребностью в систематизации данных через такой наглядный инструмент, как сводные таблицы. Практически в любой СУБД реализована возможность формирования сводных таблиц, например, для pivot в SQL Server и Oracle выглядит следующим образом:

with n as ( select coalesce(n.country, 'total_sum') as country, coalesce(n.goods, 'total_sum') as goods, sum(n.sum) as agg from new1 as n group by cube(n.country, n.goods) ) select * from n pivot ( count(agg) for goods in ("Зерно", "Бананы", "Лимоны", "Картофель", "Мандарины", "Мясо", "Яблоки") ) pvt

Результатом выполнения запроса будет сводная таблица:

В СУБД Greenplum существует несколько способов для формирования сводной таблицы.

Первый из них это функция crosstab, являющаяся частью расширения tablefunc в PostgreSQL. Чтобы вызвать функцию пишем запрос:

create extension tablefunc;

И получаем ошибку:

Как видно из текста ошибки, используется версия Greenplum 6.20.3. По информации размещенной здесь используется версия PostgreSQL 9.4, которая согласно документации не поддерживает функцию crosstab (актуальные версии можно посмотреть в документации). Поэтому, я использую оператор case.

Продемонстрирую это на примере тестовой таблицы, данные которой имеют формат text:

select * from s_grnplm_ld_da_sandbox.pivot

Создам запрос, содержащий конструкцию case … when и функции суммирования:

select coalesce (country) as "country", sum(case when goods = 'Картофель' then sum end) as "A", sum(sum) as "total" from s_grnplm_ld_da_sandbox.pivot group by country

Результатом выполнения запроса будет ошибка формата данных (система пытается просуммировать текстовые значения), более подробно написано в описании функции:

Если поменять тип данных в исходной таблице на int4, то запрос сработает, система просуммирует данные, но результат не годится для дальнейшей обработки. Хочу обратить внимание на то, что не всегда можно успешно изменить формат на числовой без потери данных и/или ошибок:

Для корректного отображения информации необходимо отредактировать запрос. Также вместо case … when можно использовать оператор PostgreSQL filter. Отредактирую запрос с учётом изложенных выше комментариев:

select coalesce (country) as "country", count(country) filter ( where goods = 'Бананы') as "Бананы", count(country) filter ( where goods = 'Яблоки') as "Яблоки", count(country) filter ( where goods = 'Мандарины') as "Мандарины", count(country) filter ( where goods = 'Зерно') as "Зерно", count(country) filter ( where goods = 'Картофель') as "Картофель", count(country) filter ( where goods = 'Лимоны') as "Лимоны", count(country) filter ( where goods = 'Мясо') as "Мясо", sum(sum) from s_grnplm_ld_da_sandbox.pivot group by country

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

Таким образом, я предложил своё рабочее решение по формированию сводных таблиц посредством использования СУБД Greenplum. Хотелось бы отметить, что указанный в публикации способ является результатом потребности в оперативном решении возникшей задачи он достаточно прост в использовании и понимании этапов самого процесса и не требует особых навыков работы с процедурами и функциями.

Буду рад увидеть в комментариях альтернативные решения или возможности по развитию моего способа формирования сводных таблиц.