詳解Oracle 中實(shí)現(xiàn)數(shù)據(jù)透視表的幾種方法

數(shù)據(jù)透視表（Pivot Table）是 Excel 中一個(gè)非常實(shí)用的分析功能，可以用于實(shí)現(xiàn)復(fù)雜的數(shù)據(jù)分類匯總和對(duì)比分析，是數(shù)據(jù)分析師和運(yùn)營(yíng)人員必備技能之一。今天我們來談?wù)勅绾卧?Oracle 數(shù)據(jù)庫(kù)中實(shí)現(xiàn)數(shù)據(jù)透視表。

本文使用的示例數(shù)據(jù)可以點(diǎn)此下載。

使用 CASE 表達(dá)式實(shí)現(xiàn)數(shù)據(jù)透視表

數(shù)據(jù)透視表的本質(zhì)就是按照行和列的不同組合進(jìn)行數(shù)據(jù)分組，然后對(duì)結(jié)果進(jìn)行匯總；因此，它和數(shù)據(jù)庫(kù)中的分組（GROUP BY）和聚合函數(shù)（COUNT、SUM、AVG 等）的功能非常類似。

我們首先使用以下 GROUP BY 子句對(duì)銷售數(shù)據(jù)進(jìn)行分類匯總：

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

以上語句按照產(chǎn)品、渠道以及月份進(jìn)行匯總；rollup 選項(xiàng)用于生成不同層次的小計(jì)、合計(jì)以及總計(jì)；coalesce 函數(shù)用于將匯總行中的 NULL 值顯示為相應(yīng)的信息。查詢返回的結(jié)果如下：

產(chǎn)品      |渠道      |月份       |銷量    |
---------|---------|-----------|-------|
桔子      |京東      |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|
【全部產(chǎn)品】|【所有渠道】|【所有月份】|2771682|

實(shí)際上，我們已經(jīng)得到了數(shù)據(jù)透視表的匯總結(jié)果，只不過需要將數(shù)據(jù)按照不同月份顯示為不同的列；也就是需要將行轉(zhuǎn)換為列，這個(gè)功能可以使用 CASE 表達(dá)式實(shí)現(xiàn)：

select coalesce(product, '【全部產(chǎn)品】') "產(chǎn)品", 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) "總計(jì)"
from sales_data
group by rollup (product, channel);

第一個(gè) SUM 函數(shù)中的 CASE 表達(dá)式只匯總 201901 月份的銷量，其他月份銷量設(shè)置為 0；后面的 SUM 函數(shù)依次類推，得到了每個(gè)月的銷量匯總和所有月份的總計(jì)。

產(chǎn)品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計(jì)   |
----------|----------|------|------|------|------|------|------|-------|
桔子       |京東      | 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|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

Oracle 中的 decode 函數(shù)也可以實(shí)現(xiàn)類似 CASE 表達(dá)式的功能。

以上實(shí)現(xiàn)數(shù)據(jù)透視表的方法存在一定的局限性，假如還有 7 月份到 12 月份的銷量需要統(tǒng)計(jì)，我們就需要修改查詢語句增加這部分的處理。因此，Oracle 11g 引入了一個(gè)新的子句來實(shí)現(xiàn)自動(dòng)的行轉(zhuǎn)列：PIVOT。

使用 PIVOT 子句實(shí)現(xiàn)數(shù)據(jù)透視表

Oracle 中的 PIVOT 子句用于將行轉(zhuǎn)換為列，基本語法如下：

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

PIVOT 子句包含 3 個(gè)部分：

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

對(duì)于上文中的示例，我們可以使用以下 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 子句按照月份對(duì)銷量進(jìn)行匯總并且將月份轉(zhuǎn)換為列顯示，返回的結(jié)果如下：

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|

接下來我們需要增加一個(gè)總計(jì)行和總計(jì)列，為此可以先將 sales_data 數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)然后再使用 PIVOT 子句進(jìn)行轉(zhuǎn)換：

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, '【全部產(chǎn)品】') "產(chǎn)品",
    coalesce(channel, '【所有渠道】') "渠道",
    s01 "一月", s02 "二月", s03 "三月", s04 "四月", s05 "五月", s06 "六月",
    s01+s02+s03+s04+s05+s06 "總計(jì)"
