PostgreSQL - tablefunc

官方文档地址

创建扩展

create extension tablefunc;

tablefunc函数

1. normal_rand - 产生一个正态分布随机值（高斯分布）的集合

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

numvals是从该函数返回的值的数量。mean是值的正态分布的均值，而stddev是值的正态分布的标准偏差。

标准偏差举例：两组数的集合{0,5,9,14}和{5,6,8,9}其平均值都是7，但第二个集合具有较小的标准差。

SELECT * FROM normal_rand(100, 5, 3);

2. crosstab - 行转列(1)

crosstab(text sql)

--示例
--1、建表
db_test=# create table db_test.t_sales_amount (n_id int4,c_name varchar(100),n_quarter int2,n_amount numeric(20,2));
--2、插入数据
db_test=# insert into db_test.t_sales_amount(n_id, c_name, n_quarter, n_amount) values
(1,'张三',1,100),(1,'张三',2,200),(1,'张三',3,300),(1,'张三',4,400),
(2,'李四',1,500),(2,'李四',2,600),(2,'李四',3,700),(2,'李四',4,800);
db_test=# select * from db_test.t_sales_amount;
 n_id | c_name | n_quarter | n_amount
------+--------+-----------+----------
    1 | 张三   |         1 |   100.00
    1 | 张三   |         2 |   200.00
    1 | 张三   |         3 |   300.00
    1 | 张三   |         4 |   400.00
    2 | 李四   |         1 |   500.00
    2 | 李四   |         2 |   600.00
    2 | 李四   |         3 |   700.00
    2 | 李四   |         4 |   800.00
--3、想得到的结果，1~4季度变成四列
db_test=# SELECT *
FROM crosstab(
  'SELECT c_name, n_quarter, n_amount FROM db_test.t_sales_amount
   order by 1,2')
AS ct(c_name varchar, q_1 numeric, q_2 numeric, q_3 numeric, q_4 numeric);
--结果
 c_name |  q_1   |  q_2   |  q_3   |  q_4
--------+--------+--------+--------+--------
 李四   | 500.00 | 600.00 | 700.00 | 800.00
 张三   | 100.00 | 200.00 | 300.00 | 400.00

3. crosstab - 行转列(2)

crosstab(text source_sql, text category_sql)

• 示例一

--1、建表插入数据
create table db_test.t_sales(year int, month int, qty int);
insert into db_test.t_sales values(2007, 1, 1000);
insert into db_test.t_sales values(2007, 2, 1500);
insert into db_test.t_sales values(2007, 7, 500);
insert into db_test.t_sales values(2007, 11, 1500);
insert into db_test.t_sales values(2007, 12, 2000);
insert into db_test.t_sales values(2008, 1, 1000);

--2、实现效果，12个月变成列，没有数据的月显示空
select * from crosstab(
  'select year, month, qty from db_test.t_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 |      |     |     |     |     |     |     |     |     |      |

• 示例二

--1、建表插入
CREATE TABLE db_test.t_cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO db_test.t_cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO db_test.t_cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO db_test.t_cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO db_test.t_cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO db_test.t_cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO db_test.t_cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO db_test.t_cth VALUES('test2','02 March 2003','volts','3.1234');

--2、实现效果
SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM db_test.t_cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM db_test.t_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 | 2003-03-01 00:00:00 |          42 | PASS        |                     | 2.6987
 test2 | 2003-03-02 00:00:00 |          53 | FAIL        | 2003-03-01 00:00:00 | 3.1234

4. connectby