tablefunc — функции, возвращающие таблицы (crosstab и другие)

примечание

Эта страница переведена при помощи нейросети GigaChat.

Модуль предоставляет набор функций, возвращающих множественные строки (табличные данные). Они полезны как в прикладных целях, так и в роли наглядных примеров для написания собственных функций на языке C, возвращающих табличные результаты.

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

Предоставляемые функции

Таблица «Функции модуля tablefunc» суммирует функции, предоставляемые модулем tablefunc.

Функции модуля tablefunc:

Функция	Описание
`'normal_rand' ('numvals' integer, 'mean' float8, 'stddev' float8) → 'setof float8'`	Генерирует набор случайных значений, распределенных нормально.
`'crosstab' ('sql' text) → 'setof record'`	Создает «поворотную таблицу», содержащую имена строк плюс N столбцов значений, где N определяется типом строки, указанным в вызывающем запросе.
`'crosstabN' ('sql' text) → 'setof table_crosstab_N'`	Создает «поворотную таблицу», содержащую имена строк плюс N столбцов значений. Функции `'crosstab2'`, `'crosstab3'` и `'crosstab4'` предопределены, но можно создать дополнительные функции `'crosstabN'`, как описано ниже.
`'crosstab' ('source_sql' text, 'category_sql' text) → 'setof record'`	Создает «таблицу поворота» со значениями столбцов, указанными во втором запросе.
`'crosstab' ('sql' text, 'N' integer) → 'setof record'`	Устаревшая версия `'crosstab(text)'`. Параметр N теперь игнорируется, поскольку количество столбцов значений всегда определяется вызывающим запросом.
`'connectby' ('relname' text, 'keyid_fld' text, 'parent_keyid_fld' text [, 'orderby_fld' text ], 'start_with' text, 'max_depth' integer [, 'branch_delim' text ]) → 'setof record'`	Создает представление иерархической структуры дерева.

normal_rand

normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8

Функция normal_rand генерирует набор случайных чисел, подчиняющихся нормальному (гауссову) распределению.

Аргумент numvals определяет количество возвращаемых значений.
Параметр mean задает среднее арифметическое для создаваемого распределения.
Значение stddev задает стандартное отклонение, характеризующее разброс данных вокруг среднего значения.

Пример вызова функции, который вернет 1000 случайных величин с математическим ожиданием равным 5 и среднеквадратическим отклонением 3:

test=# SELECT * FROM normal_rand(1000, 5, 3);
     normal_rand
----------------------
     1.56556322244898
     9.10040991424657
     5.36957140345079
   -0.369151492880995
    0.283600703686639
       .
       .
       .
     4.82992125404908
     9.71308014517282
     2.49639286969028
(1000 rows)

crosstab(text)

crosstab(text sql)
crosstab(text sql, int N)

Функция crosstab предназначена для формирования «поворотных» таблиц (pivot tables), в которых данные располагаются горизонтально, а не вертикально. Например, она полезна, когда исходные данные организованы в виде перечня записей, а нужно представить их в форме, удобной для сравнения или агрегации по категориям.

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

которые можно отобразить так:

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

Функция crosstab принимает один обязательный параметр — текстовый запрос SQL, который формирует исходные данные в определенном формате. Результатом работы функции является результирующая таблица, организованная в требуемом поворотно-табличном виде.

Параметр sql представляет собой инструкцию SQL, порождающую сырые данные, которые впоследствии подвергаются трансформации. Запрос должен возвращать три обязательных столбца: row_name, category и value. Ранее использовавшийся параметр N устаревший и игнорируется, так как количество выходящих столбцов значений теперь определяется самим вызывающим запросом.

В итоге полученный набор данных выглядит примерно так:

 row_name    cat    value
----------+-------+-------
  row1      cat1    val1
  row1      cat2    val2
  row1      cat3    val3
  row1      cat4    val4
  row2      cat1    val5
  row2      cat2    val6
  row2      cat3    val7
  row2      cat4    val8

Функция crosstab объявлена для возврата setof record, поэтому фактические имена и типы выходных столбцов должны быть определены в предложении FROM вызывающего оператора SELECT, например:

SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text);

Этот пример создает набор данных, похожий на следующий:

           <== value  columns  ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

Конструкция FROM должна формировать выходной набор данных следующего вида:

