微信号:cntongji

介绍:中国统计网(www.itongji.cn),国内最大的数据分析门户网站.提供数据分析行业资讯,统计百科知识、数据分析、商业智能(BI)、数据挖掘技术,Excel、SPSS、SAS、R等数据分析软件等在线学习平台.

SQL 实现数据透视表功能

2019-05-30 15:30 中国统计网

- 点击上方“中国统计网”订阅我吧!-



数据分组和数据透视表很像,Sql 中的数据分组大家应该都很熟悉了,用的就是 group by。数据透视表是作为一个数据分析师最基本,也是使用频率最高的一个功能了,我们可以把明细数据从数据库导出来,然后在 Excel 里面做透视表,也可以直接在数据库里面做透视表,然后将透视结果进行导出。这篇我们来看看 Sql 中的数据透视表怎么做,这里以 Mysql 数据库为例。


要讲怎么在 Sql 中做透视表,我们还是先看看什么是透视表,其实透视表的核心就是按照行列同时分组,然后对分组后的值进行某种汇总运算。



现在有这么一张表 t,存储的字段如下:

orderid price date areaS001 10 2019/1/1 A区S002 20 2019/1/1 B区S003 30 2019/1/1 C区S004 40 2019/1/2 A区S005 10 2019/1/2 B区S006 20 2019/1/2 C区S007 30 2019/1/3 A区S008 40 2019/1/3 C区


现在我们想看一下每个区域每天的订单量,就是下表这个样子,我们该怎么做呢?


如果要是在 Excel 中做的话就很简单,直接做一个透视表就ok了,把 date 拖到行那个框,将 area 拖到列那个框,将 orderid 拖到值那个框,然后对 orderid 进行计数运算即可。


在 Sql 中,如果我们只是看每一天的订单量的话是不是只需要直接按照成交日期进行 group by 就行,Sql 语句如下:


select ,date ,count(orderid)from tgroup by date


现在我们想看一下每个区域每天的一个成交情况,那是不是在上面代码的基础上再加一个用来判断区域的一个条件,就可以得出每个区域的情况了。


select ,date ,count(case when area = "A区" then orderid end) as "A区" ,count(case when area = "B区" then orderid end) as "B区" ,count(case when area = "C区" then orderid end) as "C区"from tgroup by date


最后结果如下:

     

 A区 B区 C区2019/1/1 1 1 12019/1/2 1 1 02019/1/3 1 1 1


当然了,聚合函数不止可以用 count,还可以用别的聚合函数,比如说,我们想看一下每个区域每一天的营业额,就是所有订单的 price 相加。Sql 代码如下:


select ,date ,sum(case when area = "A区" then price else 0 end) as "A区" ,sum(case when area = "B区" then price else 0 end) as "B区" ,sum(case when area = "C区" then price else 0 end) as "C区"from tgroup by date


最后结果如下:

 A区 B区 C区2019/1/1 10 20 302019/1/2 40 10 202019/1/3 30 0 40


以上就是关于利用 Sql 做数据透视表的一个基本讲解,用 case when 这种方法虽然可以实现数据透视表的功能,但是无论从代码量还是运行速度方面都不是特别理想,如果大家有别的更好的方法,欢迎评论区一起交流。


「零基础数据分析就业班」今晚20:30准时开课

扫描二维码 报名参加


点击阅读全文,入职数据分析!
 
中国统计网 更多文章 Excel数据透视表:4步统计各项所占百分比和排序! 超全干货:Pandas入门教程! 一份有价值的数据报告应该怎么写? 深扒5000+招聘数据 | 听说产品要失宠了? Python链接数据库,SQL语句查询这样操作!
猜您喜欢 冬瓜哥的一项新存储技术专利已正式通过 mysql的实时备份或者 热备份 如何在三年内成长为顶级的程序员 可给老婆发晚归短信的脚本hacker scripts 滚蛋吧,XML 君