MySQL/MariaDB 如何实现数据透视表的示例代码
作者:不剪发的Tony老师 发布时间:2024-01-13 16:12:18
前文介绍了Oracle 中实现数据透视表的几种方法,今天我们来看看在 MySQL/MariaDB 中如何实现相同的功能。
本文使用的示例数据可以点此下载。
使用 CASE 表达式和分组聚合
数据透视表的本质就是按照行和列的不同组合进行数据分组,然后对结果进行汇总;因此,它和数据库中的分组(GROUP BY)加聚合函数(COUNT、SUM、AVG 等)的功能非常类似。
我们首先使用以下 GROUP BY 子句对销售数据进行分类汇总:
select coalesce(product, '【全部产品】') "产品",
coalesce(channel, '【所有渠道】') "渠道",
any_value(coalesce(extract(year_month from saledate), '【所有月份】')) "月份",
sum(amount) "销量"
from sales_data
group by product,channel,extract(year_month from saledate) with rollup;
以上语句按照产品、渠道以及月份进行汇总;with rollup 选项用于生成不同层次的小计、合计以及总计;coalesce 函数用于将汇总行中的 NULL 值显示为相应的信息;any_value 函数用于返回分组内的任意数据,如果去掉会返回语法错误(MySQL 的一个 bug)。该查询返回的结果如下:
产品 |渠道 |月份 |销量 |
---------|---------|-----------|-------|
桔子 |京东 |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 表达式实现:
select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",
sum(case extract(year_month from saledate) when 201901 then amount else 0 end) "一月",
sum(case extract(year_month from saledate) when 201902 then amount else 0 end) "二月",
sum(case extract(year_month from saledate) when 201903 then amount else 0 end) "三月",
sum(case extract(year_month from saledate) when 201904 then amount else 0 end) "四月",
sum(case extract(year_month from saledate) when 201905 then amount else 0 end) "五月",
sum(case extract(year_month from saledate) when 201906 then amount else 0 end) "六月",
sum(amount) "总计"
from sales_data
group by product, channel with rollup;
第一个 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|
MySQL 中的 IF(expr1,expr2,expr3) 函数也可以用于替换上面 CASE 表达式。
有行转列就有列转行,MySQL 也没有专门的函数处理这种情况,可以使用 UNION 操作符将多个结果集进行合并。例如:
with d as (
select product, channel,
sum(case extract(year_month from saledate) when 201901 then amount else 0 end) s01,
sum(case extract(year_month from saledate) when 201902 then amount else 0 end) s02,
sum(case extract(year_month from saledate) when 201903 then amount else 0 end) s03,
sum(case extract(year_month from saledate) when 201904 then amount else 0 end) s04,
sum(case extract(year_month from saledate) when 201905 then amount else 0 end) s05,
sum(case extract(year_month from saledate) when 201906 then amount else 0 end) s06
from sales_data
group by product, channel
)
select product, channel, 201901 saledate, s01 amount from d
union all
select product, channel, 201902 saledate, s02 from d
union all
select product, channel, 201903 saledate, s03 from d
union all
select product, channel, 201904 saledate, s04 from d
union all
select product, channel, 201905 saledate, s05 from d
union all
select product, channel, 201906 saledate, s06 from d;
通用表表达(with 子句)构造了包含多个月份的销量数据,每个月份都是一列;然后每个查询返回一个月份的数据,并且通过 union all 操作符将所有结果合并到一起。
使用预编译的动态 SQL 语句
使用 CASE 表达式和聚合函数实现数据透视表的方法存在一定的局限性,假如还有 7 月份到 12 月份的销量需要统计,我们就需要修改查询语句增加这部分的处理。为此,我们可以使用动态 SQL 自动生成行列转换的语句:
select group_concat(
distinct concat(
' sum(case extract(year_month from saledate) when ', dt,
' then amount else 0 end) as "', dt, '"')
) into @sql
from (
select extract(year_month from saledate) as dt
from sales_data
order by saledate
) d;
set @sql
= concat('select coalesce(product, ''【全部产品】'') "产品", coalesce(channel, ''【所有渠道】'') "渠道",', @sql,
', sum(amount) "总计"
from sales_data
group by product, channel with rollup;');
select @sql;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
首先,通过查询 sales_data 表找出所有的月份并且构造 sum 函数,将构造的语句存入变量 @sql 中;group_concat 函数可以将多行字符串合并成单个字符串。
group_concat 函数允许返回的最大长度(字节)由系统变量 group_concat_max_len 进行设置,默认值为 1024。
然后,使用 set 命令将查询语句的其他部分和已有的内容进行合并,生成的查询语句如下:
select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",
sum(case extract(year_month from saledate) when 201901 then amount else 0 end) as "201901",
sum(case extract(year_month from saledate) when 201902 then amount else 0 end) as "201902",
sum(case extract(year_month from saledate) when 201903 then amount else 0 end) as "201903",
sum(case extract(year_month from saledate) when 201904 then amount else 0 end) as "201904",
sum(case extract(year_month from saledate) when 201905 then amount else 0 end) as "201905",
sum(case extract(year_month from saledate) when 201906 then amount else 0 end) as "201906",
sum(amount) "总计"
from sales_data
group by product, channel with rollup;
最后通过预编译命令执行该语句并返回结果,即使增加了其他月份的销售数据也不需要手动修改查询语句。
使用 CONNECT 存储引擎
如果使用 MariaDB 10.0 以上的版本,可以利用 CONNECT 存储引擎中的 PIVOT 表类型实现数据透视表。
首先,我们需要安装 CONNECT 存储引擎。Windows 系统可以执行以下命令进行动态安装:
INSTALL SONAME 'ha_connect';
也可以在配置文件 my.ini 中增加以下内容,不过需要重启服务:
[mysqld]
plugin_load_add = ha_connect
对于 Linux 系统,安装过程可以参考官方文档。
接下来我们定义一个 pivot 类型的表:
create table pivot_sales(
product varchar(20) not null,
channel varchar(20) not null,
`201901` decimal(10,2) not null flag=1,
`201902` decimal(10,2) not null flag=1,
`201903` decimal(10,2) not null flag=1,
`201904` decimal(10,2) not null flag=1,
`201905` decimal(10,2) not null flag=1,
`201906` decimal(10,2) not null flag=1
)
engine=connect table_type=pivot
option_list='PivotCol=saledate,FncCol=amount,host=127.0.0.1,user=root, password=p123456,port=3306'
SrcDef='select product,channel,date_format(saledate, ''%Y%m'') saledate,sum(amount) amount from sales_data group by product,channel,date_format(saledate, ''%Y%m'')';
其中,engine 定义存储引擎为 connect;table_type 定义表的类型为 pivot;option_list 用于定义各种选项,PivotCol 表示要转换成多个字段的数据所在的列,FncCol 指定要进行汇总的字段,其他是连接源表服务器的信息;SrcDef 用于指定源表查询语句,也可以使用 Tabname 指定表名;上面的字段是透视表的结构,flag=1 表示聚合之后的字段。
创建成功之后,我们就可以直接查询 pivot_sales 表中的数据了:
select * from pivot_sales;
product |channel |201901 |201902 |201903 |201904 |201905 |201906 |
--------|---------|--------|--------|--------|--------|--------|--------|
桔子 |京东 |41289.00|43913.00|49803.00|49256.00|64889.00|62649.00|
桔子 |店面 |41306.00|37906.00|48866.00|48673.00|58998.00|58931.00|
桔子 |淘宝 |43488.00|37598.00|48621.00|49919.00|58530.00|64626.00|
苹果 |京东 |38269.00|40593.00|56552.00|56662.00|64493.00|62045.00|
苹果 |店面 |43845.00|40539.00|44909.00|55646.00|56771.00|64933.00|
苹果 |淘宝 |42969.00|43289.00|48769.00|58052.00|58872.00|59844.00|
香蕉 |京东 |36879.00|36981.00|51748.00|54801.00|64936.00|60688.00|
香蕉 |店面 |41210.00|39420.00|50884.00|52085.00|60249.00|67597.00|
香蕉 |淘宝 |42468.00|41955.00|52780.00|54971.00|56504.00|59213.00|
目前,PIVOT 表支持的功能有限,只能进行一些基本的操作。例如:
-- 不会出错
select * from pivot_sales
where channel ='京东';
-- 语法错误
select channel from pivot_sales
where channel ='京东';
来源:https://blog.csdn.net/horses/article/details/107701625
猜你喜欢
- 删除一条留言信息会级联删除回复信息,这时我们需要用到事务,如下SQLALTER PROCEDURE [dbo].[proc_tb_leave
- 昨天,系统管理员告诉我,我们一个内部应用数据库所在的磁盘空间不足了。我注意到数据库事件日志文件XXX_Data.ldf文件已经增长到了3GB
- 将有安全问题的SQL过程删除,比较全面.一切为了安全!删除了调用shell,注册表,COM组件的破坏权限use master&nb
- 相信大家对阶乘都不陌生。不知道阶乘的可以看这里:一个数的阶乘:比如5的阶乘在python中大部分教程一般推荐用递归函数完成:#!/usr/b
- Liwu_Items表,CreateTime列建立聚集索引 第一种,sqlserver2005特有的分页语法 代码如下:declare @p
- pandas DataFrame是二维的,所以,它既有列索引,又有行索引上一篇里只介绍了列索引:import pandas as pddf
- MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能
- Tuple 叫做 tuple,用小括号、或者无括号来表述,是一连串有顺序的数字。a_tuple = (12, 3, 5, 15 , 6)an
- 这段时间一直比较忙,一忙起来真感觉自己就只是一台挣钱的机器了(说的好像能挣到多少钱似的,呵呵);这会儿难得有点儿空闲时间,想把前段时间开发微
- JS调试技巧技巧一:格式化压缩代码 技巧二:快速跳转到某个断点的位置右侧的Breakpoints会汇总你在JS文件所有打过的断点,点击跟ch
- 一般在本机上完成基于Flask框架的代码编写后,如果有接口或者数据操作方面需求需要把代码部署到指定服务器上。一般情况下,使用Flask框架开
- 今天是五一劳动节,可是我们劳动人民的节日哦。很多大网站都设计了特殊的logo来表示向每一位普通的劳动者致敬!下面就让我们看看这些logo吧!
- 搜了全网都是各种坑,没能解决我的问题。最后自己琢磨出来了。安装好以后,会弹出临时密码 ,copy住。如果手点快了,在通知栏还有一次机会,通知
- 举个例子来说,要查找出2007-10-12至2007-10-31之间在网站上注册的会员,选择好日期后,点击“查询”按钮,发现2007-10-
- 问题重述实习项目要做安全帽目标检测,拿到了公司给的一些视频数据,使用Opencv读取视频并每隔 1 s 1s 1s存储一副图像,下面是一些视
- Pytorch中retain_graph的坑在查看SRGAN源码时有如下损失函数,其中设置了retain_graph=True,其作用就是在
- 一 NULL 为什么这么经常用(1) java的nullnull是一个让人头疼的问题,比如java中的NullPointerExceptio
- 本文实例讲述了Python3.5实现的 * 菜单功能。分享给大家供大家参考,具体如下:程序: * 菜单要求:
- 为了区分选择与未选择区域,,将已选择区域的文本背景色设置为浅蓝色是个很做法。设置的路径在 Editor > Color Scheme
- 1. 定义用一行构建代码例题# 构建一个1-100的列表l1 = [i for i in range(1,101)]print(l1)# 输