Один столбец row_name, имеющий тот же тип данных, что и первый столбец исходного SQL-запроса.
Далее идут N столбцов value, имеющих тот же тип данных, что и третий столбец исходного SQL-запроса. Количество таких столбцов задается произвольно и определяется требованиями пользователя.

Функция crosstab собирает данные следующим образом: для каждой группы входных строк с одинаковым значением row_name формируется одна результирующая строка. Столбцы value заполняются слева направо данными из третьей колонки исходного запроса. Если группа строк исчерпалась раньше, чем заполнены все выходные столбцы, оставшиеся позиции заполняются пустыми значениями. Если строк оказалось больше, чем предусмотрено выходными столбцами, лишние строки опускаются.

Рекомендуется, чтобы исходный SQL-запрос всегда содержал конструкцию ORDER BY 1,2, гарантирующую правильную сортировку входных данных. Вторая колонка запроса играет вспомогательную роль, обеспечивая порядок заполнения данных в выходной таблице, но сама по себе функцией не учитывается.

Вот полный пример:

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att2','val2');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att3','val3');
INSERT INTO ct(rowid, attribute, value) VALUES('test1','att4','val4');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att1','val5');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att2','val6');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att3','val7');
INSERT INTO ct(rowid, attribute, value) VALUES('test2','att4','val8');

