PostgreSQL tablefunc

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

normal_rand產(chǎn)生一個正態(tài)分布隨機(jī)值（高斯分布）的集合。

numvals是從該函數(shù)返回的值的數(shù)量。mean是值的正態(tài)分布的均值而stddev是值的正態(tài)分布的標(biāo)準(zhǔn)偏差。

例如，這個調(diào)用請求 1000 個值，它們具有均值 5 和標(biāo)準(zhǔn)偏差 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)

crosstab(text sql)
crosstab(text sql, int N)

crosstab函數(shù)被用來產(chǎn)生“pivot”顯示，在其中數(shù)據(jù)被橫布在頁面上而不是直接向下列舉。例如，我們可能有這樣的數(shù)據(jù)

row1    val11
row1    val12
row1    val13
...
row2    val21
row2    val22
row2    val23
...

而我們希望顯示成這樣

row1    val11   val12   val13   ...
row2    val21   val22   val23   ...
...

crosstab函數(shù)會采用一個文本參數(shù)，該文本是一個 SQL 查詢，它產(chǎn)生按照第一種方式格式化的原始數(shù)據(jù)，并且產(chǎn)生以第二種方式格式化的一個表。

sql參數(shù)是一個產(chǎn)生數(shù)據(jù)的源集合的 SQL 語句。這個語句必須返回一個row_name列、一個category列和一個value列。N是一個廢棄參數(shù)，即使提供也會被忽略（之前這必須匹配輸出值列的數(shù)目，但是現(xiàn)在這由調(diào)用查詢決定了）。

例如，所提供的查詢可能會產(chǎ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函數(shù)被聲明為返回setof record， 因此輸出列的實(shí)際名稱和類型必須定義在調(diào)用的SELECT語句的FROM子句中，例如：

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

這個例子產(chǎn)生這樣一個集合：

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

FROM子句必須把輸出定義為一個row_name列 （具有 SQL 查詢的第一個結(jié)果列的相同數(shù)據(jù)類型），其后跟隨著 N 個value列 （都具有 SQL 查詢的第三個結(jié)果列的相同數(shù)據(jù)類型）。你可以按照你的意愿設(shè)置任意多的輸出值列。 而輸出列的名稱取決于你。

crosstab函數(shù)為具有相同row_name值的 輸入行的每一個連續(xù)分組產(chǎn)生一個輸出行。它使用來自這些行的值域 從左至右填充輸出的值列。如果一個分組中的行比輸出值列少， 多余的輸出列將被用空值填充。如果行更多，則多余的輸入行會被跳過。

事實(shí)上，SQL 查詢應(yīng)該總是指定ORDER BY 1,2來保證輸入行被正確地排序， 也就是說具有相同row_name的值會被放在一起并且在行內(nèi) 被正確地排序。注意crosstab本身并不關(guān)注查詢結(jié)果的第二列，它放在那里 只是為了被排序，以便控制出現(xiàn)在頁面上的第三列值的順序。

這是一個完整的例子：

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子句來定義輸出列， 方法是設(shè)置一個在其定義中硬編碼所期望的輸出行類型的自定義 crosstab 函數(shù)。 這會在下一節(jié)中描述。另一種可能性是在一個視圖定義中嵌入所需的FROM子句。

crosstabN(text sql)

crosstabN 系列函數(shù)是如何為普通crosstab 函數(shù)設(shè)置自定義包裝器的例子，這樣你不需要在調(diào)用的SELECT查詢中 寫出列名和類型。tablefunc模塊包括crosstab2、 crosstab3以及crosstab4，它們的輸入行類型被定義為：

CREATE TYPE tablefunc_crosstab_N AS ( row_name TEXT, category_1 TEXT, category_2 TEXT, . . . category_N TEXT );

因此，當(dāng)輸入查詢產(chǎn)生類型為text的列row_name和value 并且想要 2、3 或 4 個輸出值列時，這些函數(shù)可以被直接使用。在所有其他方法中，它們的行為都和上面的 一般crosstab函數(shù)完全相同。

例如，前一節(jié)給出的例子也可以這樣來做

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

這些函數(shù)主要是出于舉例的目的而提供。你可以基于底層的crosstab()函數(shù) 創(chuàng)建你自己的返回類型和函數(shù)。有兩種方法來做：

crosstab(text source_sql, text category_sql)

crosstab的單一參數(shù)形式的主要限制是它把一個組中的所有值都視作相似， 并且把每一個值插入到第一個可用的列中。如果你想要值列對應(yīng)于特定的數(shù)據(jù)分類，并且 某些分組可能沒有關(guān)于某些分類的數(shù)據(jù)，這樣的形式就無法工作。crosstab的雙參數(shù)形式 通過提供一個對應(yīng)于輸出列的顯式分類列表來處理這種情況。

source_sql是一個產(chǎn)生源數(shù)據(jù)集的 SQL 語句。這個語句必須返回一個 row_name列、一個category列以及一個value列。 也可以有一個或者多個“extra”列。 row_name列必須是第一個。 category和value列必須是按照這個順序的最后兩個列。 row_name和category之間的任何列都被視作“extra”。 對于具有相同row_name值的所有行，其“extra”列都應(yīng)該相同。

例如，source_sql可能產(chǎn)生一組這樣的東西：

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是一個產(chǎn)生分類集合的 SQL 語句。這個語句必須只返回一列。 它必須產(chǎn)生至少一行，否則會生成一個錯誤。還有，它不能產(chǎn)生重復(fù)值，否則會生成一個錯誤。category_sql可能是這樣的：

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

crosstab函數(shù)被聲明為返回setof record，這樣輸出列的實(shí)際名稱和類型 就必須在調(diào)用的SELECT語句的FROM子句中被定義，例如：

SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, extra text, cat1 text, cat2 text, cat3 text, cat4 text);

