• 售前

  • 售后

热门帖子
入门百科

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

[复制链接]
123456833 显示全部楼层 发表于 2021-8-14 15:02:24 |阅读模式 打印 上一主题 下一主题
数据透视表(Pivot Table)是 Excel 中一个非常实用的分析功能,可以用于实现复杂的数据分类汇总和对比分析,是数据分析师和运营职员必备技能之一。本日我们来谈谈怎样在 Oracle 数据库中实现数据透视表。
本文使用的示例数据可以点此下载。
使用 CASE 表达式实现数据透视表


数据透视表的本质就是按照行和列的差别组合进行数据分组,然后对结果进行汇总;因此,它和数据库中的分组(GROUP BY)和聚合函数(COUNT、SUM、AVG 等)的功能非常类似。
我们首先使用以下 GROUP BY 子句对贩卖数据进行分类汇总:
  1. select coalesce(product, '【全部产品】') "产品",
  2.     coalesce(channel, '【所有渠道】') "渠道",
  3.     coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份",
  4.     sum(amount) "销量"
  5. from sales_data
  6. group by rollup (product,channel,to_char(saledate, 'YYYYMM'));
复制代码
以上语句按照产物、渠道以及月份进行汇总;rollup 选项用于生成差别条理的小计、合计以及总计;coalesce 函数用于将汇总行中的 NULL 值表现为相应的信息。查询返回的结果如下:
  1. 产品      |渠道      |月份       |销量    |
  2. ---------|---------|-----------|-------|
  3. 桔子      |京东      |201901    |  41289|
  4. 桔子      |京东      |201902    |  43913|
  5. 桔子      |京东      |201903    |  49803|
  6. 桔子      |京东      |201904    |  49256|
  7. 桔子      |京东      |201905    |  64889|
  8. 桔子      |京东      |201906    |  62649|
  9. 桔子      |京东      |【所有月份】| 311799|
  10. 桔子      |店面      |201901    |  41306|
  11. 桔子      |店面      |201902    |  37906|
  12. 桔子      |店面      |201903    |  48866|
  13. 桔子      |店面      |201904    |  48673|
  14. 桔子      |店面      |201905    |  58998|
  15. 桔子      |店面      |201906    |  58931|
  16. 桔子      |店面      |【所有月份】| 294680|
  17. 桔子      |淘宝      |201901    |  43488|
  18. 桔子      |淘宝      |201902    |  37598|
  19. 桔子      |淘宝      |201903    |  48621|
  20. 桔子      |淘宝      |201904    |  49919|
  21. 桔子      |淘宝      |201905    |  58530|
  22. 桔子      |淘宝      |201906    |  64626|
  23. 桔子      |淘宝      |【所有月份】| 302782|
  24. 桔子      |【所有渠道】|【所有月份】| 909261|
  25. ...
  26. 香蕉      |【所有渠道】|【所有月份】| 925369|
  27. 【全部产品】|【所有渠道】|【所有月份】|2771682|
复制代码
实际上,我们已经得到了数据透视表的汇总结果,只不过需要将数据按照差别月份表现为差别的列;也就是需要将行转换为列,这个功能可以使用 CASE 表达式实现:
  1. select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",
  2.     sum(case to_char(saledate, 'YYYYMM') when '201901' then amount else 0 end) "一月",
  3.     sum(case to_char(saledate, 'YYYYMM') when '201902' then amount else 0 end) "二月",
  4.     sum(case to_char(saledate, 'YYYYMM') when '201903' then amount else 0 end) "三月",
  5.     sum(case to_char(saledate, 'YYYYMM') when '201904' then amount else 0 end) "四月",
  6.     sum(case to_char(saledate, 'YYYYMM') when '201905' then amount else 0 end) "五月",
  7.     sum(case to_char(saledate, 'YYYYMM') when '201906' then amount else 0 end) "六月",
  8.     sum(amount) "总计"
  9. from sales_data
  10. group by rollup (product, channel);
