• Oracle行列转换函数--Pivot 和 Unpivot


    https://www.oracle.com/cn/database/articles/technology/pivot-and-unpivot.html

    Pivot 和 Unpivot
    使用简单的 SQL 以电子表格类型的交叉表报表显示任何关系表中的信息,并将交叉表中的所有数据存储到关系表中。

    Pivot
    如您所知,关系表是表格化的,即,它们以列-值对的形式出现。假设一个表名为 CUSTOMERS。

    COPY
    Copied to ClipboardError: Could not Copy
    SQL> desc customers
    Name Null? Type


    CUST_ID NUMBER(10)
    CUST_NAME VARCHAR2(20)
    STATE_CODE VARCHAR2(2)
    TIMES_PURCHASED NUMBER(3)
    选定该表:
    select cust_id, state_code, times_purchased
    from customers
    order by cust_id;
    输出结果如下:
    CUST_ID STATE_CODE TIMES_PURCHASED


      1 CT                       1
      2 NY                      10
      3 NJ                       2
      4 NY                       4
    

    ...

    and so on ...
    SQL> desc customers
    Name Null? Type


    CUST_ID NUMBER(10)
    CUST_NAME VARCHAR2(20)
    STATE_CODE VARCHAR2(2)
    TIMES_PURCHASED NUMBER(3)
    选定该表:
    select cust_id, state_code, times_purchased
    from customers
    order by cust_id;
    输出结果如下:
    CUST_ID STATE_CODE TIMES_PURCHASED


      1 CT                       1
      2 NY                      10
      3 NJ                       2
      4 NY                       4
    

    ...

    and so on ...
    注意数据是如何以行值的形式显示的:针对每个客户,该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时,列 times_purchased 会进行更新。

    现在,假设您希望统计一个报表,以了解各个州的购买频率,即,各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL,您可以执行以下语句:

    COPY
    Copied to ClipboardError: Could not Copy
    select state_code, times_purchased, count(1) cnt
    from customers
    group by state_code, times_purchased;
    输出如下:
    ST TIMES_PURCHASED CNT


    CT 0 90
    CT 1 165
    CT 2 179
    CT 3 173
    CT 4 173
    CT 5 152
    ...

    and so on ...
    select state_code, times_purchased, count(1) cnt
    from customers
    group by state_code, times_purchased;
    输出如下:
    ST TIMES_PURCHASED CNT


    CT 0 90
    CT 1 165
    CT 2 179
    CT 3 173
    CT 4 173
    CT 5 152
    ...

    and so on ...
    这就是您所要的信息,但是看起来不太方便。使用交叉表报表可能可以更好地显示这些数据,这样,您可以垂直排列数据,水平排列各个州,就像电子表格一样:

    COPY
    Copied to ClipboardError: Could not Copy
    Times_purchased
    CT NY NJ ...

    and so on ...

    1 0 1 0 ...
    2 23 119 37 ...
    3 17 45 1 ...
    ...

    and so on ...
    Times_purchased
    CT NY NJ ...

    and so on ...

    1 0 1 0 ...
    2 23 119 37 ...
    3 17 45 1 ...
    ...

    and so on ...
    在 Oracle 数据库 11g 推出之前,您需要针对每个值通过 decode 函数进行以上操作,并将每个不同的值编写为一个单独的列。但是,该方法一点也不直观。

    庆幸的是,您现在可以使用一种很棒的新特性 PIVOT 通过一种新的操作符以交叉表格式显示任何查询,该操作符相应地称为 pivot。下面是查询的编写方式:

    COPY
    Copied to ClipboardError: Could not Copy
    select * from (
    select times_purchased, state_code
    from customers t
    )
    pivot
    (
    count(state_code)
    for state_code in ('NY','CT','NJ','FL','MO')
    )
    order by times_purchased
    /
    select * from (
    select times_purchased, state_code
    from customers t
    )
    pivot
    (
    count(state_code)
    for state_code in ('NY','CT','NJ','FL','MO')
    )
    order by times_purchased
    /
    输出如下:

    COPY
    Copied to ClipboardError: Could not Copy
    . TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'


          0      16601         90          0          0          0
          1      33048        165          0          0          0
          2      33151        179          0          0          0
          3      32978        173          0          0          0
          4      33109        173          0          1          0
    

    ... and so on ...
    . TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'


          0      16601         90          0          0          0
          1      33048        165          0          0          0
          2      33151        179          0          0          0
          3      32978        173          0          0          0
          4      33109        173          0          1          0
    

    ... and so on ...
    这表明了 pivot 操作符的威力。state_codes 作为标题行而不是列显示。下面是传统的表格化格式的图示:

    Times Purchased

    图 1 传统的表格化显示

    在交叉表报表中,您希望将 Times Purchased 列的位置掉换到标题行,如图 2 所示。该列变为行,就好像该列逆时针旋转 90 度而变为标题行一样。该象征性的旋转需要有一个支点 (pivot point),在本例中,该支点为 count(state_code) 表达式。

    Times Purchased2

    图 2 执行了 Pivot 操作的显示

    该表达式需要采用以下查询语法:

    COPY
    Copied to ClipboardError: Could not Copy
    ...
    pivot
    (
    count(state_code)
    for state_code in ('NY','CT','NJ','FL','MO')
    )
    ...
    ...
    pivot
    (
    count(state_code)
    for state_code in ('NY','CT','NJ','FL','MO')
    )
    ...
    第二行“for state_code ...”限制查询对象仅为这些值。该行是必需的,因此不幸的是,您需要预先知道可能的值。该限制在 XML 格式的查询将有所放宽,如本文后面部分所述。

    注意输出中的标题行:

    COPY
    Copied to ClipboardError: Could not Copy
    . TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
    --------------- ---------- ---------- ---------- ---------- ----------
    列标题是来自表本身的数据:州代码。缩写可能已经相当清楚无需更多解释,但是假设您希望显示州名而非缩写(“Connecticut”而非“CT”),那又该如何呢?如果是这样,您需要在查询的 FOR 子句中进行一些调整,如下所示:
    select * from (
    select times_purchased as "Puchase Frequency", state_code
    from customers t
    )
    pivot
    (
    count(state_code)
    for state_code in ('NY' as "New York",'CT' "Connecticut",
    'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
    )
    order by 1
    /

    Puchase Frequency New York Connecticut New Jersey Florida Missouri


          0      16601         90           0          0          0
          1      33048        165           0          0          0
          2      33151        179           0          0          0
          3      32978        173           0          0          0
          4      33109        173           0          1          0
    

    ...

    and so on ...
    . TIMES_PURCHASED 'NY' 'CT' 'NJ' 'FL' 'MO'
    --------------- ---------- ---------- ---------- ---------- ----------
    列标题是来自表本身的数据:州代码。缩写可能已经相当清楚无需更多解释,但是假设您希望显示州名而非缩写(“Connecticut”而非“CT”),那又该如何呢?如果是这样,您需要在查询的 FOR 子句中进行一些调整,如下所示:
    select * from (
    select times_purchased as "Puchase Frequency", state_code
    from customers t
    )
    pivot
    (
    count(state_code)
    for state_code in ('NY' as "New York",'CT' "Connecticut",
    'NJ' "New Jersey",'FL' "Florida",'MO' as "Missouri")
    )
    order by 1
    /

    Puchase Frequency New York Connecticut New Jersey Florida Missouri


          0      16601         90           0          0          0
          1      33048        165           0          0          0
          2      33151        179           0          0          0
          3      32978        173           0          0          0
          4      33109        173           0          1          0
    

    ...

    and so on ...
    FOR 子句可以提供其中的值(这些值将成为列标题)的别名。

    Unpivot
    就像有物质就有反物质一样,有 pivot 就应该有“unpivot”,对吧?

    好了,不开玩笑,但 pivot 的反向操作确实需要。假设您有一个显示交叉表报表的电子表格,如下所示:

    Purchase Frequency New York Connecticut New Jersey Florida Missouri
    0 12 11 1 0 0
    1 900 14 22 98 78
    2 866 78 13 3 9
    ... .
    现在,您希望将这些数据加载到一个名为 CUSTOMERS 的关系表中:

    COPY
    Copied to ClipboardError: Could not Copy
    SQL> desc customers
    Name Null? Type


    CUST_ID NUMBER(10)
    CUST_NAME VARCHAR2(20)
    STATE_CODE VARCHAR2(2)
    TIMES_PURCHASED NUMBER(3)
    SQL> desc customers
    Name Null? Type


    CUST_ID NUMBER(10)
    CUST_NAME VARCHAR2(20)
    STATE_CODE VARCHAR2(2)
    TIMES_PURCHASED NUMBER(3)
    必须将电子表格数据去规范化为关系格式,然后再进行存储。当然,您可以使用 DECODE 编写一个复杂的 SQL*:Loader 或 SQL 脚本,以将数据加载到 CUSTOMERS 表中。或者,您可以使用 pivot 的反向操作 UNPIVOT,将列打乱变为行,这在 Oracle 数据库 11g 中可以实现。

    通过一个示例对此进行演示可能更简单。让我们首先使用 pivot 操作创建一个交叉表:

    COPY
    Copied to ClipboardError: Could not Copy
    1 create table cust_matrix
    2 as
    3 select * from (
    4 select times_purchased as "Puchase Frequency", state_code
    5 from customers t
    6 )
    7 pivot
    8 (
    9 count(state_code)
    10 for state_code in ('NY' as "New York",'CT' "Conn",
    'NJ' "New Jersey",'FL' "Florida",
    'MO' as "Missouri")
    11 )
    12* order by 1
    1 create table cust_matrix
    2 as
    3 select * from (
    4 select times_purchased as "Puchase Frequency", state_code
    5 from customers t
    6 )
    7 pivot
    8 (
    9 count(state_code)
    10 for state_code in ('NY' as "New York",'CT' "Conn",
    'NJ' "New Jersey",'FL' "Florida",
    'MO' as "Missouri")
    11 )
    12* order by 1
    您可以查看数据在表中的存储方式:

    COPY
    Copied to ClipboardError: Could not Copy
    SQL> select * from cust_matrix
    2 /

    Puchase Frequency New York Conn New Jersey Florida Missouri


                1      33048        165          0          0          0
                2      33151        179          0          0          0
                3      32978        173          0          0          0
                4      33109        173          0          1          0
    

    ... and so on ...
    SQL> select * from cust_matrix
    2 /

    Puchase Frequency New York Conn New Jersey Florida Missouri


                1      33048        165          0          0          0
                2      33151        179          0          0          0
                3      32978        173          0          0          0
                4      33109        173          0          1          0
    

    ... and so on ...
    这是数据在电子表格中的存储方式:每个州是表中的一个列(“New York”、“Conn”等等)。

    COPY
    Copied to ClipboardError: Could not Copy
    SQL> desc cust_matrix
    Name Null? Type


    Puchase Frequency NUMBER(3)
    New York NUMBER
    Conn NUMBER
    New Jersey NUMBER
    Florida NUMBER
    Missouri NUMBER
    SQL> desc cust_matrix
    Name Null? Type


    Puchase Frequency NUMBER(3)
    New York NUMBER
    Conn NUMBER
    New Jersey NUMBER
    Florida NUMBER
    Missouri NUMBER
    您需要将该表打乱,使行仅显示州代码和该州的购物人数。通过 unpivot 操作可以达到此目的,如下所示:

    COPY
    Copied to ClipboardError: Could not Copy
    select *
    from cust_matrix
    unpivot
    (
    state_counts
    for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
    )
    order by "Puchase Frequency", state_code
    /
    select *
    from cust_matrix
    unpivot
    (
    state_counts
    for state_code in ("New York","Conn","New Jersey","Florida","Missouri")
    )
    order by "Puchase Frequency", state_code
    /
    输出如下:

    输出如下:

    COPY
    Copied to ClipboardError: Could not Copy
    Puchase Frequency STATE_CODE STATE_COUNTS


                1 Conn                165
                1 Florida               0
                1 Missouri              0
                1 New Jersey            0
                1 New York          33048
                2 Conn                179
                2 Florida               0
                2 Missouri              0
    

    ...

    and so on ...
    Puchase Frequency STATE_CODE STATE_COUNTS


                1 Conn                165
                1 Florida               0
                1 Missouri              0
                1 New Jersey            0
                1 New York          33048
                2 Conn                179
                2 Florida               0
                2 Missouri              0
    

    ...

    and so on ...
    注意每个列名如何变为 STATE_CODE 列中的一个值。Oracle 如何知道 state_code 是一个列名?它是通过查询中的子句知道的,如下所示:

    for state_code in ("New York","Conn","New Jersey","Florida","Missouri")

    这里,您指定“New York”、“Conn”等值是您要对其执行 unpivot 操作的 state_code 新列的值。我们来看看部分原始数据:

  • 相关阅读:
    C# sqlhelp
    vs2015 C#打包程序为exe
    python3.6安装docx模块
    python 第八天
    python 第七天
    python 选课系统
    python 第六天
    python 模拟实现一个ATM + 购物商城程序
    python 计算器
    python 第五天
  • 原文地址:https://www.cnblogs.com/lipeng20004/p/13924205.html
Copyright © 2020-2023  润新知