文档中心MogDBMogDB StackUqbar
生态工具
文档工具
EN
v5.0

文档:v5.0

支持的版本:
最新(v5.0)

其他版本:
v3.1/
v3.0/
v2.1/
v2.0/
v1.1

支持PIVOT和UNPIVOT语法

可获得性

本特性自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)

相关页面

SELECT

首页

反馈
Copyright © 2011-2024 www.enmotech.com All rights reserved.