复制代码
第一个 SUM 函数中的 CASE 表达式只汇总 201901 月份的销量,其他月份销量设置为 0;背面的 SUM 函数依次类推,得到了每个月的销量汇总和全部月份的总计。
  1. 产品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |总计   |
  2. ----------|----------|------|------|------|------|------|------|-------|
  3. 桔子       |京东      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
  4. 桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
  5. 桔子       |淘宝      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
  6. 桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
  7. 苹果       |京东      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
  8. 苹果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
  9. 苹果       |淘宝      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
  10. 苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
  11. 香蕉       |京东      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
  12. 香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
  13. 香蕉       |淘宝      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
  14. 香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
  15. 【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
复制代码
Oracle 中的 decode 函数也可以实现类似 CASE 表达式的功能。
以上实现数据透视表的方法存在肯定的范围性,假如还有 7 月份到 12 月份的销量需要统计,我们就需要修改查询语句增长这部分的处理。因此,Oracle 11g 引入了一个新的子句来实现主动的行转列:PIVOT。
使用 PIVOT 子句实现数据透视表


Oracle 中的 PIVOT 子句用于将行转换为列,根本语法如下:
  1. SELECT col1, col2, ...
  2. FROM tbl
  3. PIVOT (
  4. pivot_clause,
  5. pivot_for_clause,
  6. pivot_in_clause
  7. );
复制代码
PIVOT 子句包罗 3 个部分:
      
  • pivot_clause,界说需要汇总的数据,也就是聚合函数。比方使用 SUM(amount) 汇总销量;  
  • pivot_for_clause,指定需要从行转换成列的字段。比方使用 for saledate 将每个月的数据表现为一列;  
  • pivot_in_clause,指定将 pivot_for_clause 字段中的哪些数据值转换为列。比方 in (‘201901', ‘201902') 表现只将 201901 和 201902 两个月份的数据转换为列。
对于上文中的示例,我们可以使用以下 PIVOT 子句:
  1. with d(saledate, product, channel, amount) as (
  2. select to_char(saledate, 'YYYYMM'),
  3.      product,
  4.      channel,
  5.      amount
  6. from sales_data
  7. )
  8. select *
  9. from d
  10. pivot (
  11. sum(amount)
  12. for saledate
  13. in ('201901', '201902', '201903', '201904', '201905', '201906')
  14. )
  15. order by product, channel;
复制代码
其中,PIVOT 子句按照月份对销量进行汇总并且将月份转换为列表现,返回的结果如下:
  1. PRODUCT  |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'|
  2. ---------|--------|--------|--------|--------|--------|--------|--------|
  3. 桔子     |京东     |   41289|   43913|   49803|   49256|   64889|   62649|
  4. 桔子     |店面     |   41306|   37906|   48866|   48673|   58998|   58931|
  5. 桔子     |淘宝     |   43488|   37598|   48621|   49919|   58530|   64626|
  6. 苹果     |京东     |   38269|   40593|   56552|   56662|   64493|   62045|
  7. 苹果     |店面     |   43845|   40539|   44909|   55646|   56771|   64933|
  8. 苹果     |淘宝     |   42969|   43289|   48769|   58052|   58872|   59844|
  9. 香蕉     |京东     |   36879|   36981|   51748|   54801|   64936|   60688|
  10. 香蕉     |店面     |   41210|   39420|   50884|   52085|   60249|   67597|
  11. 香蕉     |淘宝     |   42468|   41955|   52780|   54971|   56504|   59213|
