可获得性
本特性自MogDB 5.0.4版本开始引入。
特性简介
本特性兼容Oracle PIVOT和UNPIVOT的语法和功能。
客户价值
增强MogDB与Oracle的兼容性，减少应用程序的迁移代价。
特性描述
PIVOT子句用于将指定字段的字段值由行转换为列，UNPIVOT子句用于将指定字段的字段值由列转换为行。
语法描述
PIVOT
pivot_clause::= PIVOT ( aggregate_function ( expr ) [[AS] alias ][, ...]
pivot_for_clause
pivot_in_clause
)
pivot_for_clause::= FOR (column [, ...])
pivot_in_clause::= IN ({{{ expr | (expr [, ...])} [[AS] alias] [, ...]} | subquery [, ...]})
UNPIVOT
unpivot_clause::= UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
({column | (column [, ...])}
pivot_for_clause
unpivot_in_clause
)
pivot_for_clause::= FOR (column [, ...])
unpivot_in_clause::= IN ({column | (column [, ...])} [AS {literal | (literal [, ...])}] [ {column | (column [, ...])} [AS {literal | (literal [, ...])}]])
特性约束
- pivot和unpivot只支持查询语句
- pivot和unpivot支持普通表、临时表、列存表、分区表、subquery、with子句等，支持多pivot、多unpivot，支持join，支持并行
- pivot和unpivot支持create view、create table as、select into语句
- pivot支持hashAgg、sortAgg
- pivot子句不支持XML
- pivot_in子句不支持subquery和ANY
- pivot和unpivot暂不支持向量化
- pivot和unpivot不支持嵌套
示例
PIVOT
# PIVOT普通表使用示例
MogDB=# create table emp_phone(name varchar2(50), type char, phone varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone values('aaa', '1', '1234-5678');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '2', '3219-6066');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '1', '6837-2745');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '3', '2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '1', '5838-9002');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '2', '2749-5580');
INSERT 0 1
MogDB=# insert into emp_phone values('ddd', '2', '9876-3453');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9599');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '1111-9599');
INSERT 0 1
MogDB=# select * from emp_phone pivot(max(phone) for type in (1 home, 2 office, 3 mobile)) order by 1;
name | home | office | mobile
------+-----------+-----------+-----------
aaa | 1234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
# in子句中如果没有alias，则只用值作为列名
MogDB=# select * from emp_phone pivot(max(phone) for type in (1, 2, 3));
name | 1 | 2 | 3
------+-----------+-----------+-----------
aaa | 1234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
# 删除表
MogDB=# drop table emp_phone;
DROP TABLE
# PIVOT分区表使用示例
MogDB=# create table emp_phone(name varchar2(50), type char, phone varchar2(50))
partition by list(type)
(
PARTITION p1 VALUES ('1', '2'),
PARTITION p2 VALUES ('3')
);
CREATE TABLE
MogDB=# insert into emp_phone values('aaa', '1', '1234-5678');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '2', '3219-6066');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '1', '6837-2745');
INSERT 0 1
MogDB=# insert into emp_phone values('bbb', '3', '2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '1', '5838-9002');
INSERT 0 1
MogDB=# insert into emp_phone values('ccc', '2', '2749-5580');
INSERT 0 1
MogDB=# insert into emp_phone values('ddd', '2', '9876-3453');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '5365-9599');
INSERT 0 1
MogDB=# insert into emp_phone values('aaa', '3', '1111-9599');
INSERT 0 1
MogDB=# select * from emp_phone pivot(max(phone) for type in (1 home, 2 office, 3 mobile)) order by 1;
name | home | office | mobile
------+-----------+-----------+-----------
aaa | 2234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
MogDB=# explain(verbose, costs off) select * from emp_phone partition(p1) pivot(max(phone) for type in (1 home, 2 office, 3 mobile)) order by 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort
Output: emp_phone.name, (max((CASE WHEN ((emp_phone.type)::bigint = 1) THEN emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((emp_phone.type)::bigint = 2) THEN
emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((emp_phone.type)::bigint = 3) THEN emp_phone.phone ELSE NULL::character varying END)::text))
Sort Key: emp_phone.name
-> HashAggregate
Output: emp_phone.name, max((CASE WHEN ((emp_phone.type)::bigint = 1) THEN emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((emp_phone.type)::bigint = 2) T
HEN emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((emp_phone.type)::bigint = 3) THEN emp_phone.phone ELSE NULL::character varying END)::text)
Group By Key: emp_phone.name
-> Partition Iterator
Output: emp_phone.name, emp_phone.type, emp_phone.phone
Iterations: 1
Selected Partitions: 1
-> Partitioned Seq Scan on public.emp_phone
Output: emp_phone.name, emp_phone.type, emp_phone.phone
(12 rows)
# PIVOT支持join
MogDB=# explain (verbose) select * from emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) as p1, emp_phone pivot(max(phone) for type in (1 as home, 2 as office, 3 as mobile)) as p2 where p1.name=p2.name;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=47.36..54.11 rows=200 distinct=[200, 200] width=428)
Output: public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_p
hone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE NULL::c
haracter varying END)::text)), public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE
WHEN ((public.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phon
e.phone ELSE NULL::character varying END)::text))
Hash Cond: ((public.emp_phone.name)::text = (public.emp_phone.name)::text)
-> HashAggregate (cost=20.43..22.43 rows=200 width=340)
Output: public.emp_phone.name, max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.em
p_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE NULL::
character varying END)::text)
Group By Key: public.emp_phone.name
-> Partition Iterator (cost=0.00..12.98 rows=298 width=244)
Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
Iterations: 2
Selected Partitions: 1..2
-> Partitioned Seq Scan on public.emp_phone (cost=0.00..12.98 rows=298 width=244)
Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
-> Hash (cost=24.43..24.43 rows=200 width=214)
Output: public.emp_phone.name, (max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public
.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text)), (max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE N
ULL::character varying END)::text))
-> HashAggregate (cost=20.43..22.43 rows=200 width=340)
Output: public.emp_phone.name, max((CASE WHEN ((public.emp_phone.type)::bigint = 1) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((pub
lic.emp_phone.type)::bigint = 2) THEN public.emp_phone.phone ELSE NULL::character varying END)::text), max((CASE WHEN ((public.emp_phone.type)::bigint = 3) THEN public.emp_phone.phone ELSE
NULL::character varying END)::text)
Group By Key: public.emp_phone.name
-> Partition Iterator (cost=0.00..12.98 rows=298 width=244)
Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
Iterations: 2
Selected Partitions: 1..2
-> Partitioned Seq Scan on public.emp_phone (cost=0.00..12.98 rows=298 width=244)
Output: public.emp_phone.name, public.emp_phone.type, public.emp_phone.phone
(23 rows)
# pivot_for子句支持多个column
MogDB=# create table cust_sales_category(location varchar(20),prod_category varchar(50),customer_id int,sale_amount int);
CREATE TABLE
MogDB=# insert into cust_sales_category (location,prod_category,customer_id,sale_amount) values
MogDB-# ('north','furniture',2,875),
MogDB-# ('south','electronics',2,378),
MogDB-# ('east','gardening',4,136),
MogDB-# ('west','electronics',3,236),
MogDB-# ('central','furniture',3,174),
MogDB-# ('north','electronics',1,729),
MogDB-# ('east','gardening',2,147),
MogDB-# ('west','electronics',3,200),
MogDB-# ('north','furniture',4,987),
MogDB-# ('central','gardening',4,584),
MogDB-# ('south','electronics',3,714),
MogDB-# ('east','furniture',1,192),
MogDB-# ('west','gardening',3,946),
MogDB-# ('east','electronics',4,649),
MogDB-# ('south','furniture',2,503),
MogDB-# ('north','electronics',1,399),
MogDB-# ('central','gardening',3,259),
MogDB-# ('east','electronics',3,407),
MogDB-# ('west','furniture',1,545);
INSERT 0 19
MogDB=# SELECT * FROM (SELECT location, prod_category, customer_id, sale_amount FROM cust_sales_category) PIVOT (SUM(sale_amount) FOR (customer_id, prod_category)IN ((1, 'furniture') AS furn1, (2, 'furniture') AS furn2, (1, 'electronics') AS elec1, (2, 'electronics') AS elec2)) order by 1;
location | furn1 | furn2 | elec1 | elec2
----------+-------+-------+-------+-------
central | | | |
east | 192 | | |
north | | 875 | 1128 |
south | | 503 | | 378
west | 545 | | |
(5 rows)
MogDB=# explain(verbose, analyze) SELECT * FROM (SELECT location, prod_category, customer_id, sale_amount FROM cust_sales_category) PIVOT (SUM(sale_amount) FOR (customer_id, prod_category)IN ((1, 'furniture') AS furn1, (2, 'furniture') AS furn2, (1, 'electronics') AS elec1, (2, 'electronics') AS elec2)) order by 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=37.88..38.38 rows=200 width=90) (actual time=0.075..0.076 rows=5 loops=1)
Output: cust_sales_category.location, (sum(CASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL
::integer END)), (sum(CASE WHEN ((cust_sales_category.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)), (sum(CA
SE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)), (sum(CASE WHEN ((cust_sales_c
ategory.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END))
Sort Key: cust_sales_category.location
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=26.23..28.23 rows=200 width=216) (actual time=0.060..0.060 rows=5 loops=1)
Output: cust_sales_category.location, sum(CASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE
NULL::integer END), sum(CASE WHEN ((cust_sales_category.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'furniture'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END), sum(C
ASE WHEN ((cust_sales_category.customer_id = 1) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END), sum(CASE WHEN ((cust_sales_ca
tegory.customer_id = 2) AND ((cust_sales_category.prod_category)::text = 'electronics'::text)) THEN cust_sales_category.sale_amount ELSE NULL::integer END)
Group By Key: cust_sales_category.location
-> Seq Scan on public.cust_sales_category (cost=0.00..13.82 rows=382 width=184) (actual time=0.019..0.022 rows=19 loops=1)
Output: cust_sales_category.location, cust_sales_category.customer_id, cust_sales_category.prod_category, cust_sales_category.sale_amount
Total runtime: 0.179 ms
(10 rows)
# pivot_for支持with子句
MogDB=# with a as (
MogDB(# select 'Jack' Name ,'sex' Key,'male' Value union all
MogDB(# select 'Jack' ,'country','USA' union all
MogDB(# select 'Jack' ,'hobby','sing' union all
MogDB(# select 'Jack' ,'age','19' union all
MogDB(# select 'Bob' ,'country','UK' union all
MogDB(# select 'Bob' ,'age','20' union all
MogDB(# select 'Bob' ,'weight','70' union all
MogDB(# select 'Maria' ,'sex','female' union all
MogDB(# select 'Maria' ,'weight','50')
MogDB-# select * from a pivot (max(value) for key in ('sex' sex,'country' country,'hobby' hobby,'age' age,'weight' weight)) order by 1,2;
name | sex | country | hobby | age | weight
-------+--------+---------+-------+-----+--------
Bob | | UK | | 20 | 70
Jack | male | USA | sing | 19 |
Maria | female | | | | 50
(3 rows)
# PIVOT支持多个聚合函数
MogDB=# create table t_demo(id int, name text, nums int);
CREATE TABLE
MogDB=# insert into t_demo values(1,'aa',1000),(2,'aa',2000),(3,'aa',4000),(4,'bb',5000),(5,'bb',3000),(6,'cc',3500),(7,'dd',4200),(8,'dd',5500);
INSERT 0 8
MogDB=# select * from (select name, nums from t_demo) pivot (sum(nums) total,min(nums) min for name in ('aa' as apple, 'bb' as orange, 'cc' as grape, 'dd' as mango));
apple_total | apple_min | orange_total | orange_min | grape_total | grape_min | mango_total | mango_min
-------------+-----------+--------------+------------+-------------+-----------+-------------+-----------
7000 | 1000 | 8000 | 3000 | 3500 | 3500 | 9700 | 4200
(1 row)
# PIVOT支持多列多聚合函数
MogDB=# create table tab1(type varchar2(50), weight int, height int);
CREATE TABLE
MogDB=# insert into tab1 values('A',50,10),('A',60,12),('B',40,8),('C',30,15);
INSERT 0 4
MogDB=# select * from tab1 pivot (count(type) as ct, sum(weight) as wt, sum(height) as ht for type in ('A' as A, 'B' as B, 'C' as C));
a_ct | a_wt | a_ht | b_ct | b_wt | b_ht | c_ct | c_wt | c_ht
------+------+------+------+------+------+------+------+------
2 | 110 | 22 | 1 | 40 | 8 | 1 | 30 | 15
(1 row)
# PIVOT聚合函数支持expr
MogDB=# select * from emp_phone pivot(max(phone||'xxx') for type in (1 home, 2 office, 3 mobile)) order by 1;
name | home | office | mobile
------+--------------+--------------+--------------
aaa | 2234-5678xxx | 3219-6066xxx | 5365-9599xxx
bbb | 6837-2745xxx | | 2649-5820xxx
ccc | 5838-9002xxx | 2749-5580xxx |
ddd | | 9876-3453xxx |
(4 rows)
MogDB=# explain(verbose, analyze) select * from emp_phone pivot(max(phone||'xxx') for type in (1 home, 2 office, 3 mobile)) order by 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=34.31..34.81 rows=200 width=214) (actual time=0.088..0.088 rows=4 loops=1)
Output: emp_phone.name, (max(CASE WHEN ((emp_phone.type)::bigint = 1) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END)), (max(CASE WHEN ((emp_phone.type)::bigint = 2) THEN ((emp_phone.phone
)::text || 'xxx'::text) ELSE NULL::text END)), (max(CASE WHEN ((emp_phone.type)::bigint = 3) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END))
Sort Key: emp_phone.name
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=22.67..24.67 rows=200 width=340) (actual time=0.073..0.075 rows=4 loops=1)
Output: emp_phone.name, max(CASE WHEN ((emp_phone.type)::bigint = 1) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END), max(CASE WHEN ((emp_phone.type)::bigint = 2) THEN ((emp_phone.ph
one)::text || 'xxx'::text) ELSE NULL::text END), max(CASE WHEN ((emp_phone.type)::bigint = 3) THEN ((emp_phone.phone)::text || 'xxx'::text) ELSE NULL::text END)
Group By Key: emp_phone.name
-> Partition Iterator (cost=0.00..12.98 rows=298 width=244) (actual time=0.012..0.022 rows=11 loops=1)
Output: emp_phone.name, emp_phone.type, emp_phone.phone
Iterations: 2
Selected Partitions: 1..2
-> Partitioned Seq Scan on public.emp_phone (cost=0.00..12.98 rows=298 width=244) (actual time=0.007..0.010 rows=11 loops=2)
Output: emp_phone.name, emp_phone.type, emp_phone.phone
Total runtime: 0.201 ms
(14 rows)
# PIVOT支持create table as
MogDB=# create table test1 as select * from emp_phone pivot(max(phone) for type in (1 home, 2 office, 3 mobile)) order by 1;
INSERT 0 4
MogDB=# select * from test1;
name | home | office | mobile
------+-----------+-----------+-----------
aaa | 2234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
# PIVOT支持select into
MogDB=# select * into test2 from emp_phone pivot(max(phone) for type in (1 home, 2 office, 3 mobile)) order by 1;
INSERT 0 4
MogDB=# select * from test2;
name | home | office | mobile
------+-----------+-----------+-----------
aaa | 2234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
# PIVOT支持view
MogDB=# create view tv1 as select * from emp_phone pivot(max(phone) for type in (1 home, 2 office, 3 mobile));
CREATE VIEW
MogDB=# \d+ tv1;
View "public.tv1"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
name | character varying(50) | | extended |
home | text | | extended |
office | text | | extended |
mobile | text | | extended |
View definition:
SELECT *
FROM ( SELECT emp_phone.name,
max(
CASE
WHEN emp_phone.type::bigint = 1 THEN emp_phone.phone
ELSE NULL::character varying
END::text) AS home,
max(
CASE
WHEN emp_phone.type::bigint = 2 THEN emp_phone.phone
ELSE NULL::character varying
END::text) AS office,
max(
CASE
WHEN emp_phone.type::bigint = 3 THEN emp_phone.phone
ELSE NULL::character varying
END::text) AS mobile
FROM emp_phone
GROUP BY emp_phone.name) unnamed_pivot;
MogDB=# select * from tv1;
name | home | office | mobile
------+-----------+-----------+-----------
aaa | 2234-5678 | 3219-6066 | 5365-9599
bbb | 6837-2745 | | 2649-5820
ccc | 5838-9002 | 2749-5580 |
ddd | | 9876-3453 |
(4 rows)
UNPIVOT
# UNPIVOT使用示例
MogDB=# create table emp_phone1(name varchar2(50), home varchar2(50), office varchar2(50), mobile varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone1 values('aaa','1234-5678','3219-6066','5365-9583');
INSERT 0 1
MogDB=# insert into emp_phone1 values('bbb','5838-9002','2749-5580','');
INSERT 0 1
MogDB=# insert into emp_phone1 values('ccc','','9876-3453','');
INSERT 0 1
MogDB=# insert into emp_phone1 values('ddd','6837-2745','','2649-5820');
INSERT 0 1
MogDB=# insert into emp_phone1 values('eee','','','2649-5820');
INSERT 0 1
# unpivot_in中，in类型的隐私转换使用默认list的隐私类型转换
MogDB=# select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));
name | type | phone
------+------+-----------
aaa | 1 | 1234-5678
aaa | 2 | 3219-6066
aaa | 3 | 5365-9583
bbb | 1 | 5838-9002
bbb | 2 | 2749-5580
ccc | 2 | 9876-3453
ddd | 1 | 6837-2745
ddd | 3 | 2649-5820
eee | 3 | 2649-5820
(9 rows)
MogDB=# explain(verbose, analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Unpivot (cost=0.00..12.85 rows=487 width=154) (actual time=0.010..0.012 rows=9 loops=1)
Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
Project 1: emp_phone1.name, 1, emp_phone1.home
Project 2: emp_phone1.name, 2, emp_phone1.office
Project 3: emp_phone1.name, 3, emp_phone1.mobile
Filter 1: (emp_phone1.home IS NOT NULL)
Filter 2: (emp_phone1.office IS NOT NULL)
Filter 3: (emp_phone1.mobile IS NOT NULL)
-> Seq Scan on public.emp_phone1 (cost=0.00..11.63 rows=163 width=472) (actual time=0.007..0.007 rows=5 loops=1)
Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
Total runtime: 0.067 ms
(11 rows)
MogDB=# select * from emp_phone1 unpivot include nulls (phone for type in (home as 1, office as 2, mobile as 3));
name | type | phone
------+------+-----------
aaa | 1 | 1234-5678
aaa | 2 | 3219-6066
aaa | 3 | 5365-9583
bbb | 1 | 5838-9002
bbb | 2 | 2749-5580
bbb | 3 |
ccc | 1 |
ccc | 2 | 9876-3453
ccc | 3 |
ddd | 1 | 6837-2745
ddd | 2 |
ddd | 3 | 2649-5820
eee | 1 |
eee | 2 |
eee | 3 | 2649-5820
(15 rows)
# UNPIVOT支持并行
MogDB=# set query_dop = 4;
SET
MogDB=# set smp_thread_cost = 0;
SET
MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot include nulls (phone for type in (home as 1, office as 2, mobile as 3));
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Streaming(type: LOCAL GATHER dop: 1/4) (cost=0.00..23.04 rows=489 width=154) (actual time=[18.733,32.127]..[18.733,32.127], rows=15)
Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
-> Unpivot (cost=0.00..3.21 rows=489 width=154) (actual time=[0.001,0.001]..[0.008,0.015], rows=15)
Output: unnamed_unpivot.name, unnamed_unpivot.type, unnamed_unpivot.phone
Project 1: emp_phone1.name, 1, emp_phone1.home
Project 2: emp_phone1.name, 2, emp_phone1.office
Project 3: emp_phone1.name, 3, emp_phone1.mobile
-> Seq Scan on public.emp_phone1 (cost=0.00..2.91 rows=163 width=472) (actual time=[0.000,0.000]..[0.005,0.006], rows=5)
Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
Total runtime: 33.168 ms
(10 rows)
# UNPIVOT支持join
MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p1, emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p2 where p1.name=p2.name;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=18.94..46.08 rows=1186 distinct=[200, 200] width=308) (actual time=0.162..0.170 rows=19 loops=1)
Output: p1.name, p1.type, p1.phone, p2.name, p2.type, p2.phone
Hash Cond: ((p1.name)::text = (p2.name)::text)
-> Unpivot (cost=0.00..12.85 rows=487 width=154) (actual time=0.009..0.013 rows=9 loops=1)
Output: p1.name, p1.type, p1.phone
Project 1: public.emp_phone1.name, 1, public.emp_phone1.home
Project 2: public.emp_phone1.name, 2, public.emp_phone1.office
Project 3: public.emp_phone1.name, 3, public.emp_phone1.mobile
Filter 1: (public.emp_phone1.home IS NOT NULL)
Filter 2: (public.emp_phone1.office IS NOT NULL)
Filter 3: (public.emp_phone1.mobile IS NOT NULL)
-> Seq Scan on public.emp_phone1 (cost=0.00..11.63 rows=163 width=472) (actual time=0.006..0.006 rows=5 loops=1)
Output: public.emp_phone1.name, public.emp_phone1.home, public.emp_phone1.office, public.emp_phone1.mobile
-> Hash (cost=17.72..17.72 rows=487 width=154) (actual time=0.024..0.024 rows=9 loops=1)
Output: p2.name, p2.type, p2.phone
Buckets: 32768 Batches: 1 Memory Usage: 257kB
-> Unpivot (cost=0.00..12.85 rows=487 width=154) (actual time=0.001..0.014 rows=9 loops=1)
Output: p2.name, p2.type, p2.phone
Project 1: public.emp_phone1.name, 1, public.emp_phone1.home
Project 2: public.emp_phone1.name, 2, public.emp_phone1.office
Project 3: public.emp_phone1.name, 3, public.emp_phone1.mobile
Filter 1: (public.emp_phone1.home IS NOT NULL)
Filter 2: (public.emp_phone1.office IS NOT NULL)
Filter 3: (public.emp_phone1.mobile IS NOT NULL)
-> Seq Scan on public.emp_phone1 (cost=0.00..11.63 rows=163 width=472) (actual time=0.001..0.013 rows=5 loops=1)
Output: public.emp_phone1.name, public.emp_phone1.home, public.emp_phone1.office, public.emp_phone1.mobile
Total runtime: 0.290 ms
(27 rows)
MogDB=# explain(verbose,analyze) select * from emp_phone1 unpivot(phone for type in (home as 1, office as 2, mobile as 3)) as p1, emp_phone as p2 where p1.name=p2.name;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=16.71..38.64 rows=726 distinct=[200, 200] width=398) (actual time=0.187..0.194 rows=26 loops=1)
Output: p1.name, p1.type, p1.phone, p2.name, p2.type, p2.phone
Hash Cond: ((p1.name)::text = (p2.name)::text)
-> Unpivot (cost=0.00..12.85 rows=487 width=154) (actual time=0.009..0.011 rows=9 loops=1)
Output: p1.name, p1.type, p1.phone
Project 1: emp_phone1.name, 1, emp_phone1.home
Project 2: emp_phone1.name, 2, emp_phone1.office
Project 3: emp_phone1.name, 3, emp_phone1.mobile
Filter 1: (emp_phone1.home IS NOT NULL)
Filter 2: (emp_phone1.office IS NOT NULL)
Filter 3: (emp_phone1.mobile IS NOT NULL)
-> Seq Scan on public.emp_phone1 (cost=0.00..11.63 rows=163 width=472) (actual time=0.005..0.005 rows=5 loops=1)
Output: emp_phone1.name, emp_phone1.home, emp_phone1.office, emp_phone1.mobile
-> Hash (cost=12.98..12.98 rows=298 width=244) (actual time=0.040..0.040 rows=11 loops=1)
Output: p2.name, p2.type, p2.phone
Buckets: 32768 Batches: 1 Memory Usage: 257kB
-> Partition Iterator (cost=0.00..12.98 rows=298 width=244) (actual time=0.025..0.033 rows=11 loops=1)
Output: p2.name, p2.type, p2.phone
Iterations: 2
Selected Partitions: 1..2
-> Partitioned Seq Scan on public.emp_phone p2 (cost=0.00..12.98 rows=298 width=244) (actual time=0.005..0.005 rows=11 loops=2)
Output: p2.name, p2.type, p2.phone
Total runtime: 0.299 ms
(23 rows)
# UNPIVOT支持多个column
MogDB=# create table emp_phone2(name varchar2(50), home varchar2(50), office varchar2(50), mobile varchar2(50), extra varchar2(50));
CREATE TABLE
MogDB=# insert into emp_phone2 values('aaa','1234-5678','3219-6066','5365-9583','11111');
INSERT 0 1
MogDB=# insert into emp_phone2 values('bbb','5838-9002','2749-5580','','22222');
INSERT 0 1
MogDB=# insert into emp_phone2 values('ccc','','9876-3453','','333333');
INSERT 0 1
MogDB=# insert into emp_phone2 values('ddd','6837-2745','','2649-5820','44444');
INSERT 0 1
MogDB=# insert into emp_phone2 values('eee','','','2649-5820','44444');
INSERT 0 1
MogDB=# select * from emp_phone2 unpivot((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
name | type1 | type2 | phone | phone1
------+-------+-------+-----------+-----------
aaa | 1 | 11 | 1234-5678 | 3219-6066
aaa | 3 | 33 | 5365-9583 | 11111
bbb | 1 | 11 | 5838-9002 | 2749-5580
bbb | 3 | 33 | | 22222
ccc | 1 | 11 | | 9876-3453
ccc | 3 | 33 | | 333333
ddd | 1 | 11 | 6837-2745 |
ddd | 3 | 33 | 2649-5820 | 44444
eee | 3 | 33 | 2649-5820 | 44444
(9 rows)
MogDB=# explain(verbose,analyze) select * from emp_phone2 unpivot((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Unpivot (cost=0.00..11.98 rows=264 width=190) (actual time=0.009..0.012 rows=9 loops=1)
Output: unnamed_unpivot.name, unnamed_unpivot.type1, unnamed_unpivot.type2, unnamed_unpivot.phone, unnamed_unpivot.phone1
Project 1: emp_phone2.name, 1, 11, emp_phone2.home, emp_phone2.office
Project 2: emp_phone2.name, 3, 33, emp_phone2.mobile, emp_phone2.extra
Filter 1: ((emp_phone2.home IS NOT NULL) OR (emp_phone2.office IS NOT NULL))
Filter 2: ((emp_phone2.mobile IS NOT NULL) OR (emp_phone2.extra IS NOT NULL))
-> Seq Scan on public.emp_phone2 (cost=0.00..11.32 rows=132 width=590) (actual time=0.006..0.007 rows=5 loops=1)
Output: emp_phone2.name, emp_phone2.home, emp_phone2.office, emp_phone2.mobile, emp_phone2.extra
Total runtime: 0.078 ms
(9 rows)
# UNPIVOT支持with子句
MogDB=# with t as (select 0 a,1 b,2 c,3 d) select * from t unpivot (val for col in (A,B,C,D));
col | val
-----+-----
a | 0
b | 1
c | 2
d | 3
(4 rows)
MogDB=# explain(verbose,analyze) with t as (select 0 a,1 b,2 c,3 d) select * from t unpivot (val for col in (A,B,C,D));
QUERY PLAN
------------------------------------------------------------------------------------------
Unpivot (cost=0.00..0.02 rows=1 width=36) (actual time=0.004..0.005 rows=4 loops=1)
Output: unnamed_unpivot.col, unnamed_unpivot.val
Project 1: 'a'::text, (0)
Project 2: 'b'::text, (1)
Project 3: 'c'::text, (2)
Project 4: 'd'::text, (3)
Filter 1: ((0) IS NOT NULL)
Filter 2: ((1) IS NOT NULL)
Filter 3: ((2) IS NOT NULL)
Filter 4: ((3) IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
Output: 0, 1, 2, 3
Total runtime: 0.047 ms
(13 rows)
# UNPIVOT支持view
MogDB=# create view tv2 as select * from emp_phone2 unpivot include nulls((phone,phone1) for (type1,type2) in ((home,office) as (1,11), (mobile,extra) as (3,33)));
CREATE VIEW
MogDB=# \d+ tv2;
View "public.tv2"
Column | Type | Modifiers | Storage | Description
--------+-----------------------+-----------+----------+-------------
name | character varying(50) | | extended |
type1 | integer | | plain |
type2 | integer | | plain |
phone | character varying | | extended |
phone1 | character varying | | extended |
View definition:
SELECT *
FROM emp_phone2 UNPIVOT INCLUDE NULLS ((phone,phone1) FOR (type1,type2) IN ((home,office) AS (1,11),(mobile,extra) AS (3,33)));
MogDB=# select * from tv2;
name | type1 | type2 | phone | phone1
------+-------+-------+-----------+-----------
aaa | 1 | 11 | 1234-5678 | 3219-6066
aaa | 3 | 33 | 5365-9583 | 11111
bbb | 1 | 11 | 5838-9002 | 2749-5580
bbb | 3 | 33 | | 22222
ccc | 1 | 11 | | 9876-3453
ccc | 3 | 33 | | 333333
ddd | 1 | 11 | 6837-2745 |
ddd | 3 | 33 | 2649-5820 | 44444
eee | 1 | 11 | |
eee | 3 | 33 | 2649-5820 | 44444
(10 rows)
相关页面
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
