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 #