- ubuntu12.04环境下使用kvm ioctl接口实现最简单的虚拟机
- Ubuntu 通过无线网络安装Ubuntu Server启动系统后连接无线网络的方法
- 在Ubuntu上搭建网桥的方法
- ubuntu 虚拟机上网方式及相关配置详解
CFSDN坚持开源创造价值,我们致力于搭建一个资源共享平台,让每一个IT人在这里找到属于你的精彩世界.
这篇CFSDN的博客文章详解Oracle 中实现数据透视表的几种方法由作者收集整理,如果你对这篇文章有兴趣,记得点赞哟.
数据透视表(pivot table)是 excel 中一个非常实用的分析功能,可以用于实现复杂的数据分类汇总和对比分析,是数据分析师和运营人员必备技能之一。今天我们来谈谈如何在 oracle 数据库中实现数据透视表.
本文使用的示例数据可以点此下载.
。
数据透视表的本质就是按照行和列的不同组合进行数据分组,然后对结果进行汇总;因此,它和数据库中的分组(group by)和聚合函数(count、sum、avg 等)的功能非常类似.
我们首先使用以下 group by 子句对销售数据进行分类汇总:
1
2
3
4
5
6
|
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 表达式实现:
1
2
3
4
5
6
7
8
9
10
|
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.
。
oracle 中的 pivot 子句用于将行转换为列,基本语法如下:
1
2
3
4
5
6
7
|
select
col1, col2, ...
from
tbl
pivot (
pivot_clause,
pivot_for_clause,
pivot_in_clause
);
|
pivot 子句包含 3 个部分:
对于上文中的示例,我们可以使用以下 pivot 子句:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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 子句进行转换:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 子句也可以一次执行多个聚合操作,或者按照多个字段进行分组。例如:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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,它可以将列转换为行。我们通过以下示例将行专列之后的数据再转换回来:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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 的使用姿势,可以参考官方文档中的定义和示例.
。
除了 pivot 子句之外,oracle 还提供一个更加强大的功能:model 子句。简单来说,model 子句可以实现 excel 等电子表格中基于位置和符号的单元格引用以及复杂的公式计算.
完整的 model 子句比较复杂,我们直接看一个示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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 数据透视表内容请搜索我以前的文章或继续浏览下面的相关文章希望大家以后多多支持我! 。
原文链接:https://tonydong.blog.csdn.net/article/details/107555256 。
最后此篇关于详解Oracle 中实现数据透视表的几种方法的文章就讲到这里了,如果你想了解更多关于详解Oracle 中实现数据透视表的几种方法的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
我有一台 MySQL 服务器和一台 PostgreSQL 服务器。 需要从多个表中复制或重新插入一组数据 MySQL 流式传输/同步到 PostgreSQL 表。 这种复制可以基于时间(Sync)或事
如果两个表的 id 彼此相等,我尝试从一个表中获取数据。这是我使用的代码: SELECT id_to , email_to , name_to , status_to
我有一个 Excel 工作表。顶行对应于列名称,而连续的行每行代表一个条目。 如何将此 Excel 工作表转换为 SQL 表? 我使用的是 SQL Server 2005。 最佳答案 这取决于您使用哪
我想合并两个 Django 模型并创建一个模型。让我们假设我有第一个表表 A,其中包含一些列和数据。 Table A -------------- col1 col2 col3 col
我有两个表:table1,table2,如下所示 table1: id name 1 tamil 2 english 3 maths 4 science table2: p
关闭。此题需要details or clarity 。目前不接受答案。 想要改进这个问题吗?通过 editing this post 添加详细信息并澄清问题. 已关闭 1 年前。 Improve th
下面两个语句有什么区别? newTable = orginalTable 或 newTable.data(originalTable) 我怀疑 .data() 方法具有性能优势,因为它在标准 AX 中
我有一个表,我没有在其中显式定义主键,它并不是真正需要的功能......但是一位同事建议我添加一个列作为唯一主键以随着数据库的增长提高性能...... 谁能解释一下这是如何提高性能的? 没有使用索引(
如何将表“产品”中的产品记录与其不同表“图像”中的图像相关联? 我正在对产品 ID 使用自动增量。 我觉得不可能进行关联,因为产品 ID 是自动递增的,因此在插入期间不可用! 如何插入新产品,获取产品
我有一个 sql 表,其中包含关键字和出现次数,如下所示(尽管出现次数并不重要): ____________ dog | 3 | ____________ rat | 7 | ____
是否可以使用目标表中的LAST_INSERT_ID更新源表? INSERT INTO `target` SELECT `a`, `b` FROM `source` 目标表有一个自动增量键id,我想将其
我正在重建一个搜索查询,因为它在“我看到的”中变得多余,我想知道什么 (albums_artists, artists) ( ) does in join? is it for boosting pe
以下是我使用 mysqldump 备份数据库的开关: /usr/bin/mysqldump -u **** --password=**** --single-transaction --databas
我试图获取 MySQL 表中的所有行并将它们放入 HTML 表中: Exam ID Status Assigned Examiner
如何查询名为 photos 的表中的所有记录,并知道当前用户使用单个查询将哪些结果照片添加为书签? 这是我的表格: -- -- Table structure for table `photos` -
我的网站都在 InnoDB 表上运行,目前为止运行良好。现在我想知道在我的网站上实时发生了什么,所以我将每个页面浏览量(页面、引荐来源网址、IP、主机名等)存储在 InnoDB 表中。每秒大约有 10
我在想我会为 mysql 准备两个表。一个用于存储登录信息,另一个用于存储送货地址。这是传统方式还是所有内容都存储在一张表中? 对于两个表...有没有办法自动将表 A 的列复制到表 B,以便我可以引用
我不是程序员,我从这个表格中阅读了很多关于如何解决我的问题的内容,但我的搜索效果不好 我有两张 table 表 1:成员 id*| name | surname -------------------
我知道如何在 ASP.NET 中显示真实表,例如 public ActionResult Index() { var s = db.StaffInfoDBSet.ToList(); r
我正在尝试运行以下查询: "insert into visits set source = 'http://google.com' and country = 'en' and ref = '1234
我是一名优秀的程序员,十分优秀!