MySQL/MariaDB 如何实现数据透视表的示例代码

前文介绍了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 ='京东';

到此这篇关于MySQL/MariaDB 如何实现数据透视表的示例代码的文章就介绍到这了,更多相关MySQL/MariaDB数据透视表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • 简单谈谈MySQL数据透视表

    我有一张这样的产品零件表: 部分 part_id part_type product_id -------------------------------------- 1 A 1 2 B 1 3 A 2 4 B 2 5 A 3 6 B 3 我想要一个返回如下表格的查询: product_id part_A_id part_B_id ---------------------------------------- 1 1 2 2 3 4 3 5 6 在实际实施中,将有数百万个产品部件 最佳答案

  • MySQL/MariaDB 如何实现数据透视表的示例代码

    前文介绍了Oracle 中实现数据透视表的几种方法,今天我们来看看在 MySQL/MariaDB 中如何实现相同的功能. 本文使用的示例数据可以点此下载. 使用 CASE 表达式和分组聚合 数据透视表的本质就是按照行和列的不同组合进行数据分组,然后对结果进行汇总:因此,它和数据库中的分组(GROUP BY)加聚合函数(COUNT.SUM.AVG 等)的功能非常类似. 我们首先使用以下 GROUP BY 子句对销售数据进行分类汇总: select coalesce(product, '[全部产品]

  • C#如何操作Excel数据透视表

    一.概述 数据透视表(Pivot Table)是一种交互式的表,可以进行某些计算,如求和与计数等,可动态地改变透视表版面布置,也可以重新安排行号.列标和页字段.当改变版面布置时,数据透视表也会按照新的布置来进行更新,可以说是一个功能强大的数据分析工具.因此,本篇文章将介绍在C# 中关于Excel数据透视表的操作示例,示例内容主要包含以下要点: 1. 创建透视表 (1)创建数据缓存 (2)创建数据透视表 (3)添加行字段和列字段 (4)添加值字段 (5)设置样式 2.  设置行折叠.展开 3. 

  • MySQL Union合并查询数据及表别名、字段别名用法分析

    本文实例讲述了MySQL Union合并查询数据及表别名.字段别名用法.分享给大家供大家参考,具体如下: union关键字 SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103); 为表取别名 SELECT * from orders AS o WHERE o.o_num = 3000

  • pandas实现excel中的数据透视表和Vlookup函数功能代码

    在孩子王实习中做的一个小工作,方便整理数据. 目前这几行代码是实现了一个数据透视表和匹配的功能,但是将做好的结果写入了不同的excel中, 如何实现将结果连续保存到同一个Excel的同一个工作表中? 还需要探索. import pandas as pd import numpy as np a = [1601,1602,1603,1604,1605,1606,1607,1608,1609,1610,1611,1612,1701,1702,1703,1704] for i in a: b = st

  • python 用pandas实现数据透视表功能

    透视表是一种可以对数据动态排布并且分类汇总的表格格式.对于熟练使用 excel 的伙伴来说,一定很是亲切! pd.pivot_table() 语法: pivot_table(data, # DataFrame values=None, # 值 index=None, # 分类汇总依据 columns=None, # 列 aggfunc='mean', # 聚合函数 fill_value=None, # 对缺失值的填充 margins=False, # 是否启用总计行/列 dropna=True,

  • Python实现数据透视表详解

    目录 1.groupby + agg 2. crosstab 3.groupby + pivot pivot_table 总结 用Python里的Pandas可以实现,虽然感觉Excel更方便 1.groupby + agg 不够直观,不好看 对贷款年份,贷款种类创建数据透视 train_data.groupby(['year_of_loan', 'class']).agg(d_roat =('isDefault', 'mean')) 2. crosstab pandas.crosstab(in

  • Python+Pandas实现数据透视表

    目录 导入示例数据 参数说明 常用操作 大家好,我是丁小杰. 对于数据透视表,相信对于 Excel 比较熟悉的小伙伴都知道如何使用它,并了解它的强大之处,而在pandas中要实现数据透视就要用到pivot_table了. 导入示例数据 首先导入演示的数据集. import pandas as pd df = pd.read_csv('销售目标.csv') df.head() 参数说明 主要参数: data:待操作的 DataFrame values:被聚合操作的列,可选项 index:行分组键,

  • Pandas数据分析之pandas数据透视表和交叉表

    目录 前言 整理透视 pivot 聚合透视 Pivot Table 聚合透视高级操作 交叉表crosstab() 数据融合melt() 数据堆叠 stack 前言 pandas对数据框也可以像excel一样进行数据透视表整合之类的操作.主要是针对分类数据进行操作,还可以计算数值型数据,去满足复杂的分类数据整理的逻辑. 首先还是导入包: import numpy as np import pandas as pd 整理透视 pivot 首先介绍的是最简单的整理透视函数pivot,其原理如图: pi

  • C++实现将数据写入Excel工作表的示例代码

    目录 安装Spire.XLS for C++ 在 C++ 中将文本或数字值写入单元格 完整代码 效果图 在 C++ 中将数组写入指定的单元格范围 完整代码 效果图 直观的界面.出色的计算功能和图表工具,使Excel成为最流行的个人计算机数据处理软件.在独立的数据包含的信息量太少,而过多的数据又难以理清头绪时,制作成表格是数据管理的最有效手段之一.这样不仅可以方便整理数据,还可以方便我们查找和应用数据.后期我们还可以对具有相似表格框架,相同性质的数据进行合并汇总工作.在本文中,您将学习如何使用 S

  • SpringBoot结合JSR303对前端数据进行校验的示例代码

    一.校验分类 数据的校验一般分为**前端校验.后端校验** 二.前端校验 前端校验是最为明显的,先说一下: ① HTML 非空校验 如 HTML5 新增的属性required="true",一旦没有填写就输入框就显示红色,具体使用如: <input type="text" id="name" name="name" required="true"/> ② JS 同时在提交表单发送 Ajax请求

随机推荐