tablefunc

CREATE EXTENSION tablefunc;

CREATE EXTENSION tablefunc;

SELECT * FROM normal_rand(1000, 5, 3);
-- numvals: 值的数量, mean: 均值 5, stddev: 标准差 3

CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT);
INSERT INTO ct(rowid, attribute, value) VALUES
  ('test1','att1','val1'), ('test1','att2','val2'),
  ('test1','att3','val3'), ('test2','att1','val5'),
  ('test2','att2','val6'), ('test2','att3','val7');

SELECT *
FROM crosstab(
  'SELECT rowid, attribute, value FROM ct 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    | val1       | val2       | val3
 test2    | val5       | val6       | val7

CREATE TABLE sales(year int, month int, qty int);
INSERT INTO sales VALUES(2007,1,1000),(2007,2,1500),(2007,7,500),
  (2007,11,1500),(2007,12,2000),(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 |      |     |     |     |     |     |     |     |     |      |

SELECT * FROM crosstab3(
  'SELECT rowid, attribute, value FROM ct ORDER BY 1,2'
);

CREATE TYPE my_crosstab_float8_5_cols AS (
    my_row_name text, c1 float8, c2 float8, c3 float8, c4 float8, c5 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;

CREATE TABLE connectby_tree(keyid text, parent_keyid text, pos int);
INSERT INTO connectby_tree VALUES
  ('row1',NULL,0), ('row2','row1',0), ('row3','row1',0),
  ('row4','row2',1), ('row5','row2',0), ('row6','row4',0),
  ('row7','row3',0), ('row8','row6',0), ('row9','row5',0);

-- 带分支显示和排序
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