from pt
order by product, channel;

我們?cè)?PIVOT 子句返回的結(jié)果之上增加了一個(gè) SELECT 查詢，并且修改了返回字段的名稱，讓結(jié)果更加接近 EXCEL 數(shù)據(jù)透視表：

產(chǎn)品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計(jì)   |
----------|----------|------|------|------|------|------|------|-------|
桔子       |京東      | 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|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

PIVOT 子句也可以一次執(zhí)行多個(gè)聚合操作，或者按照多個(gè)字段進(jìn)行分組。例如：

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|

以上查詢返回了按照渠道和月份分組的匯總結(jié)果，并且將它們轉(zhuǎn)換為列進(jìn)行顯示。

與 PIVOT 相反的操作是 UNPIVOT，它可以將列轉(zhuǎn)換為行。我們通過以下示例將行專列之后的數(shù)據(jù)再轉(zhuǎn)換回來：

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 子句也有三個(gè)選項(xiàng)，將每個(gè)月份代表的列轉(zhuǎn)換為 saledate 字段中的行，并且將對(duì)應(yīng)的數(shù)據(jù)轉(zhuǎn)換為 amount 字段中的行。以上查詢返回的結(jié)果如下：

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 的使用姿勢(shì)，可以參考官方文檔中的定義和示例。

使用 MODEL 子句實(shí)現(xiàn)數(shù)據(jù)透視表

除了 PIVOT 子句之外，Oracle 還提供一個(gè)更加強(qiáng)大的功能：MODEL 子句。簡(jiǎn)單來說，MODEL 子句可以實(shí)現(xiàn) EXCEL 等電子表格中基于位置和符號(hào)的單元格引用以及復(fù)雜的公式計(jì)算。

完整的 MODEL 子句比較復(fù)雜，我們直接看一個(gè)示例：

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, '【全部產(chǎn)品】') "產(chǎn)品",
    coalesce(channel, '【所有渠道】') "渠道",
    s201901 "一月", s201902 "二月", s201903 "三月", s201904 "四月", s201905 "五月", s201906 "六月",
    stotal "總計(jì)"
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 子句獲得基本數(shù)據(jù)。然后使用 model 子句實(shí)現(xiàn)行專列；return updated rows 表示只返回計(jì)算模型更新和插入的數(shù)據(jù)，partition by 用于定義分區(qū)（產(chǎn)品和渠道），每個(gè)分區(qū)獨(dú)立計(jì)算；dimension by 指定度量的維度（月份）；measures 定義度量，amount 來自源表，0 s201901 表示創(chuàng)建一個(gè)度量 s201901 并初始化為 0；unique dimension 表示 partition by 加 dimension by 字段可以唯一確定模型中的每個(gè)單元格；rules 用于定義給每個(gè)度量賦值的表達(dá)式，upsert all 表示更新已有的單元格，如果不存在則創(chuàng)建單元格；s201901[0] 是通過位置對(duì)單元格的引用（維度為 1），amount[‘201901'] 表示月份 201901 對(duì)應(yīng)的 amount 字段值，stotal[0] 是所有月份的總和。

以上語句返回的結(jié)果如下：

產(chǎn)品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |總計(jì)   |
----------|----------|------|------|------|------|------|------|-------|
桔子       |京東      | 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|
【全部產(chǎn)品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|

MODEL 子句允許通過分區(qū)（PARTITION BY）和維度（DIMENSION BY）創(chuàng)建一個(gè)多維數(shù)組，并且通過指定規(guī)則（RULES）來操作和更新數(shù)組中單元格中的度量值（MEASURES）。其中，規(guī)則支持通配符和循環(huán)迭代，度量可以使用聚合函數(shù)和窗口函數(shù)。

MODEL 子句完整的使用姿勢(shì)請(qǐng)參考官方文檔。

到此這篇關(guān)于詳解Oracle 中實(shí)現(xiàn)數(shù)據(jù)透視表的幾種方法的文章就介紹到這了,更多相關(guān)Oracle 數(shù)據(jù)透視表內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！