PostgreSQL：文档：18：F.43. tablefunc — 返回表的函数（crosstab 等）

支持的版本：当前 (18) / 17 / 16 / 15 / 14 / 13

开发版本：devel

不支持的版本：12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3

F.43. tablefunc — 返回表的函数（`crosstab` 等）
上一步	上一级	附录 F. 附加提供的模块和扩展	首页	下一步

F.43. tablefunc — 返回表的函数（`crosstab` 等） #

F.43.1. 提供的函数
F.43.2. 作者

tablefunc 模块包含各种返回表的函数（即多行）。这些函数本身很有用，也可以作为编写返回多行的 C 函数的示例。

此模块被认为是“受信任的”，这意味着非超级用户也可以在其拥有的数据库上安装它，前提是他们具有 CREATE 权限。

F.43.1. 提供的函数 #

表 F.33 总结了 tablefunc 模块提供的函数。

表 F.33. 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 ( 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

生成表示层次树结构的函数。

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

F.43.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，因此输出列的实际名称和类型必须在调用 SELECT 语句的 FROM 子句中定义，例如

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)

您可以通过设置自定义的 crosstab 函数来避免每次都编写输出列的 FROM 子句，这样就无需在调用 SELECT 查询中写出列名和类型。这将在下一节中介绍。另一种选择是将所需的 FROM 子句嵌入到视图定义中。

注意

另请参阅 psql 中的 \crosstabview 命令，它提供了与 crosstab() 类似的功能。

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

F.43.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，因此输出列的实际名称和类型必须在调用 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 列以及任何“附加”列将从组的第一行复制。输出的 value 列将用具有匹配 category 值的行的 value 字段填充。如果行的 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)

您可以创建预定义的函数来避免在每个查询中都必须写出结果列名和类型。请参阅上一节中的示例。此形式的 crosstab 的底层 C 函数名为 crosstab_hash。

F.43.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.34 解释了参数。

表 F.34. connectby 参数

参数	描述
`relname`	源关系的名称
`keyid_fld`	键字段的名称
`parent_keyid_fld`	父键字段的名称
`orderby_fld`	用于排序同级的字段名称（可选）
`start_with`	开始行的键值
`max_depth`	要向下深入的最大深度，或零表示无限深度
`branch_delim`	用于在分支输出中分隔键的字符串（可选）

键字段和父键字段可以是任何数据类型，但它们必须是相同类型。请注意，start_with 值必须作为文本字符串输入，无论键字段的类型如何。

connectby 函数被声明为返回 setof record，因此输出列的实际名称和类型必须在调用 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);

前两列输出用于当前行的键及其父行的键；它们必须与表的键字段类型匹配。第三个输出列是树中的深度，必须是 integer 类型。如果提供了 branch_delim 参数，则下一列输出是分支显示，必须是 text 类型。最后，如果提供了 orderby_fld 参数，则最后一列输出是序号，必须是 integer 类型。

“分支”输出列显示了到达当前行的键路径。键由指定的 branch_delim 字符串分隔。如果不需要分支显示，请在输出列列表中省略 branch_delim 参数和分支列。

如果同一父级的同级顺序很重要，请包含 orderby_fld 参数来指定按哪个字段排序同级。此字段可以是任何可排序的数据类型。输出列列表必须包含一个最终的整数序号列，当且仅当指定了 orderby_fld 时。

表示表和字段名称的参数会原封不动地复制到 connectby 内部生成的 SQL 查询中。因此，如果名称是混合大小写或包含特殊字符，请包含双引号。您可能还需要对表名进行模式限定。

在大表中，除非在父键字段上有索引，否则性能会很差。

重要的是，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)

F.43.2. 作者 #

Joe Conway

上一步	上一级	下一步
F.42. sslinfo — 获取客户端 SSL 信息	首页	F.44. tcn — 一个触发函数，用于通知监听器表内容更改

提交更正

如果您在文档中看到任何不正确的内容、与您对特定功能的体验不符或需要进一步说明的内容，请使用此表单报告文档问题。

F.43. tablefunc — 返回表的函数（crosstab 等） #

F.43.1. 提供的函数 #

F.43.1.1. normal_rand #

F.43.1.2. crosstab(text) #

注意

F.43.1.3. crosstabN(text) #

F.43.1.4. crosstab(text, text) #

F.43.1.5. connectby #