Создание таблицы измерений из иерархической таблицы

SQL   PostgreSQL   Добряки  

Как сделать из иерархической таблицы таблицу измерений с иерархией в столбцах? Обращаю внимание, что тип text не является стандартным для SQL языка. Пример проверял на PostgreSQL (8.4).

Создаем таблицу:

create table test_rec (
 id int,
 pid int,
 descr varchar(256)
);

 

Заполняем таблицу данными test_rec:

 

insert into test_rec values (1,NULL, 'Россия');
insert into test_rec values (2,1, 'Москва');
insert into test_rec values (3,2, 'Головной');
insert into test_rec values (4,1, 'Приморье');
insert into test_rec values (5,4, 'Офис 1');
insert into test_rec values (6,4, 'Офис 2');

 Делаем таблицу измерений к схеме “Снежинка”. Формируем запрос.

select  id, levels, trim(Descr) as Descr, trim(arr[1]) as group_1, trim(arr[2]) as group_2, trim(arr[3]) as group_3, trim(arr[4]) as group_4, trim(arr[5]) as group_5, trim(arr[6]) as group_6, trim(arr[7]) as group_7
from (select id,  pid, levels, Descr, regexp_split_to_array(group_tree,';') as arr
    	from (with recursive tree
              	as (
                    	select id, pid , 1 as levels, Descr::text, Descr::text as group_tree
     			from test_rec
    			where pid isnull
			union all
    			select  test_rec.id, test_rec.pid, levels + 1 , cast(test_rec.Descr as text), cast (tree.group_tree ||';'|| test_rec.Descr as text)
    			from test_rec
     				inner join tree  on tree.id = test_rec.pid)
		select * from tree) 
	as tree) 
as temp;

 

Разберем только основные моменты:

  1. Прочитать подробно об обходе with recursive с примером можно тут: (Пример работы с рекурсивными запросами в PostgreSQL (8.4))
  2. Накопление пути идет командой cast (tree.group_tree ||';'|| test_rec.Descr as text).
  3. Разделение по столбцам идет в два этапа:

    • Разбиение на массив regexp_split_to_array(group_tree,';') as arr.

    • Разбиение массива по столбцам trim(arr[i]), где trim - обрезка пробелов, а i - адрес массива arr.

Вывод запроса:

idlevelsdescrgroup_1group_2group_3group_4group_5group_6group_7
1 1 Россия Россия (null) (null) (null) (null) (null) (null)
4 2 Приморье Россия Приморье (null) (null) (null) (null) (null)
2 2 Москва Россия Москва (null) (null) (null) (null) (null)
6 3 Офис 2 Россия Приморье Офис 2 (null) (null) (null) (null)
5 3 Офис 1 Россия Приморье Офис 1 (null) (null) (null) (null)
3 3 Головной Россия Москва Головной (null) (null) (null) (null)

Минусы решения - заранее заданное количество иерархии (в этом примере 7).

Такими же запросами я разбивал справочник “Номенклатура” для стыковки с таблицей фактов для схемы “Звезда”. Хотя системы аналитики умеют работать и с иерархическими таблицами [2], интересно протестировать, какое из решений удобнее.

Ссылки на материалы:

  1. Пример использования разбиения строки по столбцам: http://www.sql.ru/forum/1027709/razbit-na-stolbcy
  2. "Mondrian 3.0.4 Technical Guide".
  3. Создание измерения времени на PostgreSQL https://anonymousbi.wordpress.com/tag/postgresql/
Подробности
Опубликовано: 20 Сентябрь 2013
Просмотров: 5082
   
   
© BIWED.RU