复制代码
接下来我们需要增长一个总计行和总计列,为此可以先将 sales_data 数据进行分组统计然后再使用 PIVOT 子句进行转换:
  1. with d(saledate, product, channel, amount) as (
  2. select to_char(saledate, 'YYYYMM'),
  3.      product,
  4.      channel,
  5.      sum(amount)
  6. from sales_data
  7. group by rollup (to_char(saledate, 'YYYYMM'), product, channel)
  8. ), pt as (
  9. select *
  10. from d
  11. pivot (
  12.   sum(amount)
  13.   for saledate
  14.   in ('201901' s01, '201902' s02, '201903' s03, '201904' s04, '201905' s05, '201906' s06)
  15. )
  16. )
  17. select coalesce(product, '【全部产品】') "产品",
  18.     coalesce(channel, '【所有渠道】') "渠道",
  19.     s01 "一月", s02 "二月", s03 "三月", s04 "四月", s05 "五月", s06 "六月",
  20.     s01+s02+s03+s04+s05+s06 "总计"
  21. from pt
  22. order by product, channel;
复制代码
我们在 PIVOT 子句返回的结果之上增长了一个 SELECT 查询,并且修改了返回字段的名称,让结果更加靠近 EXCEL 数据透视表:
  1. 产品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |总计   |
  2. ----------|----------|------|------|------|------|------|------|-------|
  3. 桔子       |京东      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
  4. 桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
  5. 桔子       |淘宝      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
  6. 桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
  7. 苹果       |京东      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
  8. 苹果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
  9. 苹果       |淘宝      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
  10. 苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
  11. 香蕉       |京东      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
  12. 香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
  13. 香蕉       |淘宝      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
  14. 香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
  15. 【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
复制代码
PIVOT 子句也可以一次实验多个聚合操作,或者按照多个字段进行分组。比方:
  1. with d(saledate, product, channel, amount) as (
  2. select to_char(saledate, 'YYYYMM'), product, channel, amount
  3. from sales_data
  4. where to_char(saledate, 'YYYYMM') in ('201901', '201902', '201903')
  5. )
  6. select *
  7. from d
  8. pivot (
  9. sum(amount)
  10. for (channel, saledate)
  11. in (('淘宝','201901'), ('店面','201901'), ('京东','201901'),
  12.    ('淘宝','201902'), ('店面','201902'), ('京东','201902'),
  13.    ('淘宝','201903'), ('店面','201903'), ('京东','201903'))
  14. );
  15. PRODUCT|'淘宝'_'201901'|'店面'_'201901'|'京东'_'201901'|'淘宝'_'201902'|'店面'_'201902'|'京东'_'201902'|'淘宝'_'201903'|'店面'_'201903'|'京东'_'201903'|
  16. -------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|--------------|
  17. 香蕉  |     42468|     41210|     36879|     41955|     39420|     36981|     52780|     50884|     51748|
  18. 桔子  |     43488|     41306|     41289|     37598|     37906|     43913|     48621|     48866|     49803|
  19. 苹果  |     42969|     43845|     38269|     43289|     40539|     40593|     48769|     44909|     56552|
复制代码
以上查询返回了按照渠道和月份分组的汇总结果,并且将它们转换为列进行表现。
与 PIVOT 相反的操作是 UNPIVOT,它可以将列转换为行。我们通过以下示例将行专列之后的数据再转换返来:
  1. with d(saledate, product, channel, amount) as (
  2. select to_char(saledate, 'YYYYMM'),
  3.      product,
  4.      channel,
  5.      amount
  6. from sales_data
  7. ),
  8. pt as (
  9. select *
  10. from d
  11. pivot (
  12.   sum(amount)
  13.   for saledate
  14.   in ('201901' "201901", '201902' "201902", '201903' "201903", '201904' "201904", '201905' "201905", '201906' "201906")
  15. )
  16. )
  17. select * from pt
  18. unpivot (
  19. amount
  20. for saledate
  21. IN ("201901", "201902", "201903", "201904", "201905", "201906")
  22. );
