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

Этот пример создает набор, похожий на:

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

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(). Есть два способа сделать это:

crosstab(text source_sql, text category_sql)

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

source_sql - это SQL-запрос, который создает исходный набор данных. Этот запрос должен возвращать одну колонку row_name, одну колонку category и одну колонку value. Он также может иметь одну или несколько дополнительных колонок “extra”. Колонка row_name должна быть первой. Колонки category и value должны быть последними двумя колонками в указанном порядке. Любые колонки между row_name и category рассматриваются как “extra”. Ожидается, что дополнительные колонки “extra” будут одинаковыми для всех строк с одним и тем же значением 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);

Это приведет к получению результата, что-то вроде:

                  <==  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 из них должны соответствовать первым N-2 выходным столбцам. Оставшиеся выходные столбцы должны иметь тип последнего столбца в результате запроса source_sql, и их количество должно быть точно таким же, как количество строк в результате запроса category_sql.

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

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

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

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

Ключевые поля и родительские ключи могут быть любого типа данных, но они должны быть одного типа. Обратите внимание, что значение 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);

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