Oracle 中实现数据透视表的几种方法

文章目录

        使用 CASE 表达式实现数据透视表
        使用 PIVOT 子句实现数据透视表
        使用 MODEL 子句实现数据透视表

数据透视表（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)

更多请见：http://www.mark-to-win.com/tutorial/51525.html