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

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

           <== столбцы значений ==>
 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 в определение представления.

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. Также он может выдать один или несколько «дополнительных» столбцов. Столбец 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.

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.33.

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