PostgreSQL：說明文件：17：F.41. tablefunc — 傳回表格的函式（交叉表等等）

支援的版本：目前 (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.41. tablefunc — 傳回表格的函式 (`crosstab` 以及其他)
上一頁	往上	附錄 F. 額外提供的模組與擴充功能	首頁	下一頁

F.41. tablefunc — 傳回表格的函式 (`crosstab` 以及其他) #

F.41.1. 提供的函式
F.41.2. 作者

tablefunc 模組包含各種傳回表格（也就是多列）的函式。這些函式本身很有用，同時也是如何撰寫傳回多列 C 函式的範例。

此模組被視為「受信任的」，也就是說，具有目前資料庫 CREATE 權限的非超級使用者可以安裝它。

F.41.1. 提供的函式 #

表格 F.31 總結了 tablefunc 模組提供的函式。

表格 F.31. 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.41.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.41.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)

您可以透過設定自訂的 crosstab 函數來避免總是必須寫出 FROM 子句來定義輸出欄位，該函數的定義中已連線到所需的輸出列類型。這將在下一節中說明。另一種可能性是將所需的 FROM 子句嵌入到檢視定義中。

注意：

另請參閱 psql 中的 \crosstabview 命令，它提供的功能與 crosstab() 類似。

F.41.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 和 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() 函數建立自己的回傳類型和函數。有兩種方法可以做到這一點：

建立一個複合類型來描述所需的輸出欄位，類似於 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.41.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 會被忽略。匹配類別未出現在群組的任何輸入列中的輸出欄位會填入空值。

實際上，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.41.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.32 說明了這些參數。

表格 F.32. 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.41.2. 作者 #

Joe Conway

上一頁	往上	下一頁
F.40. sslinfo — 取得客戶端 SSL 資訊	首頁	F.42. tcn — 一個觸發函式，用於通知監聽器表格內容的變更

提交更正

如果您在文件中發現任何不正確、與您使用特定功能時的經驗不符或需要進一步說明的地方，請使用此表格來報告文件問題。

F.41. tablefunc — 傳回表格的函式 (crosstab 以及其他) #

F.41.1. 提供的函式 #

F.41.1.1. normal_rand #

F.41.1.2. crosstab(text) #

注意：

F.41.1.3. crosstabN(text) #

F.41.1.4. crosstab(text, text) #

F.41.1.5. connectby #