你真的会玩SQL吗?透视转换的艺术

简介:

你真的会玩SQL吗?系列目录

你真的会玩SQL吗?之逻辑查询处理阶段

你真的会玩SQL吗?和平大使 内连接、外连接

你真的会玩SQL吗?三范式、数据完整性

你真的会玩SQL吗?查询指定节点及其所有父节点的方法

你真的会玩SQL吗?让人晕头转向的三值逻辑

你真的会玩SQL吗?EXISTS和IN之间的区别

你真的会玩SQL吗?无处不在的子查询

你真的会玩SQL吗?Case也疯狂

你真的会玩SQL吗?表表达式,排名函数

你真的会玩SQL吗?简单的 数据修改

你真的会玩SQL吗?你所不知道的 数据聚合

你真的会玩SQL吗?透视转换的艺术

你真的会玩SQL吗?冷落的Top和Apply

你真的会玩SQL吗?实用函数方法汇总

你真的会玩SQL吗?玩爆你的数据报表之存储过程编写(上)

你真的会玩SQL吗?玩爆你的数据报表之存储过程编写(下)

 

透视转换是一种行列互转的技术,在转过程中可能执行聚合操作,应用非常广泛。

本章与 你真的会玩SQL吗?数据聚合 内容比较重要,还涉及到 你真的会玩SQL吗?Case的用法 的内容,都可以一起看。

 

下面的例子将使用OpenSchema表,运行创建表:

复制代码
CREATE TABLE OpenSchema(
objectid INT NOT NULL,
attribute VARCHAR(30) NOT NULL ,
VALUE SQL_VARIANT NOT NULL,
PRIMARY KEY (objectid,attribute)
)
GO 

INSERT INTO OpenSchema(objectid,attribute,VALUE)
VALUES 
(1,N'attr1',CAST(CAST('ABC' AS VARCHAR(10)) AS SQL_VARIANT)),
(1,N'attr2',CAST(CAST(10 AS INT) AS SQL_VARIANT)),
(1,N'attr3',CAST(CAST('20070101' AS SMALLDATETIME) AS SQL_VARIANT)),
(2,N'attr2',CAST(CAST(12 AS INT) AS SQL_VARIANT)),
(2,N'attr3',CAST(CAST('20090101' AS SMALLDATETIME) AS SQL_VARIANT)),
(2,N'attr4',CAST(CAST('Y' AS CHAR(1)) AS SQL_VARIANT)),
(2,N'attr5',CAST(CAST(13.7 AS NUMERIC(9,3)) AS SQL_VARIANT)),
(3,N'attr1',CAST(CAST('xyz' AS VARCHAR(10)) AS SQL_VARIANT)),
(3,N'attr2',CAST(CAST(20 AS INT) AS SQL_VARIANT)),
(3,N'attr3',CAST(CAST('20080101' AS SMALLDATETIME) AS SQL_VARIANT))
复制代码

 将会得到以下输出:

以上VALUE属性保存了多个不同数据类型的值,可以实现要添加新的属性时不用添加列,直接保存。

但是这样查询我们希望把数据旋转为每个属性占一列的传统方式,然后再保存到临时表中处理后续查询称之为透视转换技术。在这里需要回看一下 你真的会玩SQL吗?之逻辑查询处理阶段 对于理解透视转换的步骤是有帮助的。

 

来看一看经典的行转列实例,如要得到下面的结果怎么做:

透视转换的步骤:

    1. 分组:这里需要为每个对象从多个基础行来创建单独的一列数据,这意味着要对行进行分组,这里依据的是objectid列。
    2. 扩展:从结果列考虑每个唯一的属性都需要一个结果列,对应的是attribute列。这里是attr1,attr2……attr5,列中包含5个表达式。
    3. 聚合:从一组NULL值和已知值中提取出已知值,这就需要使用聚合操作,提取已知值技巧就是使用MAX或MIN函数,这两个会忽略NULL,并返回一个非NULL值,国为只包含一个值的集合最大值和最小值就是这个值。此处对就列是VALUE列。每组中若包含多个非NULL值 ,视情况也可用SUM/AVG。

 参考SQL:

  View Code

这里也可以用PIVOT,不过PIVOT不支持动态透视转换,除了使代码更短外没有什么显著差异,这里就不演示了。

 

逆透视转换

即列旋转行,常用于规范化数据,如将上面的结果逆转换。

创建表:

复制代码
CREATE TABLE PvtOpenSchema(
objectid INT NOT NULL,
attr1 VARCHAR(10)  NULL ,
attr2 VARCHAR(10)  NULL ,
attr3 VARCHAR(10)  NULL ,
attr4 VARCHAR(10)  NULL ,
attr5 VARCHAR(10)  NULL 
)
复制代码

将上面的结果插入此表:

复制代码
INSERT INTO PvtOpenSchema
(
objectid,attr1,attr2,attr3,attr4,attr5
)
SELECT  objectid ,
        MAX(CASE WHEN attribute = 'attr1' THEN CAST( VALUE AS VARCHAR(10)) 
            END) AS attr1 ,
        MAX(CASE WHEN attribute = 'attr2' THEN  CAST( VALUE AS VARCHAR(10)) 
            END) AS attr2 ,
        MAX(CASE WHEN attribute = 'attr3' THEN  CAST( VALUE AS VARCHAR(10)) 
            END) AS attr3 ,
        MAX(CASE WHEN attribute = 'attr4' THEN  CAST( VALUE AS VARCHAR(10)) 
            END) AS attr4 ,
        MAX(CASE WHEN attribute = 'attr5' THEN  CAST( VALUE AS VARCHAR(10)) 
            END) AS attr5
FROM    OpenSchema
GROUP BY objectid
复制代码

结果:

