Chinaunix首页 | 论坛 | 认证专区 | 博客

Serge Rielau (瑞赛奇)

Interesting SQL solutions for DB2 LUW as well as hints on how to move Oracle applications to DB2

  • 博客访问: 74458
  • 博文数量: 9
  • 博客积分: 205
  • 博客等级: 入伍新兵
  • 技术积分: 1304
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-25 13:37
文章分类
文章存档

2012年(9)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

Pivoting tables 2012-05-25 14:00:22

分类: Mysql/postgreSQL

  •  PIVOT
    The meaning of pivoting a table is to turn n-rows into n-columns.
    For example given a ales table with a composite primary key of year and quarter you may want to get a result that shows only one row for each year, but a separate column for each quarter.

    点击(此处)折叠或打开

    1. CREATE TABLE Sales(Year INTEGER NOT NULL,
    2. Quarter INTEGER NOT NULL,
    3. Results INTEGER);
    4. CREATE UNIQUE INDEX PK_IDX ON Sales(Year, Quarter) INCLUDE(Results);
    5. ALTER TABLE Sales ADD PRIMARY KEY (Year, Quarter);
    6. INSERT INTO Sales VALUES
    7. (2004, 1, 20),
    8. (2004, 2, 30),
    9. (2004, 3, 15),
    10. (2004, 4, 10),
    11. (2005, 1, 18),
    12. (2005, 2, 40),
    13. (2005, 3, 12),
    14. (2005, 4, 27);

    The desired result is:

  • 点击(此处)折叠或打开

    1. Year Q1 Q2 Q3 Q4
    2. ---- -- -- -- --
    3. 2004 20 30 15 10
    4. 2005 18 40 12 27

    There a many ways to achieve pivoting, but there is clearly one that is optimal which I will explain here.
    Pivoting involves two events: First the payload (Results in this case) needs to be dispatch into the Q1 through Q4 columns.
    This can be done with a CASE expression or DECODE (which is another syntax for CASE):

    点击(此处)折叠或打开

    1. SELECT Year,
    2. DECODE(Quarter, 1, Results) AS Q1,
    3. DECODE(Quarter, 2, Results) AS Q2,
    4. DECODE(Quarter, 3, Results) AS Q3,
    5. DECODE(Quarter, 4, Results) AS Q4
    6. FROM Sales;
    7. YEAR Q1 Q2 Q3 Q4
    8. ----------- ----------- ----------- ----------- -----------
    9. 2004 20 - - -
    10. 2004 - 30 - -
    11. 2004 - - 15 -
    12. 2004 - - - 10
    13. 2005 18 - - -
    14. 2005 - 40 - -
    15. 2005 - - 12 -
    16. 2005 - - - 27
    17. 8 record(s) selected.
    Note how DECODE injects NULL as an implicit ELSE.
    The second step is to collapse the rows to get one row per Year.
    The technique of choice is a GROUP BY here.
    When grouping on Year we need to use some sort of column function for the columns Q1 through Q4.
    MAX or MIN() work for most types and they are very cheap. Especially since all but one value will be NULL per group:

    点击(此处)折叠或打开

    1. SELECT Year,
    2. MAX(DECODE(Quarter, 1, Results)) AS Q1,
    3. MAX(DECODE(Quarter, 2, Results)) AS Q2,
    4. MAX(DECODE(Quarter, 3, Results)) AS Q3,
    5. MAX(DECODE(Quarter, 4, Results)) AS Q4
    6. FROM Sales
    7. GROUP BY Year;
    8. YEAR Q1 Q2 Q3 Q4
    9. ----------- ----------- ----------- ----------- -----------
    10. 2004 20 30 15 10
    11. 2005 18 40 12 27
    12. 2 record(s) selected.
    I noted above that this approach is the best. How do I know. Well let's look at the access plan:

    点击(此处)折叠或打开

    1. Access Plan:
    2. -----------
    3. Total Cost: 0.0134932
    4. Query Degree: 1
    5. Rows
    6. RETURN
    7. ( 1)
    8. Cost
    9. I/O
    10. |
    11. 1.6
    12. GRPBY
    13. ( 2)
    14. 0.01322
    15. 0
    16. |
    17. 8
    18. IXSCAN
    19. ( 3)
    20. 0.0126886
    21. 0
    22. |
    23. 8
    24. INDEX: SERGE
    25. PK_IDX
    26. Q1
    Pretty hard to beat!

  • UNPIVOT
    Unpivot is the inverse operation of PIVOT. Here we have several similar columns which are to be combined into one column, but different rows.
    Let's reuse the same example from above and run it backwards so to speak

    点击(此处)折叠或打开

    1. CREATE TABLE Sales(Year INTEGER NOT NULL PRIMARY KEY,
    2. Q1 INTEGER,
    3. Q2 INTEGER,
    4. Q3 INTEGER,
    5. Q4 INTEGER);
    6. INSERT INTO Sales VALUES
    7. (2004, 20, 30, 15, 10),
    8. (2005, 18, 40, 12, 27);
    The most efficient way to unpivot is to do a cross join between the Sales table and a correlated VALUES of as many rows as columns that need to be unpivoted.

    点击(此处)折叠或打开

    1. SELECT Year, Quarter, Results
    2. FROM Sales AS S,
    3. LATERAL(VALUES (1, S.Q1),
    4. (2, S.Q2),
    5. (3, S.Q3),
    6. (4, S.Q4)) AS T(Quarter, Results);
    7. YEAR QUARTER RESULTS
    8. ----------- ----------- -----------
    9. 2004 1 20
    10. 2004 2 30
    11. 2004 3 15
    12. 2004 4 10
    13. 2005 1 18
    14. 2005 2 40
    15. 2005 3 12
    16. 2005 4 27
    17. 8 record(s) selected.
    Again let's look at the explain:
  • Once more this is the most efficient plan. Since it only contains a single scan.
阅读(1875) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册