Postgresql 中文操作指南

F.43. tablefunc — functions that return tables (crosstab and others) #

tablefunc 模块包含各种返回表（即多行）的函数。这些函数既对自身有用，又作为编写返回多行的 C 函数的示例。

此模块被认为是“受信任的”，也就是说，它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。

F.43.1. Functions Provided #

Table F.32 概述了 tablefunc 模块提供的一些功能。

Table F.32. tablefunc Functions

Function

Description

normal_rand ( numvals integer , mean float8 , stddev float8 ) → setof float8 生成一组正态分布的随机值。

crosstab ( sql text ) → setof record 制作一个“主轴表”，其中包含行名和 N 值列，其中 N 由调用查询中指定的行类型确定。

crosstab_N ( sql text ) → setof table_crosstab_N 制作一个“主轴表”，其中包含行名和 _N 值列。 crosstab2 、 crosstab3 和 crosstab4 是预定义的，但您可以按照以下所示创建其他 _crosstab_N 函数。

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 制作一个表示分级树结构的 representation。

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 列，则额外的输出列将用空值填充；如果有更多行，则会跳过额外的输入行。

实际上，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 子句来定义输出列。这在下一节中进行了描述。另一种可能性是在视图定义中嵌入必需的 FROM 子句。

Note

另请参见 psql 中的 _ \crosstabview_ 命令，该命令提供了与 crosstab() 类似的功能。

F.43.1.3. crosstab_N(text)_ #

crosstabN(text sql)

crosstab_N_ 函数是为通用 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 和 value 列，并且您想要 2、3 或 4 个输出值列，则可以直接使用这些函数。在其他所有方面，它们的行为与上面为通用 crosstab 函数所述的行为完全相同。

例如，上一部分中给出的示例也将作为工作方式：

SELECT *
FROM crosstab3(
  'select rowid, attribute, value
   from ct
   where attribute = ''att2'' or attribute = ''att3''
   order by 1,2');

这些函数主要是出于说明目的而提供的。你可以基于底层 crosstab() 函数创建自己的返回类型和函数。有两种实现方法：

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 可以显示任何行开始的子树。

Table F.33 说明了一些参数。

Table F.33. connectby Parameters

Parameter

Description

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. Author #

乔·康威