复制代码
其中,unpivot 子句也有三个选项,将每个月份代表的列转换为 saledate 字段中的行,并且将对应的数据转换为 amount 字段中的行。以上查询返回的结果如下:
  1. PRODUCT |CHANNEL |SALEDATE|AMOUNT|
  2. --------|--------|--------|------|
  3. 桔子     |京东     |201901  | 41289|
  4. 桔子     |京东     |201902  | 43913|
  5. 桔子     |京东     |201903  | 49803|
  6. 桔子     |京东     |201904  | 49256|
  7. 桔子     |京东     |201905  | 64889|
  8. 桔子     |京东     |201906  | 62649|
  9. 香蕉     |店面     |201901  | 41210|
  10. 香蕉     |店面     |201902  | 39420|
  11. 香蕉     |店面     |201903  | 50884|
  12. 香蕉     |店面     |201904  | 52085|
  13. 香蕉     |店面     |201905  | 60249|
  14. 香蕉     |店面     |201906  | 67597|
  15. ...
复制代码
假如想要解锁更多的 PIVOT 和 UNPIVOT 的使用姿势,可以参考官方文档中的界说和示例。
使用 MODEL 子句实现数据透视表


除了 PIVOT 子句之外,Oracle 还提供一个更加强盛的功能:MODEL 子句。简单来说,MODEL 子句可以实现 EXCEL 等电子表格中基于位置和符号的单位格引用以及复杂的公式盘算。
完整的 MODEL 子句比力复杂,我们直接看一个示例:
  1. with d(saledate, product, channel, amount) as (
  2. select to_char(saledate, 'YYYYMM'), product, channel, sum(amount)
  3. from sales_data
  4. group by rollup (to_char(saledate, 'YYYYMM'), product, channel)
  5. )
  6. select coalesce(product, '【全部产品】') "产品",
  7.     coalesce(channel, '【所有渠道】') "渠道",
  8.     s201901 "一月", s201902 "二月", s201903 "三月", s201904 "四月", s201905 "五月", s201906 "六月",
  9.     stotal "总计"
  10. from d
  11. model
  12. return updated rows
  13. partition by (product, channel)
  14. dimension by (saledate)
  15. measures (amount, 0 s201901, 0 s201902, 0 s201903, 0 s201904, 0 s201905, 0 s201906, 0 stotal)
  16. unique dimension
  17. rules upsert all
  18. (s201901[0] = amount['201901'],
  19.   s201902[0] = amount['201902'],
  20.   s201903[0] = amount['201903'],
  21.   s201904[0] = amount['201904'],
  22.   s201905[0] = amount['201905'],
  23.   s201906[0] = amount['201906'],
  24.   stotal[0] = sum(amount)[saledate between '201901' and '201906'])
  25. 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] 是全部月份的总和。
以上语句返回的结果如下:
  1. 产品       |渠道       |一月  |二月   |三月   |四月   |五月  |六月   |总计   |
  2. ----------|----------|------|------|------|------|------|------|-------|
  3. 桔子       |京东      | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
  4. 桔子       |店面      | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
  5. 桔子       |淘宝      | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
  6. 桔子       |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
  7. 苹果       |京东      | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
  8. 苹果       |店面      | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
  9. 苹果       |淘宝      | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
  10. 苹果       |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
  11. 香蕉       |京东      | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
  12. 香蕉       |店面      | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
  13. 香蕉       |淘宝      | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
  14. 香蕉       |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
  15. 【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
复制代码
MODEL 子句允许通太过区(PARTITION BY)和维度(DIMENSION BY)创建一个多维数组,并且通过指定规则(RULES)来操作和更新数组中单位格中的度量值(MEASURES)。其中,规则支持通配符和循环迭代,度量可以使用聚合函数和窗口函数。
MODEL 子句完整的使用姿势请参考官方文档。
到此这篇关于详解Oracle 中实现数据透视表的几种方法的文章就先容到这了,更多相干Oracle 数据透视表内容请搜索草根技能分享从前的文章或继承欣赏下面的相干文章盼望大家以后多多支持草根技能分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作