SELECT *
FROM crosstab(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

 row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
 test1    | val2       | val3       |
 test2    | val6       | val7       |
(2 rows)

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

Создание собственной специализированной функции перекрестного табулирования, которая интегрирует желаемую схему выходных данных непосредственно в свое объявление. Подробности изложены далее в соответствующем разделе руководства.
Определение необходимого пункта FROM непосредственно в теле представления (VIEW), что избавляет от необходимости прописывать его при каждом обращении к запросу.

Примечание

Также команда \crosstabview в psql, которая предоставляет функциональность, аналогичную crosstab().

crosstabN(text)

crosstabN(text sql)

Функции crosstabN служат образцом того, как можно создать пользовательские оболочки для основной функции crosstab, освобождая от необходимости постоянно задавать имена и типы столбцов при выполнении запросов SELECT. Модуль tablefunc включает функции crosstab2, crosstab3 и crosstab4, в которых типы выходных строк жестко закреплены следующим образом:

CREATE TYPE tablefunc_crosstab_N AS (
    row_name TEXT,
    category_1 TEXT,
    category_2 TEXT,
        .
        .
        .
    category_N TEXT
);

Функции crosstabN предназначены для прямого использования в запросах, результатом которых являются столбцы row_name и value типа text, при условии, что необходимо получить ровно 2, 3 или 4 выходных столбца соответственно. В остальном их поведение совпадает с описанием основной функции crosstab.

Рассмотренный ранее сценарий также успешно применим и с использованием функций crosstabN.

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

Эти функции предоставляются главным образом для иллюстрации. Можно создать свои собственные типы возвращаемых значений и функции на основе базовой функции crosstab(). Есть два способа сделать это:

Определите композитный тип, описывающий требуемую структуру выходных столбцов, аналогично примерам из файла contrib/tablefunc/tablefunc--1.0.sql. Затем объявите собственную функцию с уникальным названием, принимающую один параметр типа text и возвращающую результат типа setof your_type_name, но связанную с базовой реализацией функции crosstab на языке C.

Например, если исходные данные состоят из столбца строк типа text и столбца значений типа float8, а необходимо вывести 5 столбцов значений, процедура выглядит так:
```
CREATE TYPE my_crosstab_float8_5_cols AS (
    my_row_name text,
    my_category_1 float8,
    my_category_2 float8,
    my_category_3 float8,
    my_category_4 float8,
    my_category_5 float8
);

CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(text)
    RETURNS setof my_crosstab_float8_5_cols
    AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;
```

Используйте OUT параметры для неявного определения типа возврата. Тот же пример можно было бы сделать и так:

CREATE OR REPLACE FUNCTION crosstab_float8_5_cols(
    IN text,
    OUT my_row_name text,
    OUT my_category_1 float8,
    OUT my_category_2 float8,
    OUT my_category_3 float8,
    OUT my_category_4 float8,
    OUT my_category_5 float8)
  RETURNS setof record
  AS '$libdir/tablefunc','crosstab' LANGUAGE C STABLE STRICT;

crosstab(text, text)

crosstab(text source_sql, text category_sql)

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

source_sql --- это оператор SQL, который создает исходный набор данных. Это утверждение должно возвращать один row_name столбец, один category столбец и один value столбец. В нем также может быть одна или несколько «дополнительных» столбцов. Столбец row_name должен быть первым. Столбцы category и value должны быть двумя последними столбцами именно в таком порядке. Любые столбцы между row_name и category рассматриваются как «дополнительные». Ожидается, что «дополнительные» столбцы будут одинаковыми для всех строк с одним и тем же значением row_name.

Например, source_sql может создать набор, похожий на следующий:

SELECT row_name, extra_col, cat, value FROM foo ORDER BY 1;

 row_name    extra_col   cat    value
----------+------------+-----+---------
  row1         extra1    cat1    val1
  row1         extra1    cat2    val2
  row1         extra1    cat4    val4
  row2         extra2    cat1    val5
  row2         extra2    cat2    val6
  row2         extra2    cat3    val7
  row2         extra2    cat4    val8

Параметр category_sqlcrosstab`. — это SQL-запрос, формирующий набор уникальных категорий. Запрос обязан возвращать ровно один столбец, содержащий наименования категорий. Если запрос не вернет ни одной строки, возникнет ошибка. Аналогичная ситуация произойдет, если в результатах окажутся повторы — это недопустимо.

SELECT DISTINCT cat FROM foo ORDER BY 1;
    cat
  -------
    cat1
    cat2
    cat3
    cat4

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

Это приведет к созданию результата, похожего на следующий:

                  <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

В предложении FROM необходимо четко задать необходимое количество выходных столбцов нужного типа данных. Пусть запрос source_sql возвращает N столбцов; тогда первые N–2 столбца соответствуют аналогичным первым столбцам в выходе. Остальные выходные столбцы должны иметь тот же тип данных, что и последний столбец запроса source_sql. Их количество должно совпадать с количеством строк, возвращенных запросом category_sql.

Функция crosstab формирует отдельную выходную строку для каждой группы входных строк с одинаковым значением row_name. Поля row_name и любые дополнительные столбцы заполняются значениями из первой строки группы. Остальные выходные столбцы заполняются значениями полей value из строк, чьи значения category совпадают с результатами запроса category_sql. Если какое-то значение category не найдено среди результатов category_sql, его value игнорируется. Для категорий, отсутствующих в какой-либо группе, соответствующие выходные столбцы заполняются пустыми значениями.

Практика показывает, что запрос source_sql всегда должен содержать условие ORDER BY 1, чтобы строки с одинаковыми значениями row_name располагались рядом. Порядок следования самих категорий внутри группы не влияет на результат. Следует учитывать, что порядок вывода результатов запроса category_sql должен строго соответствовать порядку выходных столбцов.

Вот два полных примера:

create table sales(year int, month int, qty int);
insert into sales values(2007, 1, 1000);
insert into sales values(2007, 2, 1500);
insert into sales values(2007, 7, 500);
insert into sales values(2007, 11, 1500);
insert into sales values(2007, 12, 2000);
insert into sales values(2008, 1, 1000);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2007 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2008 | 1000 |      |     |     |     |     |     |     |     |     |      |
(2 rows)

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

Можно создавать предопределенные функции для того, чтобы избежать необходимости записывать имена и типы столбцов результатов в каждом запросе. См. примеры в предыдущем разделе. Основная функция C для этой формы crosstab называется crosstab_hash.

connectby

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

Функция connectby формирует представление иерархической структуры данных, содержащихся в таблице. Таблица должна содержать два ключевых поля: основное, однозначно идентифицирующее каждую строку, и родительное, ссылающееся на родительскую строку (если таковая имеется). С помощью функции connectby можно построить и показать любую ветвь дерева, начиная с любого узла.

Таблица объясняет параметры.

Параметры connectby

Параметр	Описание
`relname`	Имя исходной связи
`keyid_fld`	Имя ключевого поля
`parent_keyid_fld`	Имя родительского ключевого поля
`orderby_fld`	Имя поля для упорядочивания братьев и сестер (необязательно)
`start_with`	Ключ значения строки, с которой нужно начать
`max_depth`	Максимальная глубина спуска или ноль для неограниченной глубины
`branch_delim`	Строка для разделения ключей в выходной ветви (необязательно)

Ключевое и родительское поля могут быть любого типа данных, главное, чтобы они были одинакового типа. Учтите, что параметр start_with всегда должен передаваться в виде текстовой строки, независимо от типа данных ключевого поля.

Функция connectby возвращает набор записей (setof record), поэтому реальные имена и типы выходных столбцов необходимо явно указывать в секции FROM вызывающего запроса SELECT, например:

SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
    AS t(keyid text, parent_keyid text, level int, branch text, pos int);

Первые два выходных столбца предназначены для хранения ключа текущей строки и ключа родительской строки соответственно; они должны иметь тот же тип данных, что и ключевой столбец таблицы. Третий столбец предназначен для глубины узла в дереве и должен быть типа integer. Если был указан параметр branch_delim, следующий столбец — это отображение всей цепочки предков текущей строки (ветвь), он должен быть типа text. Последний столбец, если задан параметр orderby_fld, содержит серийный номер и должен быть типа integer.

Столбец «branch» отображает путь ключей, ведущих к текущей строке, разделенный символом-разделителем, указанным в параметре branch_delim. Если построение ветви не требуется, параметр branch_delim и соответствующий столбец следует опустить.

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

Имена таблиц и полей передаются в SQL-запросы, генерируемые функцией connectby, без изменений. Следовательно, если имена содержат заглавные буквы или специальные символы, их необходимо заключить в двойные кавычки. Рекомендуется также уточнять полное имя таблицы с указанием схемы.

Производительность запроса резко снижается на больших объемах данных, если не создано индексированное поле родительских ключей.

Символ-разделитель, указанный в параметре branch_delim, не должен встречаться в значениях ключей, иначе функция connectby ошибочно распознает ситуацию как бесконечную рекурсию. Если параметр branch_delim не задан, по умолчанию используется символ ~ для выявления циклических ссылок.

Вот пример:

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);

INSERT INTO connectby_tree VALUES('row1',NULL, 0);
INSERT INTO connectby_tree VALUES('row2','row1', 0);
INSERT INTO connectby_tree VALUES('row3','row1', 0);
INSERT INTO connectby_tree VALUES('row4','row2', 1);
INSERT INTO connectby_tree VALUES('row5','row2', 0);
INSERT INTO connectby_tree VALUES('row6','row4', 0);
INSERT INTO connectby_tree VALUES('row7','row3', 0);
INSERT INTO connectby_tree VALUES('row8','row6', 0);
INSERT INTO connectby_tree VALUES('row9','row5', 0);

-- with branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text);
 keyid | parent_keyid | level |       branch
-------+--------------+-------+---------------------
 row2  |              |     0 | row2
 row4  | row2         |     1 | row2~row4
 row6  | row4         |     2 | row2~row4~row6
 row8  | row6         |     3 | row2~row4~row6~row8
 row5  | row2         |     1 | row2~row5
 row9  | row5         |     2 | row2~row5~row9
(6 rows)

-- without branch, without orderby_fld (order of results is not guaranteed)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int);
 keyid | parent_keyid | level
-------+--------------+-------
 row2  |              |     0
 row4  | row2         |     1
 row6  | row4         |     2
 row8  | row6         |     3
 row5  | row2         |     1
 row9  | row5         |     2
(6 rows)

-- with branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0, '~')
 AS t(keyid text, parent_keyid text, level int, branch text, pos int);
 keyid | parent_keyid | level |       branch        | pos
-------+--------------+-------+---------------------+-----
 row2  |              |     0 | row2                |   1
 row5  | row2         |     1 | row2~row5           |   2
 row9  | row5         |     2 | row2~row5~row9      |   3
 row4  | row2         |     1 | row2~row4           |   4
 row6  | row4         |     2 | row2~row4~row6      |   5
 row8  | row6         |     3 | row2~row4~row6~row8 |   6
(6 rows)

-- without branch, with orderby_fld (notice that row5 comes before row4)
SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', 'pos', 'row2', 0)
 AS t(keyid text, parent_keyid text, level int, pos int);
 keyid | parent_keyid | level | pos
-------+--------------+-------+-----
 row2  |              |     0 |   1
 row5  | row2         |     1 |   2
 row9  | row5         |     2 |   3
 row4  | row2         |     1 |   4
 row6  | row4         |     2 |   5
 row8  | row6         |     3 |   6
(6 rows)

Предоставляемые функции​

normal_rand​

crosstab(text)​

crosstabN(text)​

crosstab(text, text)​

connectby​