数据透视表（Pivot Table）是 Excel 中一个非常实用的分析功能，可以用于实现复杂的数据分类汇总和对比分析，是数据分析师和运营人员必备技能之一。今天我们来谈谈如何在 Oracle 数据库中实现数据透视表。使用 CASE 表达式实现数据透视表 select coalesce(product, '【全部产品】') "产品",
coalesce(channel, '【所有渠道】') "渠道",
coalesce(to_char(saledate, 'YYYYMM'), '【所有月份】') "月份",
sum(amount) "销量"
from sales_data
group by rollup (product,channel,to_char(saledate, 'YYYYMM')); 产品 |渠道 |月份 |销量 | 实际上，我们已经得到了数据透视表的汇总结果，只不过需要将数据按照不同月份显示为不同的列；也就是需要将行转换为列，这个功能可以使用 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); 产品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |总计 | Oracle 中的 decode 函数也可以实现类似 CASE 表达式的功能。使用 PIVOT 子句实现数据透视表 SELECT col1, col2, ...
FROM tbl
PIVOT (
pivot_clause,
pivot_for_clause,
pivot_in_clause
); 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; PRODUCT |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'| 接下来我们需要增加一个总计行和总计列，为此可以先将 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 子句也可以一次执行多个聚合操作，或者按照多个字段进行分组。例如：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|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")
); PRODUCT |CHANNEL |SALEDATE|AMOUNT| 如果想要解锁更多的 PIVOT 和 UNPIVOT 的使用姿势，可以参考官方文档中的定义和示例。使用 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; 产品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |总计 | MODEL 子句允许通过分区（PARTITION BY）和维度（DIMENSION BY）创建一个多维数组，并且通过指定规则（RULES）来操作和更新数组中单元格中的度量值（MEASURES）。其中，规则支持通配符和循环迭代，度量可以使用聚合函数和窗口函数。 
数据库
发布于：2021-06-26 10:35
|
阅读数：193
|
评论：0
QQ好友和群
QQ空间
