1. <i id="s6b2k"><small id="s6b2k"></small></i>
    <b id="s6b2k"><bdo id="s6b2k"></bdo></b>
  2. <wbr id="s6b2k"></wbr>

    詳解Oracle 中實現數據透視表的幾種方法_oracle

    來源:腳本之家  責任編輯:小易  

    數據透視表(Pivot Table)是 Excel 中一個非常實用的分析功能,可以用于實現復雜的數據分類匯總和對比分析,是數據分析師和運營人員必備技能之一。今天我們來談談如何在 Oracle 數據庫中實現數據透視表。

    本文使用的示例數據可以點此下載

    使用 CASE 表達式實現數據透視表

    數據透視表的本質就是按照行和列的不同組合進行數據分組,然后對結果進行匯總;因此,它和數據庫中的分組(GROUP BY)和聚合函數(COUNT、SUM、AVG 等)的功能非常類似。

    我們首先使用以下 GROUP BY 子句對銷售數據進行分類匯總:

    select coalesce(product, '【全部產品】') "產品",
        coalesce(channel, '【所有渠道】') "渠道",
        coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份",
        sum(amount) "銷量"
    from sales_data
    group by rollup (product,channel,to_char(saledate, 'YYYYMM'));
    
    

    以上語句按照產品、渠道以及月份進行匯總;rollup 選項用于生成不同層次的小計、合計以及總計;coalesce 函數用于將匯總行中的 NULL 值顯示為相應的信息。查詢返回的結果如下:

    產品      |渠道      |月份       |銷量    |
    ---------|---------|-----------|-------|
    桔子      |京東      |201901    |  41289|
    桔子      |京東      |201902    |  43913|
    桔子      |京東      |201903    |  49803|
    桔子      |京東      |201904    |  49256|
    桔子      |京東      |201905    |  64889|
    桔子      |京東      |201906    |  62649|
    桔子      |京東      |【所有月份】| 311799|
    桔子      |店面      |201901    |  41306|
    桔子      |店面      |201902    |  37906|
    桔子      |店面      |201903    |  48866|
    桔子      |店面      |201904    |  48673|
    桔子      |店面      |201905    |  58998|
    桔子      |店面      |201906    |  58931|
    桔子      |店面      |【所有月份】| 294680|
    桔子      |淘寶      |201901    |  43488|
    桔子      |淘寶      |201902    |  37598|
    桔子      |淘寶      |201903    |  48621|
    桔子      |淘寶      |201904    |  49919|
    桔子      |淘寶      |201905    |  58530|
    桔子      |淘寶      |201906    |  64626|
    桔子      |淘寶      |【所有月份】| 302782|
    桔子      |【所有渠道】|【所有月份】| 909261|
    ...
    香蕉      |【所有渠道】|【所有月份】| 925369|
    【全部產品】|【所有渠道】|【所有月份】|2771682|

    實際上,我們已經得到了數據透視表的匯總結果,只不過需要將數據按照不同月份顯示為不同的列;也就是需要將行轉換為列,這個功能可以使用 CASE 表達式實現:

    select coalesce(product, '【全部產品】') "產品", coalesce(channel, '【所有渠道】') "渠道", 
        sum(case to_char(saledate, 'YYYYMM') when '201901' then amount else 0 end) "一月",
        sum(case to_char(saledate, 'YYYYMM') when '201902' then amount else 0 end) "二月",
        sum(case to_char(saledate, 'YYYYMM') when '201903' then amount else 0 end) "三月",
        sum(case to_char(saledate, 'YYYYMM') when '201904' then amount else 0 end) "四月",
        sum(case to_char(saledate, 'YYYYMM') when '201905' then amount else 0 end) "五月",
        sum(case to_char(saledate, 'YYYYMM') when '201906' then amount else 0 end) "六月",
        sum(amount) "總計"
    from sales_data
    group by rollup (product, channel);
    
    

    第一個 SUM 函數中的 CASE 表達式只匯總 201901 月份的銷量,其他月份銷量設置為 0;后面的 SUM 函數依次類推,得到了每個月的銷量匯總和所有月份的總計。

    產品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計   |
    ----------|----------|------|------|------|------|------|------|-------|
    桔子       |京東      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
    桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
    桔子       |淘寶      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
    桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
    蘋果       |京東      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
    蘋果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
    蘋果       |淘寶      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
    蘋果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
    香蕉       |京東      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
    香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
    香蕉       |淘寶      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
    香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
    【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

    Oracle 中的 decode 函數也可以實現類似 CASE 表達式的功能。

    以上實現數據透視表的方法存在一定的局限性,假如還有 7 月份到 12 月份的銷量需要統計,我們就需要修改查詢語句增加這部分的處理。因此,Oracle 11g 引入了一個新的子句來實現自動的行轉列:PIVOT。

    使用 PIVOT 子句實現數據透視表

    Oracle 中的 PIVOT 子句用于將行轉換為列,基本語法如下:

    SELECT col1, col2, ...
    FROM tbl
    PIVOT (
     pivot_clause,
     pivot_for_clause,
     pivot_in_clause
    );

    PIVOT 子句包含 3 個部分:

    pivot_clause,定義需要匯總的數據,也就是聚合函數。例如使用 SUM(amount) 匯總銷量; pivot_for_clause,指定需要從行轉換成列的字段。例如使用 for saledate 將每個月的數據顯示為一列; pivot_in_clause,指定將 pivot_for_clause 字段中的哪些數據值轉換為列。例如 in (‘201901', ‘201902') 表示只將 201901 和 201902 兩個月份的數據轉換為列。

    對于上文中的示例,我們可以使用以下 PIVOT 子句:

    with d(saledate, product, channel, amount) as (
     select to_char(saledate, 'YYYYMM'),
         product,
         channel,
         amount
     from sales_data
    )
    select *
    from d
    pivot (
     sum(amount)
     for saledate
     in ('201901', '201902', '201903', '201904', '201905', '201906')
    )
    order by product, channel;

    其中,PIVOT 子句按照月份對銷量進行匯總并且將月份轉換為列顯示,返回的結果如下:

    PRODUCT  |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'|
    ---------|--------|--------|--------|--------|--------|--------|--------|
    桔子     |京東     |   41289|   43913|   49803|   49256|   64889|   62649|
    桔子     |店面     |   41306|   37906|   48866|   48673|   58998|   58931|
    桔子     |淘寶     |   43488|   37598|   48621|   49919|   58530|   64626|
    蘋果     |京東     |   38269|   40593|   56552|   56662|   64493|   62045|
    蘋果     |店面     |   43845|   40539|   44909|   55646|   56771|   64933|
    蘋果     |淘寶     |   42969|   43289|   48769|   58052|   58872|   59844|
    香蕉     |京東     |   36879|   36981|   51748|   54801|   64936|   60688|
    香蕉     |店面     |   41210|   39420|   50884|   52085|   60249|   67597|
    香蕉     |淘寶     |   42468|   41955|   52780|   54971|   56504|   59213|

    接下來我們需要增加一個總計行和總計列,為此可以先將 sales_data 數據進行分組統計然后再使用 PIVOT 子句進行轉換:

    with d(saledate, product, channel, amount) as (
     select to_char(saledate, 'YYYYMM'),
         product,
         channel,
         sum(amount)
     from sales_data
     group by rollup (to_char(saledate, 'YYYYMM'), product, channel)
    ), pt as (
     select *
     from d
     pivot (
      sum(amount)
      for saledate
      in ('201901' s01, '201902' s02, '201903' s03, '201904' s04, '201905' s05, '201906' s06)
     )
    )
    select coalesce(product, '【全部產品】') "產品",
        coalesce(channel, '【所有渠道】') "渠道",
        s01 "一月", s02 "二月", s03 "三月", s04 "四月", s05 "五月", s06 "六月",
        s01+s02+s03+s04+s05+s06 "總計"
    from pt
    order by product, channel;

    我們在 PIVOT 子句返回的結果之上增加了一個 SELECT 查詢,并且修改了返回字段的名稱,讓結果更加接近 EXCEL 數據透視表:

    產品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計   |
    ----------|----------|------|------|------|------|------|------|-------|
    桔子       |京東      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
    桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
    桔子       |淘寶      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
    桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
    蘋果       |京東      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
    蘋果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
    蘋果       |淘寶      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
    蘋果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
    香蕉       |京東      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
    香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
    香蕉       |淘寶      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
    香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
    【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

    PIVOT 子句也可以一次執行多個聚合操作,或者按照多個字段進行分組。例如:

    with d(saledate, product, channel, amount) as (
     select to_char(saledate, 'YYYYMM'), product, channel, amount
     from sales_data
     where to_char(saledate, 'YYYYMM') in ('201901', '201902', '201903')
    )
    select *
    from d
    pivot (
     sum(amount)
     for (channel, saledate)
     in (('淘寶','201901'), ('店面','201901'), ('京東','201901'),
       ('淘寶','201902'), ('店面','201902'), ('京東','201902'),
       ('淘寶','201903'), ('店面','201903'), ('京東','201903'))
    );
    
    PRODUCT|'淘寶'_'201901'|'店面'_'201901'|'京東'_'201901'|'淘寶'_'201902'|'店面'_'201902'|'京東'_'201902'|'淘寶'_'201903'|'店面'_'201903'|'京東'_'201903'|
    -------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
    香蕉  |     42468|     41210|     36879|     41955|     39420|     36981|     52780|     50884|     51748|
    桔子  |     43488|     41306|     41289|     37598|     37906|     43913|     48621|     48866|     49803|
    蘋果  |     42969|     43845|     38269|     43289|     40539|     40593|     48769|     44909|     56552|
    

    以上查詢返回了按照渠道和月份分組的匯總結果,并且將它們轉換為列進行顯示。

    與 PIVOT 相反的操作是 UNPIVOT,它可以將列轉換為行。我們通過以下示例將行專列之后的數據再轉換回來:

    with d(saledate, product, channel, amount) as (
     select to_char(saledate, 'YYYYMM'),
         product,
         channel,
         amount
     from sales_data
    ),
    pt as (
     select *
     from d
     pivot (
      sum(amount)
      for saledate
      in ('201901' "201901", '201902' "201902", '201903' "201903", '201904' "201904", '201905' "201905", '201906' "201906")
     )
    )
    select * from pt
    unpivot (
     amount
     for saledate
     IN ("201901", "201902", "201903", "201904", "201905", "201906")
    );

    其中,unpivot 子句也有三個選項,將每個月份代表的列轉換為 saledate 字段中的行,并且將對應的數據轉換為 amount 字段中的行。以上查詢返回的結果如下:

    PRODUCT |CHANNEL |SALEDATE|AMOUNT|
    --------|--------|--------|------|
    桔子     |京東     |201901  | 41289|
    桔子     |京東     |201902  | 43913|
    桔子     |京東     |201903  | 49803|
    桔子     |京東     |201904  | 49256|
    桔子     |京東     |201905  | 64889|
    桔子     |京東     |201906  | 62649|
    香蕉     |店面     |201901  | 41210|
    香蕉     |店面     |201902  | 39420|
    香蕉     |店面     |201903  | 50884|
    香蕉     |店面     |201904  | 52085|
    香蕉     |店面     |201905  | 60249|
    香蕉     |店面     |201906  | 67597|
    ...

    如果想要解鎖更多的 PIVOT 和 UNPIVOT 的使用姿勢,可以參考官方文檔中的定義示例

    使用 MODEL 子句實現數據透視表

    除了 PIVOT 子句之外,Oracle 還提供一個更加強大的功能:MODEL 子句。簡單來說,MODEL 子句可以實現 EXCEL 等電子表格中基于位置和符號的單元格引用以及復雜的公式計算。

    完整的 MODEL 子句比較復雜,我們直接看一個示例:

    with d(saledate, product, channel, amount) as (
     select to_char(saledate, 'YYYYMM'), product, channel, sum(amount)
     from sales_data
     group by rollup (to_char(saledate, 'YYYYMM'), product, channel)
    )
    select coalesce(product, '【全部產品】') "產品",
        coalesce(channel, '【所有渠道】') "渠道",
        s201901 "一月", s201902 "二月", s201903 "三月", s201904 "四月", s201905 "五月", s201906 "六月",
        stotal "總計"
    from d
    model 
     return updated rows
     partition by (product, channel)
     dimension by (saledate)
     measures (amount, 0 s201901, 0 s201902, 0 s201903, 0 s201904, 0 s201905, 0 s201906, 0 stotal)
     unique dimension
     rules upsert all
     (s201901[0] = amount['201901'],
      s201902[0] = amount['201902'],
      s201903[0] = amount['201903'],
      s201904[0] = amount['201904'],
      s201905[0] = amount['201905'],
      s201906[0] = amount['201906'],
      stotal[0] = sum(amount)[saledate between '201901' and '201906'])
    order by product, channel;

    首先,通過 with 子句獲得基本數據。然后使用 model 子句實現行專列;return updated rows 表示只返回計算模型更新和插入的數據,partition by 用于定義分區(產品和渠道),每個分區獨立計算;dimension by 指定度量的維度(月份);measures 定義度量,amount 來自源表,0 s201901 表示創建一個度量 s201901 并初始化為 0;unique dimension 表示 partition by 加 dimension by 字段可以唯一確定模型中的每個單元格;rules 用于定義給每個度量賦值的表達式,upsert all 表示更新已有的單元格,如果不存在則創建單元格;s201901[0] 是通過位置對單元格的引用(維度為 1),amount[‘201901'] 表示月份 201901 對應的 amount 字段值,stotal[0] 是所有月份的總和。

    以上語句返回的結果如下:

    產品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計   |
    ----------|----------|------|------|------|------|------|------|-------|
    桔子       |京東      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
    桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
    桔子       |淘寶      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
    桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
    蘋果       |京東      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
    蘋果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
    蘋果       |淘寶      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
    蘋果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
    香蕉       |京東      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
    香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
    香蕉       |淘寶      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
    香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
    【全部產品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

    MODEL 子句允許通過分區(PARTITION BY)和維度(DIMENSION BY)創建一個多維數組,并且通過指定規則(RULES)來操作和更新數組中單元格中的度量值(MEASURES)。其中,規則支持通配符和循環迭代,度量可以使用聚合函數和窗口函數。

    MODEL 子句完整的使用姿勢請參考官方文檔

    到此這篇關于詳解Oracle 中實現數據透視表的幾種方法的文章就介紹到這了,更多相關Oracle 數據透視表內容請搜索真格學網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持真格學網!

    您可能感興趣的文章:python 用pandas實現數據透視表功能Java Excel透視表相關操作實現代碼Java在Excel中創建透視表方法解析Pandas透視表(pivot_table)詳解pandas 透視表中文字段排序方法C#如何操作Excel數據透視表

  3. 本文相關:
  4. oracle出現錯誤1033和錯誤ora-00600的解決方法
  5. oracle 11g如何清理數據庫的歷史日志詳解
  6. oracle round()函數與trunc()函數區別介紹
  7. oracle應用經驗(1)
  8. 使用jdbc連接oracle的三種url格式
  9. oracle學習筆記(四)
  10. oracle數據庫逐步解決ora-12541、ora-01034和ora-27101、ora-001
  11. oracle生成不重復票號與lpad,rpad與nextval函數解析
  12. oracle開發之分析函數簡介over用法
  13. oracle 數據庫隔離級別學習
  14. oracle 數據透視表
  15. oracle如何實現同時查表中幾列數據的視圖
  16. excel中,數據透視表的具體使用方法
  17. 如何將幾個表格的數據做成一個透視表?
  18. 數據透視表模版 有幾種,如何轉換
  19. 數據透視表幾個列標簽并列,每個列標簽對應相應列...
  20. 如何查詢數據透視表中的詳細數據
  21. 數據透視表 如何取消分類匯總
  22. 怎樣在EXCEL中實現兩個獨立數據透視表合并成一個數...
  23. Excel中怎么用公式實現類似數據透視表的功能?
  24. 網站首頁網頁制作腳本下載服務器操作系統網站運營平面設計媒體動畫電腦基礎硬件教程網絡安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess數據庫文摘數據庫其它首頁oraclepython 用pandas實現數據透視表功能java excel透視表相關操作實現代碼java在excel中創建透視表方法解析pandas透視表(pivot_table)詳解pandas 透視表中文字段排序方法c#如何操作excel數據透視表oracle出現錯誤1033和錯誤ora-00600的解決方法oracle 11g如何清理數據庫的歷史日志詳解oracle round()函數與trunc()函數區別介紹oracle應用經驗(1)使用jdbc連接oracle的三種url格式oracle學習筆記(四)oracle數據庫逐步解決ora-12541、ora-01034和ora-27101、ora-001oracle生成不重復票號與lpad,rpad與nextval函數解析oracle開發之分析函數簡介over用法oracle 數據庫隔離級別學習oracle 查看表空間的大小及使用情linux系統(x64)安裝oracle11g完oracle數據庫下載及安裝圖文操作oracle存儲過程基本語法介紹oracle 10g 安裝教程[圖文]oracle 如何查詢被鎖定表及如何解基于ora-12170 tns 連接超時解決oracle中to_date詳細用法示例(ororacle數據庫tns配置方法詳解oracle 創建表空間詳細介紹解決windows 7下安裝oracle 11g相關問題的深入淺析orcale的nvl函數和sql server的ioracle 阻塞(blocking blocked)介紹和實例oracle 索引的相關介紹(創建、簡介、技巧oracle中的procedure編寫和使用詳解mybatis出現ora-00911: invalid characteorcale權限、角色查看創建方法oracle停止數據庫后linux完全卸載oracle的淺析oracle中sys、system和scott用戶下的oracle常用分析函數與聚合函數的用法
    免責聲明 - 關于我們 - 聯系我們 - 廣告聯系 - 友情鏈接 - 幫助中心 - 頻道導航
    Copyright © 2017 www.yu113.com All Rights Reserved
    战天txt全集下载