F.37. tablefunc

F.37.1.1. normal_rand

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

normal函数返回一系列正态分布的随机值(高斯分布)

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)

F.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参数用来产生一组数据源,这个语句必须返回1个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函数返回一系列记录,输出列的实际名字和类型必须定义在FROM字句中,例如:

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

This example produces a set something like:

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

FROM字句必须定义一个row_name(和你查询返回第一列具有相同数据类型)列和一个和N个value列 (和查询返回第三列的类型匹配). 由你来定义输出列的名字和数量.

crosstab函数对于具有相同row_name的一组数据返回一行.它使用这些行 从左到右填充输出到二维表.如果这组数据少于输出的列数,则使用null填充, 如果有多余的行数,则额外的输入将被忽略

SQL查询应该一直指定ORDER BY1,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)

可以通过定义一个crosstab函数去定义任意类型,数量的列,来避免每次在FROM 字句中制定输出列的值和类型.这将在下一节中讲解.另外一种方式是在定义视图的 FROM字句中制定.

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

当输入查询返回text类型的row_name并且你有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中的例子. 按照crosstab函数定义的方式,定义一个唯一函数名来接收一个text类型的参数,返回setof your_type_name. 例如,你的数据源返回一个text类型的row_name,values字段为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;

F.37.1.4. crosstab(text, text)

crosstab(text source_sql, text category_sql)

单参数的crosstab函数的主要限制是处理一组记录时插入每一个值到第一个列中. 如果你想插入值到指定的数据属性中,一些数据可能没有该属性的值,这样它将不能很好 的处理.两个参数的crosstab通过提供一个指定的属性列表能较好的处理该类问题.

source_sql语句返回一组数据源.这个语句返回一个row_name列,一个category列 ,和一个value列,它也可能有一个或多个extra列.row_name必须是第一列.category和 value列必须是最后两列.任何在在row_name和category中间的列被认为时extra列. 有相同row_name的extra列被认为是相同的.

例如,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返回categories记录.这个语句只能返回一列.它必须最少返回一行,否则 会报错.它也不能返回重复的值,否则会报错.category_sql必须如下面:

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

crosstab函数返回一系列记录,列的名字和类型必须定义在SELECT中的FROM字句中.

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和extra列.记录中匹配category的值填充到value列中. 如果一行的category不能匹配任何category_sql返回的结果,它的值将被忽略. 如果输入行中的category不匹配任何category_sql的返回结果,这一个输出列 将被填充为null.

实际上source_sql一直使用ORDER BY 1去确保具有相同row_name的行连续.然而, categories排序不是必须.它实际上时为了保证匹配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)

你能预定义一个函数来避免在每一个查询中都指定输出列的名字和类型. 查看前面部分的例子.构成这种形式的crosstab被命名为crosstab_hash.

F.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能从任意一行列出一个子树

表 F-29 explains the parameters.

键值和父节点键值可以是相同的任意类型,注意start_with必须是一个字符串,无论key的类型.

connectby函数返回一系列记录,所以输出列的名字和类型必须定义在SELECT的FROM字句中,例如:

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

输出的前两列做为现在行的键值和父节点的键值;他们必须和表的键值类型 匹配.第三列为节点在树中的深度必须为整形.如果给出branch_delim参数,下一列是 一个text类型的分支列,最后如果给出orderby_fld参数最后一列是一个整形的自增值.

branch列给出键值到达当前行的路径.键值被branch_delim字段给出的字符分割开. 如果不想显示分支,在每一个输出列表中忽略branch_delim和branch参数.

如果想要排序同一个父节点的兄弟节点,指定orderby_fld参数来指定需要排序兄弟 节点的字段.如果orderby_fld被指定,输出列一定包括一个整形自增的列.

表和字段值的参数被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)