這將產(chǎn)生這樣的結(jié)果：

                  <==  value  columns   ==>
row_name   extra   cat1   cat2   cat3   cat4
---------+-------+------+------+------+------
  row1     extra1  val1   val2          val4
  row2     extra2  val5   val6   val7   val8

FROM子句必須定義正確數(shù)量的輸出列以及正確的數(shù)據(jù)類型。如果在source_sql 查詢的結(jié)果中有N列，其中的前N-2 列必須匹配前N-2 個輸出列。剩余的輸出列必須具有source_sql查詢結(jié)果的最后一列的類型，并且并且它們的數(shù)量 必須正好和source_sql查詢結(jié)果中的行數(shù)相同。

crosstab函數(shù)為具有相同row_name值的輸入行形成的每一個連續(xù)分組 產(chǎn)生一個輸出行。輸出的row_name列外加任意一個“extra”列都是從分組的 第一行復(fù)制而來。輸出的value列被使用具有匹配的 category值的行中的 value域填充。如果一個行的category不匹配category_sql 查詢的任何輸出，它的value會被忽略。匹配的分類不出現(xiàn)于分組中任何輸出行中的的 輸出列會被用空值填充。

事實(shí)上，source_sql查詢應(yīng)該總是指定ORDER BY 1來保證 具有相同row_name的值會被放在一起。但是，一個分組內(nèi)分類的順序并不重要。 還有，確保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)

你可以創(chuàng)建預(yù)定義的函數(shù)來避免在每個查詢中都必須寫出結(jié)果列的名稱和類型。請參考前一節(jié)中的例子。 用于這種形式的crosstab的底層 C 函數(shù)被命名為crosstab_hash。

connectby(text relname, text keyid_fld, text parent_keyid_fld
          [, text orderby_fld ], text start_with, int max_depth
          [, text branch_delim ])

connectby函數(shù)產(chǎn)生存儲在一個表中的層次數(shù)據(jù)的顯示。該表必須具有一個用以 唯一標(biāo)識行的鍵域，以及一個父親鍵域用來引用其父親（如果有）。connectby能 顯示從任意行開始向下的子樹。

表 F.31解釋了參數(shù)。

鍵域和父親鍵域可以是任意數(shù)據(jù)類型，但是它們必須是同一類型。 注意start_with值必須作為一個文本串被輸入，而不管鍵域的類型如何。

connectby函數(shù)被聲明為返回setof record，因此輸出列的實(shí)際名稱和類型 就必須在調(diào)用的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);

前兩個輸出列被用于當(dāng)前行的鍵和其父親行的鍵，它們必須匹配該表的鍵域的類型。第三個輸出行是該樹中的深度， 并且必須是類型integer。如果給定了一個branch_delim參數(shù)，下一個輸出列 就是分支顯示并且必須是類型text。最后，如果給出了一個orderby_fld參數(shù)， 最后一個輸出列是一個序號，并且必須是類型integer。

“branch”輸出列顯示了用于到達(dá)當(dāng)前行的由鍵構(gòu)成的路徑。其中的鍵用指定的branch_delim 字符串分隔開。如果不需要分支顯示，可以在輸出列列表中忽略branch_delim參數(shù)和分支列。

如果同一父親的子女之間的順序很重要，可以包括orderby_fld參數(shù)以指定用哪個域?qū)π值芘判颉?這個域可以是任何可排序數(shù)據(jù)類型。當(dāng)且僅當(dāng)orderby_fld被指定時，輸出列列表必須包括一個 最終的整數(shù)序號列。

表示表和列名的參數(shù)會被原樣復(fù)制到connectby內(nèi)部生成的 SQL 查詢中。 因此，如果名稱是大小寫混合或者包含特殊字符，應(yīng)包括雙引號。你也可能需要用模式限定表名。

在大型的表中，除非在父親鍵域上有索引，否則性能會很差。

branch_delim字符串不出現(xiàn)在任何鍵值中是很重要的，否則connectby可能會錯誤地 報(bào)告一個無限遞歸錯誤。注意如果沒有提供branch_delim，將用一個默認(rèn)值~來進(jìn)行遞歸檢測。

這里是一個例子：

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

-- 帶有分支，但沒有 orderby_fld （不保證結(jié)果的順序）
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)

-- 沒有分支，也沒有 orderby_fld （不保證結(jié)果的順序）
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)

-- 有分支，有 orderby_fld （注意 row5 在 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)

-- 沒有分支，有 orderby_fld （注意 row5 在 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)