POSTGRESQL动态行转列 plsql行列转换

转载

mob64ca13f87273 2024-04-27 18:57:24

文章标签 POSTGRESQL动态行转列 sql行列转换数据数据字典数据库系统 文章分类 云原生云计算

OLAP是面向数据分析师的高级查询技巧，Excel数据透视表中可以任意切换行列，也就是维度旋转，通过sql怎么实现是这篇文章主要内容。

关键词行转列列转行行列转换 pivot unpivot if case when

一、OLAP和维度度量

数据库应用分为两种，oltp和olap，oltp联机事务处理面向于基本日常事务和处理，比如售票，银行交易等事务处理。Olap联机分析处理面向决策支持，复杂的分析操作。面向决策Olap联机分析处理需要特定的数据库系统，和面向事务处理的数据库系统在模式设计、索引结构，事务管理都要不同。专为决策服务的数据库系统是面向主题、由多数据源集成、拥有当前和历史总结数据，以读为主的数据库系统，也称为数据仓库。

数据仓库的模式中包括事实表和维度表，事实表和维度表的关系有雪花型和星型模式。在分析应用中，维度指用户分析的角度，比如像时间维度、区域维度、标签维度等。度量指维度的取值。一般上维度是离散的，可以分类的，度量是连续的，是可以比较的，有的度量可以转为维度。在很多系统中会自动区分维度和度量，一般上把数值类定义为维度，非数值类定义为度量。维度有层次，比如日期维度可以分成年、月、日、时、分秒等多层，地区可以分为国家、省、市、区县、乡镇等层次，所以有雪花型和星型区分。

多维数据分析更多内容参考：

多维数据分析mp.weixin.qq.com

POSTGRESQL动态行转列 plsql行列转换_POSTGRESQL动态行转列

OLAP一般包含五种操作，上卷（Roll-up）：从细粒度数据向粗粒度的聚合，比如从区县的人口统计到省市的人口，最后到全国的人口统计；钻取（Drill-down）：上卷的相反操作，将汇总数据拆分到更细节的数据；切片（Slice）：选择维中特定的值进行分析，通过选择某个维度的单一值进行分析，比如只看北京的数据；切块（Dice）：选择维中特定区间的数据或者某批特定值进行分析，和切片的不同是得到的结果维度更多；旋转（Pivot）：即维的位置的互换，类似二维表的行列转换，可以从多个维度进行处理。本篇主要介绍其中pivot维度旋转操作。

二、行转列

我们假设有下面一个表，相关脚本请参考文末下载链接。

SELECT * FROM stuscore;

POSTGRESQL动态行转列 plsql行列转换_数据库系统_02

如果想要下面的结果：

POSTGRESQL动态行转列 plsql行列转换_数据字典_03

第一种方案：

SELECT NAME AS '姓名',
 MAX(IF(course = '语文', score, 0)) AS '语文',
 MAX(IF(course = '数学', score, 0)) AS '数学',
 MAX(IF(course = '英语', score, 0)) AS '英语',
 MAX(IF(course = '计算机', score, 0)) AS '计算机'
FROM stuscore GROUP BY NAME;
第二种方案：
Select name as '姓名',
 max(case course when '语文' then score else 0 end) as '语文',
 max(case course when '数学' then score else 0 end) as '数学',
 max(case course when '英语' then score else 0 end) as '英语',
 max(case course when '计算机' then score else 0 end) as '计算机'
from stuscore group by name;
第三种方案可以使用子查询，在后面子查询中进行介绍。
上面查询语句里面用到了以下函数：
If函数：具体语法如下：
IF(expr1,expr2,expr3)，如果expr1的值为真，则返回expr2的值，如果expr1的值为假，
则返回expr3的值。
if(sex=0,'女','男') 如果sex字段值为0，则返回女，否则返回男。
IF(course = '语文', score, 0) 如果course字段值是语文，则返回score的值，否则返回0.
Case when then else end函数：
case 字段 when 值 then 结果 else 其他情况 end；
case when 表达式 then 结果 else 其他情况 end；
case course when '语文' then score else 0 end course字段的值为语文时返回score，否则返回0。
Max函数：指定列取最大值。
同样我们可以获取每个人按照基础学科和专业学科得分：
SELECT NAME AS '姓名',
 SUM(IF(course = '语文' OR course = '数学' OR course = '英语', score, 0)) AS '基础',
 SUM(IF(course = '计算机' , score, 0)) AS '专业'
FROM stuscore GROUP BY NAME;

POSTGRESQL动态行转列 plsql行列转换_数据字典_04

有的数据库比如sqlserver或者oracle中专门有pivot和unpivot关键词进行行列转换。

三、列转行

我们拥有下面这张表：

SELECT * FROM stuscoreline；

POSTGRESQL动态行转列 plsql行列转换_sql行列转换_05

需要获取所有单科成绩大于60的表，包括姓名，科目和分数：需要通过列转行操作：

select name, '语文' as 'course',chinesescore as score from stuScoreline where chinesescore>60

union all

select name, '数学' as 'course', mathscore as score from stuScoreline where mathscore>60

union all

select name, '英语' as 'course', englishscore as score from stuScoreline where englishscore>60

union all

select name, '计算机' as 'course', computerscore as score from stuScoreline where computerscore>60;

POSTGRESQL动态行转列 plsql行列转换_数据库系统_06

四、字典表和数据模型

数据字典，往往都是维度表。数据字典是关于数据库中数据的描述，即元数据，而不是数据本身。数据库中的数据都可以通过数据字典找到相关定义。

使用数据字典的好处：数据字典维护，第一便于维护管理，如果用户需求要增加变更配置项，只需要修改数据字典表记录即可，不需要修改代码。第二便于数据分析。

SELECT * FROM studentdict;

POSTGRESQL动态行转列 plsql行列转换_数据字典_07

POSTGRESQL动态行转列 plsql行列转换_POSTGRESQL动态行转列_08

表studentdict中的sex，nation和addr都是通过字典表来实现，这样进行修改字典表对于事实表本身没有影响，而且进行数据分析非常方便。从数据模型来说是一个典型的星型模型，如果地址字段继续分为国家、省市、区县等级别的话就是一个典型的雪花模型。

下面例子通过关联事实表和字典表实现数据查询：

SELECT stu.no, stu.name, age,sex.name,nation.name,addr.name FROM studentdict stu, sex,nation,addr WHERE stu.sex=sex.no AND stu.nation=nation.no AND stu.addr=addr.no;

POSTGRESQL动态行转列 plsql行列转换_数据字典_09