Сводные таблицы 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. Хотелось бы отметить, что указанный в публикации способ является результатом потребности в оперативном решении возникшей задачи он достаточно прост в использовании и понимании этапов самого процесса и не требует особых навыков работы с процедурами и функциями.
Буду рад увидеть в комментариях альтернативные решения или возможности по развитию моего способа формирования сводных таблиц.
