E.37. tablefunc

E.37.1.1. 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)

E.37.1.2. crosstab(text)

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

Функция crosstab применяется для формирования "повёрнутых" отображений, в которых данные идут вдоль строк, а не сверху вниз. Например, мы можем иметь такие данные:

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);

Этот запрос выдаст примерно такой результат:

           <== колонки значений ==>
 row_name   category_1   category_2
----------+------------+------------
  row1        val1         val2
  row2        val5         val6

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

Функция crosstab выдаёт одну выходную строку для каждой последовательной группы с одним значением row_name. Она заполняет колонки значений слева направо полями value из этих строк. Если в группе оказывается меньше строк, чем выходных колонок значений, дополнительные колонки принимают значения NULL; если же строк оказывается больше, лишние строки игнорируются.

На практике в SQL-запросе всегда должно указываться ORDER BY 1,2, чтобы входные строки были отсортированы должным образом, то есть, чтобы данные с одинаковым значением row_name собирались вместе и корректно упорядочивались в строке. Заметьте, что сама crosstab не учитывает вторую колонку результата запроса; она присутствует только для того, чтобы определять порядок, в котором значения третьей колонки будут следовать в строке.

Полный пример:

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

E.37.1.3. 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
);

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

Так, пример, приведённый в предыдущем разделе, можно переписать и в таком виде:

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 имя_вашего_типа, и связать его с той же нижележащей функцией 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;

E.37.1.4. crosstab(text, text)

crosstab(text source_sql, text category_sql)

Основное ограничение формы crosstab с одним параметром состоит в том, что она воспринимает все значения в группе одинаково и вставляет очередное значение в первую свободную колонку. Если вы хотите, чтобы колонки значений соответствовали определённым категориям данных и некоторые группы могли содержать данные не для всех категорий, этот подход не будет работать. Форма 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_sql передаётся оператор SQL, выдающий набор категорий. Этот оператор должен возвращать всего одну колонку. Он должен выдать минимум одну строку; в противном случае произойдёт ошибка. Кроме того, выдаваемые им значения не должны повторяться, иначе так же произойдёт ошибка. В качестве category_sql можно передать, например, такой запрос:

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

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

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

При этом будет получен примерно такой результат:

                  <==  колонки значений   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

В предложении FROM должно определяться нужное количество выходных колонок соответствующих типов данных. Если запрос source_sql выдаёт N колонок, первые N-2 из них должны соответствовать первым N-2 выходным колонкам. Оставшиеся выходные колонки должны иметь тип последней колонки результата source_sql и их должно быть столько, сколько строк оказалось в результате запроса category_sql.

Функция crosstab выдаёт одну выходную строку для каждой последовательной группы входных строк с одним значением row_name. Выходная колонка row_name плюс все "дополнительные" колонки копируются из первой строки группы. Выходные колонки значений заполняются содержимым полей value из строк с соответствующими значениями category. Если в поле category оказывается значение, отсутствующее в результате запроса category_sql, содержимое поля value в этой строке игнорируется. Выходные колонки, для которых соответствующая категория не представлена ни в одной из входных строк группы, принимают значения NULL.

На практике в запросе 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.

E.37.1.5. 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 может вывести вложенное дерево, начиная с любой строки.

Параметры описаны в Таблице E-29.

Поля ключа и ключа родителя могут быть любого типа, но должны иметь общий тип. Заметьте, что значение 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 и колонку branch в списке выходных колонок.

Если порядок потомков одного родителя имеет значение, добавьте параметр 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);

-- с ветвями без orderby_fld (порядок результатов не гарантируется)
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)

-- без ветвей и без orderby_fld (порядок результатов не гарантируется)
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)

-- с ветвями и с orderby_fld (заметьте, что row5 идёт перед 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)

-- без ветвей, с orderby_fld (заметьте, что row5 идёт перед 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)