若做到逆转换,将每个objectid 和每个attribute生成结果集中的一行

第一步是为每个甚而行生成5个属性副本,可以通过基础表和每个属性占一行虚拟辅助表执行交叉联接来实现,然后用select 返回objectid和attribute,用case计算值。

可能数据源中会得到与NULL值,如1的attr4,所以还需要对结果进行过滤掉Value为NULL的。

代码如下:

复制代码
SELECT  objectid ,
        attribute ,
        VALUE
FROM    ( SELECT    objectid ,
                    attribute ,
                    CASE attribute
                      WHEN 'attr1' THEN attr1
                      WHEN 'attr2' THEN attr2
                      WHEN 'attr3' THEN attr3
                      WHEN 'attr4' THEN attr4
                      WHEN 'attr5' THEN attr5
                    END AS VALUE
          FROM      PvtOpenSchema
                    CROSS JOIN ( SELECT 'attr1' AS attribute
                                 UNION ALL
                                 SELECT 'attr2'
                                 UNION ALL
                                 SELECT 'attr3'
                                 UNION ALL
                                 SELECT 'attr4'
                                 UNION ALL
                                 SELECT 'attr5'
                               ) AS attributes
        ) AS T
WHERE   VALUE IS NOT NULL  
复制代码

这里可以使用UNPIVOT表运算符,查询将更简单:

SELECT  objectid ,
        attribute ,
        VALUE
FROM    PvtOpenSchema UNPIVOT ( VALUE FOR attribute IN ( attr1, attr2, attr3, attr4, attr5 ) ) AS a

 UNPIVOT会在一个逻辑处理中删除NULL行。

以上只是一个简单的示例,即使现在理解了但在多变的实际应用可能就会迷惘,那时再来对比看看此例。

练习:

复制代码
           姓名    科目   成绩
           张三     语文    80
           张三     数学    90
           张三     物理    85
           李四     语文    85
           李四     物理    82
           李四     英语    90
           李四     政治    70
           王五     英语    90
复制代码

将上表转换为:

           姓名     数学    物理     英语    语文    政治 
           李四     0       82      90     85     70
           王五     0       0       90      0      0
           张三    90      85        0      80     0

 本文转自欢醉博客园博客,原文链接http://www.cnblogs.com/zhangs1986/p/4942546.html如需转载请自行联系原作者


欢醉

相关文章
小数转成百分数,日期字符串互相转换,这几个SQL问题该如何解决?(上)
SQL在使用过程中,经常会遇到一些奇奇怪怪的小问题,今天给大家总结一下常见的几个问题该如何处理。
小数转成百分数,日期字符串互相转换,这几个SQL问题该如何解决?(上)
|
SQL 关系型数据库 PostgreSQL
PostgreSQL datediff 日期间隔(单位转换)兼容SQL用法
标签 PostgreSQL , datediff 背景 使用datediff,对时间或日期相减,得到的间隔,转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周...
14537 0
|
SQL 存储 Oracle
通过sql做数据透视表,数据库表行列转换(pivot和Unpivot用法)(一)
在mssql中大家都知道可以使用pivot来统计数据,实现像excel的透视表功能 一、MSsqlserver中我们通常的用法
339 0
|
SQL 数据可视化 数据挖掘
Pandas+ SLS SQL:融合灵活性和高性能的数据透视
Pandas是一个十分强大的python数据分析工具,也是各种数据建模的标准工具。Pandas擅长处理数字型数据和时间序列数据。Pandas的第一大优势在于,封装了一些复杂的代码实现过程,只需要调用接口就行了,避免了编写大量的代码。Pandas的第二大优势在于灵活性,可以实现自动化批量化处理复杂的逻辑,这些工作是Excel等工具是无法完成的。因而Pandas介于Excel和自主编写程序之间,兼具灵活性和简洁性的数据分析工具。
461 0
|
SQL
SQL SERVER-时间戳(timestamp)与时间格式(datetime)互相转换
原文:SQL SERVER-时间戳(timestamp)与时间格式(datetime)互相转换 SQL里面有个DATEADD的函数。时间戳就是一个从1970-01-01 08:00:00到时间的相隔的秒数。
13932 0
|
SQL 分布式计算 数据挖掘
SQL、Pandas和Spark:如何实现数据透视表?
数据透视表是一个很重要的数据统计操作,最有代表性的当属在Excel中实现(甚至说提及Excel,个人认为其最有用的当属三类:好用的数学函数、便捷的图表制作以及强大的数据透视表功能)。所以,今天本文就围绕数据透视表,介绍一下其在SQL、Pandas和Spark中的基本操作与使用,这也是沿承这一系列的文章之一。
264 0
SQL、Pandas和Spark:如何实现数据透视表?
小数转成百分数,日期字符串互相转换,这几个SQL问题该如何解决?(下)
SQL在使用过程中,经常会遇到一些奇奇怪怪的小问题,今天给大家总结一下常见的几个问题该如何处理。
|
SQL Java 数据库
可将数据库表字段转换为Java代码驼峰字段SQL的实现
经常要将数据库表字段映射到实体对象,除了逆向工程,还可以通过SQL的方式将表字段列转换为Java代码驼峰字段。
1205 0
|
SQL Java 数据库连接
util包下的Date与sql包下的时间类型之间的转换|学习笔记
快速学习util包下的Date与sql包下的时间类型之间的转换
|
SQL JSON 数据处理
DLA SQL技巧:行、列转换和JSON数据列展开
1. 简介 在数据库SQL处理中,常常有行转列(Pivot)和列转行(Unpivot)的数据处理需求。本文以示例说明在Data Lake Analytics(https://www.aliyun.com/product/datalakeanalytics)中,如何使用SQL的一些技巧,达到行转列(Pivot)和列转行(Unpivot)的目的。
4630 0