select unpivot

前面的文章对pivot的使用进行了测试,本文对unpivot进行测试。
语法:
SELECT …
FROM …
UNPIVOT [INCLUDE NULLS|EXCLUDE NULLS]
( unpivot_clause
unpivot_for_clause
unpivot_in_clause )
WHERE …

使用unpivot来做列转行,默认情况unpivot是exclude nulls。

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   )
 12   select * From t
 13   unpivot(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 AD_ASST                  4400
           20 MK_MAN                  13000
           20 MK_REP                   6000
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           40 HR_REP                   6500

已选择6行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|*  7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E0_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
   7 - filter("unpivot_view_015"."TOTAL_SALARY" IS NOT NULL)   -----exclude nulls

增加include null,结果集是count(department_id)  * COUNT(distinct JOB_ID) = 4 * 6 = 24

SQL> with t as (select department_id,
  2         sum(decode(job_id, 'AD_ASST', salary, '')) as AD_ASST,
  3         sum(decode(job_id, 'MK_MAN', salary, '')) as MK_MAN,
  4         sum(decode(job_id, 'MK_REP', salary, '')) as MK_REP,
  5         sum(decode(job_id, 'PU_CLERK', salary, '')) as PU_CLERK,
  6         sum(decode(job_id, 'PU_MAN', salary, '')) as PU_MAN,
  7         sum(decode(job_id, 'HR_REP', salary, '')) as HR_REP
  8    from employees
  9   where department_id < = 40
 10   group by department_id
 11   order by 1)
 12   select * From t
 13   unpivot include nulls(
 14   total_salary
 15   for job_id in (
 16      AD_ASST as 'AD_ASST',
 17      MK_MAN as 'MK_MAN',
 18      MK_REP as 'MK_REP',
 19      PU_CLERK as 'PU_CLERK',
 20      PU_MAN as 'PU_MAN',
 21      HR_REP as 'HR_REP'
 22       )
 23  ) order by 1;

DEPARTMENT_ID JOB_ID           TOTAL_SALARY
------------- ---------------- ------------
           10 PU_MAN
           10 PU_CLERK
           10 MK_REP
           10 MK_MAN
           10 AD_ASST                  4400
           10 HR_REP
           20 PU_MAN
           20 PU_CLERK
           20 MK_REP                   6000
           20 MK_MAN                  13000
           20 AD_ASST
           20 HR_REP
           30 AD_ASST
           30 MK_MAN
           30 MK_REP
           30 PU_CLERK                13900
           30 PU_MAN                  11000
           30 HR_REP
           40 AD_ASST
           40 MK_MAN
           40 MK_REP
           40 PU_CLERK
           40 PU_MAN
           40 HR_REP                   6500

已选择24行。

执行计划

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | E-Rows |E-Bytes| Cost (%CPU)|  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |        |       |    15 (100)|       |       |          |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |        |       |            |       |       |          |
|   2 |   LOAD AS SELECT               |                             |        |       |            |   270K|   270K|  270K (0)|
|   3 |    SORT GROUP BY NOSORT        |                             |      1 |    16 |     2   (0)|       |       |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES                   |     10 |   160 |     2   (0)|       |       |          |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX           |     10 |       |     1   (0)|       |       |          |
|   6 |   SORT ORDER BY                |                             |      6 |   192 |    13   (8)|  2048 |  2048 | 2048  (0)|
|   7 |    VIEW                        |                             |      6 |   192 |    12   (0)|       |       |          |
|   8 |     UNPIVOT                    |                             |        |       |            |       |       |          |
|   9 |      VIEW                      |                             |      1 |    91 |     2   (0)|       |       |          |
|  10 |       TABLE ACCESS FULL        | SYS_TEMP_0FD9D66E2_260513AC |      1 |    16 |     2   (0)|       |       |          |
-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("DEPARTMENT_ID"<=40)
此条目发表在SQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s