文章目录
创建扩展
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