465
PostgreSQL 行列轉換的用法
一般在提交報表的時候，可能會使用到對數據表的行列變換的需求。
如以下數據:
digoal=> select * from tbl_sellers_info order by seller,se_year,se_month;
seller | se_year | se_month | se_amount
----------+---------+----------+-----------
卡洛斯 | 2011 | 3 | 12
卡洛斯 | 2011 | 4 | 45
卡洛斯 | 2011 | 5 | 56
德哥 | 2010 | 1 | 123456
德哥 | 2010 | 2 | 234567
德哥 | 2010 | 3 | 345678
德哥 | 2010 | 4 | 345678
德哥 | 2010 | 5 | 567890
德哥 | 2010 | 6 | 123456
德哥 | 2010 | 7 | 234567
德哥 | 2010 | 8 | 345678
德哥 | 2010 | 9 | 345678
德哥 | 2010 | 10 | 567890
德哥 | 2010 | 11 | 123456
德哥 | 2010 | 12 | 234567
德哥 | 2011 | 1 | 123456
德哥 | 2011 | 2 | 234567
德哥 | 2011 | 3 | 345678
德哥 | 2011 | 4 | 345678
德哥 | 2011 | 5 | 567890
羅納爾多 | 2011 | 2 | 20
羅納爾多 | 2011 | 3 | 30
羅納爾多 | 2011 | 4 | 40
羅納爾多 | 2011 | 5 | 50
貝克漢姆 | 2010 | 11 | 12
貝克漢姆 | 2010 | 12 | 23
貝克漢姆 | 2011 | 1 | 12
貝克漢姆 | 2011 | 2 | 23
貝克漢姆 | 2011 | 3 | 34
貝克漢姆 | 2011 | 4 | 45
貝克漢姆 | 2011 | 5 | 56
(31 rows)
例如要按照每個SELLER出具每年的月度銷售情況，可能要轉換成:
圖1
在PostgreSQL9.1裏麵有一個比較好用的extension，tablefunc，可以輕鬆的完成行列轉換。
下麵會有例子來簡單的描述以下這個extension.
1. 安裝tablefunc extension.
\c digoal postgres
You are now connected to database "digoal" as user "postgres".
digoal=# create extension tablefunc;
CREATE EXTENSION
digoal=# \c digoal digoal
You are now connected to database "digoal" as user "digoal".
#安裝完後會創建幾個TYPE,幾個函數.
type:
CREATE TYPE tablefunc_crosstab_N AS ( row_name TEXT, category_1 TEXT, category_2 TEXT, . . . category_N TEXT );
public | tablefunc_crosstab_2 | tablefunc_crosstab_2 | tuple | |
public | tablefunc_crosstab_3 | tablefunc_crosstab_3 | tuple | |
public | tablefunc_crosstab_4 | tablefunc_crosstab_4 | tuple | |
function
normal_rand(int numvals, float8 mean, float8 stddev) returns setof float8
crosstab(text sql) crosstab(text sql, int N)
crosstabN(text sql)
crosstab(text source_sql, text category_sql)
connectby(text relname, text keyid_fld, text parent_keyid_fld [, text orderby_fld ], text start_with, int max_depth [, text branch_delim ])
2. 創建測試表
create table tbl_sellers_info (seller text,se_year int,se_month int,se_amount int);
insert into tbl_sellers_info values ('德哥',2011,01,123456);
insert into tbl_sellers_info values ('德哥',2011,02,234567);
insert into tbl_sellers_info values ('德哥',2011,03,345678);
insert into tbl_sellers_info values ('德哥',2011,04,345678);
insert into tbl_sellers_info values ('德哥',2011,05,567890);
insert into tbl_sellers_info values ('貝克漢姆',2011,01,12);
insert into tbl_sellers_info values ('貝克漢姆',2011,02,23);
insert into tbl_sellers_info values ('貝克漢姆',2011,03,34);
insert into tbl_sellers_info values ('貝克漢姆',2011,04,45);
insert into tbl_sellers_info values ('貝克漢姆',2011,05,56);
insert into tbl_sellers_info values ('卡洛斯',2011,03,12);
insert into tbl_sellers_info values ('卡洛斯',2011,04,45);
insert into tbl_sellers_info values ('卡洛斯',2011,05,56);
insert into tbl_sellers_info values ('羅納爾多',2011,02,20);
insert into tbl_sellers_info values ('羅納爾多',2011,03,30);
insert into tbl_sellers_info values ('羅納爾多',2011,04,40);
insert into tbl_sellers_info values ('羅納爾多',2011,05,50);
insert into tbl_sellers_info values ('德哥',2010,01,123456);
insert into tbl_sellers_info values ('德哥',2010,02,234567);
insert into tbl_sellers_info values ('德哥',2010,03,345678);
insert into tbl_sellers_info values ('德哥',2010,04,345678);
insert into tbl_sellers_info values ('德哥',2010,05,567890);
insert into tbl_sellers_info values ('德哥',2010,06,123456);
insert into tbl_sellers_info values ('德哥',2010,07,234567);
insert into tbl_sellers_info values ('德哥',2010,08,345678);
insert into tbl_sellers_info values ('德哥',2010,09,345678);
insert into tbl_sellers_info values ('德哥',2010,10,567890);
insert into tbl_sellers_info values ('德哥',2010,11,123456);
insert into tbl_sellers_info values ('德哥',2010,12,234567);
insert into tbl_sellers_info values ('貝克漢姆',2010,11,12);
insert into tbl_sellers_info values ('貝克漢姆',2010,12,23);
3. 下麵我們用crosstab(text source_sql, text category_sql) 來滿足需求.
select substr(seller,1,char_length(seller)-4) seller,substr(seller,char_length(seller)-3) se_year,jan ,feb ,mar ,apr ,may ,jun ,jul ,aug ,sep ,oct ,nov ,dec from crosstab('select seller||se_year,se_month,se_amount from tbl_sellers_info order by 1',
'select distinct se_month from tbl_sellers_info order by 1') as (seller text,Jan numeric,feb numeric,mar numeric,apr numeric,may numeric,jun numeric,jul numeric,aug numeric,sep numeric,oct numeric,nov numeric,dec numeric) order by 1,2;
# 分析一下這個函數:
參數一:(這個是需要進行行列變換的源SQL)
select seller||se_year,se_month,se_amount from tbl_sellers_info order by 1
參數二:(這個在這裏代表的是月份，也就是se_month的值)
select distinct se_month from tbl_sellers_info order by 1
或者
select * from generate_series(1,12) order by 1
出來的結果都是一樣的。
輸出:
(seller text,Jan numeric,feb numeric,mar numeric,apr numeric,may numeric,jun numeric,jul numeric,aug numeric,sep numeric,oct numeric,nov numeric,dec numeric)
seller代表的是seller||se_year,這裏必須這麼做，因為需要按照這個來分組.
後麵的就是月份了，數據類型是和se_amount一致就行了。
輸出結果就是前麵提到的圖片。
行記錄中沒有的月份在變換後該月數據為空.例如卡洛斯隻有2011年的3,4,5月份有數據，那麼其他月份就是空白的。
# 另外一個要注意的是，輸出結果的順序是select * from generate_series(1,12) order by 1來決定的.
# 例如
select * from generate_series(1,12) order by 1 desc 那麼輸出的順序就是反的,因此必須嚴格的排序.
圖2
# 另外的幾個crosstab函數用法與此類似,隻不過沒有這個靈活。
# 而normal_rand用於返回一係列正態分布值。connect_by用於做異構查詢（前麵我有寫過用with rescursive來實現）。就不再演示。
參考:
https://www.postgresql.org/docs/9.5/static/tablefunc.html
圖1
圖2
最後更新：2017-04-01 13:44:32