标簽
PostgreSQL , Oracle , 行列變換 , pivot , unpivot , tablefunc , crosstab , json , jsonb
https://github.com/digoal/blog/blob/master/201807/20180714_02.md#%E8%83%8C%E6%99%AF 背景
行列轉換是OLAP應用場景中，對資料透視常用的SQL之一。
https://github.com/digoal/blog/blob/master/201807/20180714_02.md#oracle-pivot-%E8%A1%8C%E8%BD%AC%E5%88%97 Oracle pivot 行轉列
文法如下：
SELECT ...
FROM ...
PIVOT [XML]
(pivot_clause
pivot_for_clause
pivot_in_clause )
WHERE ...
In addition tothe new PIVOT keyword, we can see three new pivot clauses, described below.
（1）pivot_clause: definesthe columns to be aggregated (pivot is an aggregate operation);
（2）pivot_for_clause: definesthe columns to be grouped and pivoted;
（3）pivot_in_clause: definesthe filter for the column(s) in the pivot_for_clause (i.e. the range of valuesto limit the results to). The aggregations for each value in thepivot_in_clause will be transposed into a separate column (where appropriate).
對pivot_for_clause 指定的列進行過濾，隻将指定的行轉成列。
如：
SQL> WITH pivot_data AS ( -- 使用CTE文法，行轉列的記錄集
2 SELECT deptno, job, sal -- 原始表，注意出現過的列，看PIVOT裡面是如何處理的
3 FROM emp
4 )
5 SELECT *
6 FROM pivot_data
7 PIVOT (
8 SUM(sal) --<-- pivot_clause ， 作為聚合項
9 FOR deptno --<-- pivot_for_clause 行轉列字段（聚合）
10 IN (10,20,30,40) --<-- pivot_in_clause 行轉列的行取值，最後會生成這些行（聚合）
11 );
未寫入pivot内的列(JOB)，被作為group by的字段。sal作為聚合列，deptno的内容10,20,30,40作為行列轉換（GROUP BY）字段。
JOB 10 20 30 40
--------- ---------- ---------- --------- -----------
CLERK 1430 2090 1045
SALESMAN 6160
PRESIDENT 5500
MANAGER 2695 3272.5 3135
ANALYST 6600
5 rows selected.
https://github.com/digoal/blog/blob/master/201807/20180714_02.md#oracle-unpivot-%E5%88%97%E8%BD%AC%E8%A1%8C Oracle unpivot 列轉行
文法：
SELECT ...
FROM ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
(unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE ...
The unpivot_clause rotatescolumns into rows.
（1）The INCLUDE | EXCLUDE NULLS clausegives you the option of including or excluding null-valued rows. INCLUDE NULLS causesthe unpivot operation to include null-valued rows; EXCLUDE NULLS eliminatesnull-values rows from the return set. If you omit this clause, then the unpivotoperation excludes nulls.
這個選項用來控制unpivot 是否包含null 的記錄，預設是不包含nulls的。
（2）unpivot_clause: this clause specifies a name for a column to represent the unpivotedmeasure values.
對應的具體值
（3）Inthe pivot_for_clause, specify a name for eachoutput column that will hold descriptor values, such as quarter or product.
對應轉換後列的名稱
（4）Inthe unpivot_in_clause, specify the input datacolumns whose names will become values in the output columns of the pivot_for_clause.These input data columns have names specifying a category value, such as Q1,Q2, Q3, Q4. The optional AS clause lets you map the input data columnnames to the specified literal values in the output columns.
具體列到行的列名
SQL> SELECT *
2 FROM pivoted_data
3 UNPIVOT (
4 deptsal --<-- unpivot_clause
5 FOR saldesc --<-- unpivot_for_clause
6 IN (d10_sal, d20_sal, d30_sal, d40_sal) --<-- unpivot_in_clause
7 );
JOB SALDESC DEPTSAL
---------- ---------- ----------
CLERK D10_SAL 1430
CLERK D20_SAL 2090
CLERK D30_SAL 1045
SALESMAN D30_SAL 6160
PRESIDENT D10_SAL 5500
MANAGER D10_SAL 2695
MANAGER D20_SAL 3272.5
MANAGER D30_SAL 3135
ANALYST D20_SAL 6600
https://github.com/digoal/blog/blob/master/201807/20180714_02.md#postgresql-%E8%A1%8C%E8%BD%AC%E5%88%97 PostgreSQL 行轉列
https://www.postgresql.org/docs/devel/static/tablefunc.htmlcreate extension tablefunc;
使用crosstab函數接口進行行列轉換。
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);
insert into tbl_sellers_info select * from tbl_sellers_info;
postgres=# select * from tbl_sellers_info ;
seller | se_year | se_month | se_amount
----------+---------+----------+-----------
德哥 | 2011 | 1 | 123456
德哥 | 2011 | 2 | 234567
德哥 | 2011 | 3 | 345678
德哥 | 2011 | 4 | 345678
德哥 | 2011 | 5 | 567890
貝克漢姆 | 2011 | 1 | 12
貝克漢姆 | 2011 | 2 | 23
貝克漢姆 | 2011 | 3 | 34
貝克漢姆 | 2011 | 4 | 45
貝克漢姆 | 2011 | 5 | 56
卡洛斯 | 2011 | 3 | 12
卡洛斯 | 2011 | 4 | 45
卡洛斯 | 2011 | 5 | 56
羅納爾多 | 2011 | 2 | 20
羅納爾多 | 2011 | 3 | 30
羅納爾多 | 2011 | 4 | 40
羅納爾多 | 2011 | 5 | 50
德哥 | 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
貝克漢姆 | 2010 | 11 | 12
貝克漢姆 | 2010 | 12 | 23
德哥 | 2011 | 1 | 123456
德哥 | 2011 | 2 | 234567
德哥 | 2011 | 3 | 345678
德哥 | 2011 | 4 | 345678
德哥 | 2011 | 5 | 567890
貝克漢姆 | 2011 | 1 | 12
貝克漢姆 | 2011 | 2 | 23
貝克漢姆 | 2011 | 3 | 34
貝克漢姆 | 2011 | 4 | 45
貝克漢姆 | 2011 | 5 | 56
卡洛斯 | 2011 | 3 | 12
卡洛斯 | 2011 | 4 | 45
卡洛斯 | 2011 | 5 | 56
羅納爾多 | 2011 | 2 | 20
羅納爾多 | 2011 | 3 | 30
羅納爾多 | 2011 | 4 | 40
羅納爾多 | 2011 | 5 | 50
德哥 | 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
貝克漢姆 | 2010 | 11 | 12
貝克漢姆 | 2010 | 12 | 23
(62 rows)
行列變換，用JSON，将需要GROUP BY的多個字段合并成1個。
select
js->>'seller' as seller,
js->>'se_year' as se_year,
jan ,
feb ,
mar ,
apr ,
may ,
jun ,
jul ,
aug ,
sep ,
oct ,
nov ,
dec
from crosstab(
-- 這個是需要進行行列變換的源SQL ， 資料源。
-- 排序字段為group by字段 ，最後一個字段為轉換後的内容字段，導數第二個字段為行列變換的字段（内容為枚舉，比如月份）
-- （必須在下一個參數中提取出對應的所有枚舉值）
$$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,
-- 行列轉換的行，有哪些值被提取出來作為列。 這個在這裡代表的是月份，也就是se_month的值
-- 或(select * from (values('jan'),...('dec')) t(se_month))
'select distinct se_month from tbl_sellers_info order by 1'
)
as -- crosstab 輸出格式
( js jsonb, -- 第一個參數SQL内對應的order by對應的字段（1個或多個）
Jan numeric, -- 第一個參數SQL内對應導數第二個字段的枚舉值，（行轉列）
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;
結果
seller | se_year | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
----------+---------+--------+--------+--------+--------+---------+--------+--------+--------+--------+---------+--------+--------
卡洛斯 | 2011 | | | 24 | 90 | 112 | | | | | | |
德哥 | 2010 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134 | 691356 | 691356 | 1135780 | 246912 | 469134
德哥 | 2011 | 246912 | 469134 | 691356 | 691356 | 1135780 | | | | | | |
羅納爾多 | 2011 | | 40 | 60 | 80 | 100 | | | | | | |
貝克漢姆 | 2010 | | | | | | | | | | | 24 | 46
貝克漢姆 | 2011 | 24 | 46 | 68 | 90 | 112 | | | | | | |
(6 rows)
https://github.com/digoal/blog/blob/master/201807/20180714_02.md#postgresql-%E5%88%97%E8%BD%AC%E8%A1%8C PostgreSQL 列轉行
例子，将以上行列轉換的結果，再轉換回去。
with a as ( -- A對應原始資料（即需要列轉行的資料）
select
js->>'seller' as seller,
js->>'se_year' as se_year,
jan ,
feb ,
mar ,
apr ,
may ,
jun ,
jul ,
aug ,
sep ,
oct ,
nov ,
dec
from crosstab(
-- 這個是需要進行行列變換的源SQL ， 資料源。
-- 排序字段為group by字段 ，最後一個字段為轉換後的内容字段，導數第二個字段為行列變換的字段（内容為枚舉，比如月份）
-- （必須在下一個參數中提取出對應的所有枚舉值）
$$select jsonb_build_object('seller', seller, 'se_year', se_year) as js, se_month, sum(se_amount) from tbl_sellers_info group by 1,2 order by 1$$,
-- 行列轉換的行，有哪些值被提取出來作為列。 這個在這裡代表的是月份，也就是se_month的值
-- 或(select * from (values('jan'),...('dec')) t(se_month))
'select distinct se_month from tbl_sellers_info order by 1'
)
as -- crosstab 輸出格式
( js jsonb, -- 第一個參數SQL内對應的order by對應的字段（1個或多個）
Jan numeric, -- 第一個參數SQL内對應導數第二個字段的枚舉值，（行轉列）
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
)
,
-- b ， 用jsonb把多列合并為一列，并使用jsonb_each展開。
b as (select seller, se_year, jsonb_each(row_to_json(a)::jsonb-'seller'::text-'se_year'::text) as rec from a)
select seller, se_year, (b.rec).key as month, (b.rec).value as sum from b;
seller | se_year | month | sum
----------+---------+-------+---------
卡洛斯 | 2011 | apr | 90
卡洛斯 | 2011 | aug | null
卡洛斯 | 2011 | dec | null
卡洛斯 | 2011 | feb | null
卡洛斯 | 2011 | jan | null
卡洛斯 | 2011 | jul | null
卡洛斯 | 2011 | jun | null
卡洛斯 | 2011 | mar | 24
卡洛斯 | 2011 | may | 112
卡洛斯 | 2011 | nov | null
卡洛斯 | 2011 | oct | null
卡洛斯 | 2011 | sep | null
德哥 | 2010 | apr | 691356
德哥 | 2010 | aug | 691356
德哥 | 2010 | dec | 469134
德哥 | 2010 | feb | 469134
德哥 | 2010 | jan | 246912
德哥 | 2010 | jul | 469134
德哥 | 2010 | jun | 246912
德哥 | 2010 | mar | 691356
德哥 | 2010 | may | 1135780
德哥 | 2010 | nov | 246912
德哥 | 2010 | oct | 1135780
德哥 | 2010 | sep | 691356
德哥 | 2011 | apr | 691356
德哥 | 2011 | aug | null
德哥 | 2011 | dec | null
德哥 | 2011 | feb | 469134
德哥 | 2011 | jan | 246912
德哥 | 2011 | jul | null
德哥 | 2011 | jun | null
德哥 | 2011 | mar | 691356
德哥 | 2011 | may | 1135780
德哥 | 2011 | nov | null
德哥 | 2011 | oct | null
德哥 | 2011 | sep | null
羅納爾多 | 2011 | apr | 80
羅納爾多 | 2011 | aug | null
羅納爾多 | 2011 | dec | null
羅納爾多 | 2011 | feb | 40
羅納爾多 | 2011 | jan | null
羅納爾多 | 2011 | jul | null
羅納爾多 | 2011 | jun | null
羅納爾多 | 2011 | mar | 60
羅納爾多 | 2011 | may | 100
羅納爾多 | 2011 | nov | null
羅納爾多 | 2011 | oct | null
羅納爾多 | 2011 | sep | null
貝克漢姆 | 2010 | apr | null
貝克漢姆 | 2010 | aug | null
貝克漢姆 | 2010 | dec | 46
貝克漢姆 | 2010 | feb | null
貝克漢姆 | 2010 | jan | null
貝克漢姆 | 2010 | jul | null
貝克漢姆 | 2010 | jun | null
貝克漢姆 | 2010 | mar | null
貝克漢姆 | 2010 | may | null
貝克漢姆 | 2010 | nov | 24
貝克漢姆 | 2010 | oct | null
貝克漢姆 | 2010 | sep | null
貝克漢姆 | 2011 | apr | 90
貝克漢姆 | 2011 | aug | null
貝克漢姆 | 2011 | dec | null
貝克漢姆 | 2011 | feb | 46
貝克漢姆 | 2011 | jan | 24
貝克漢姆 | 2011 | jul | null
貝克漢姆 | 2011 | jun | null
貝克漢姆 | 2011 | mar | 68
貝克漢姆 | 2011 | may | 112
貝克漢姆 | 2011 | nov | null
貝克漢姆 | 2011 | oct | null
貝克漢姆 | 2011 | sep | null
(72 rows)
![nodejs微信開發---授權登入+擷取使用者資訊微信網頁授權[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)