【Databend】行列转化:数据透视和逆透视

2024-01-13 16:52

本文主要是介绍【Databend】行列转化:数据透视和逆透视,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

文章目录

    • 数据准备
    • 数据透视
    • 数据逆透视
    • 总结

数据准备

学生学科得分等级测试数据如下:

drop table if exists fact_suject_data;
create table if not exists fact_suject_data
(student_id    int          null comment '编号',subject_level varchar null comment '科目等级',subject_level_json variant null comment '科目等级json数据'
);
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (12,'china e,english d,math e','{"china": "e","english": "d","math": "e"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (2,'china b,english b','{"china": "b","english": "b"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (3,'english a,math c','{"english": "a","math": "c"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (4,'china c,math a','{"china": "c","math": "a"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (5,'china d,english a,math c','{"china": "d","english": "a","math": "c"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (6,'china c,english a,math d','{"china": "c","english": "a","math": "d"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (7,'china a,english e,math b','{"china": "a","english": "e","math": "b"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (8,'china d,english e,math e','{"china": "d","english": "e","math": "e"}');
insert into fact_suject_data(student_id, subject_level,subject_level_json) values (9,'china c,english e,math c','{"china": "c","english": "e","math": "c"}');

利用上一篇 【Databend】行列转化:一行变多行和简单分列 文章一行变多行,得到如下效果数据:

select t1.student_id, t1.subject_level, split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject, split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1
from fact_suject_data as t1
order by t1.student_id;

在这里插入图片描述

数据透视

Databend 中的 pivot 功能可以轻松实现数据透视,使用语法如下:

select ...
from ...pivot ( <aggregate_function> ( <pivot_column> )for <value_column> in ( <pivot_value_1> [ , <pivot_value_2> ... ] ) )
[ ... ]

参数解释如下:

  • <aggregate_function>:用于组合来自 <pivot_column> 的分组值的聚合函数。
  • <pivot_column>:将使用指定的 <aggregate_function> 聚合的列。
  • <value_column>:其唯一值将成为数据透视结果集中的新列。
  • <pivot_value_N>:来自<value_column>的唯一值,将成为透视结果集中的新列。
with a as(select t1.student_id, t1.subject_level, split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject, split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1from fact_suject_data as t1order by t1.student_id)
select *
from a pivot (max(level1) for subject in ('china','math','english'));

在这里插入图片描述

数据逆透视

Databend 中 unpivot 功能通过将列转换为行,起到数据逆透视效果。它是一个关系运算符,接受两列(来自表或子查询)以及列列表,并为列表中指定的每列生成一行。使用语法如下:

select ...
from ...unpivot ( <value_column>for <name_column> in ( <column_list> ) )
[ ... ]

参数解释:

  • <value_column>:将存储从<column_list>中列出的列中提取的值的列。
  • <name_column>:将存储提取值的列名称的列。
  • <column_list>:要旋转的列列表,用逗号分隔。

利用数据透视的结果,使用 unpivot 恢复原样实现数据逆透视。

with a as(select t1.student_id, t1.subject_level, split_part(unnest(split(t1.subject_level, ',')), ' ', 1) as subject, split_part(unnest(split(t1.subject_level, ',')), ' ', 2) as level1from fact_suject_data as t1order by t1.student_id),b as(select *from a pivot (max(level1) for subject in ('china','math','english')) )
select *
from b unpivot (level2 for subject in (`china`,`math`,`english`));

在这里插入图片描述

总结

Databend 的 pivot 和 unpivot 功能更好地实现数据的透视和逆透视,并且非常易读和分析大量数据,相较于 Mysql 实现数据透视 (case …when…) 和逆透视 (union all) 来说更简单易读,方法不闲多主要是解决实际问题,学习了解更多方法和工具,在面对问题时也能更好的应对,赶紧实操起来,当遇到也能很自信地说“这题我会”。

参考资料:

  • Mysql 行列变换《你想要的都有》:https://blog.csdn.net/weixin_50357986/article/details/134161183
  • Databend Query Pivot:https://docs.databend.com/sql/sql-commands/query-syntax/query-pivot
  • Databend Query UnPivot:https://docs.databend.com/sql/sql-commands/query-syntax/query-unpivot
  • Databend 行列转化:一行变多行和简单分列:https://blog.csdn.net/weixin_50357986/article/details/135568736

这篇关于【Databend】行列转化:数据透视和逆透视的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/602149

相关文章

Java利用JSONPath操作JSON数据的技术指南

《Java利用JSONPath操作JSON数据的技术指南》JSONPath是一种强大的工具,用于查询和操作JSON数据,类似于SQL的语法,它为处理复杂的JSON数据结构提供了简单且高效... 目录1、简述2、什么是 jsONPath?3、Java 示例3.1 基本查询3.2 过滤查询3.3 递归搜索3.4

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

Python Dash框架在数据可视化仪表板中的应用与实践记录

《PythonDash框架在数据可视化仪表板中的应用与实践记录》Python的PlotlyDash库提供了一种简便且强大的方式来构建和展示互动式数据仪表板,本篇文章将深入探讨如何使用Dash设计一... 目录python Dash框架在数据可视化仪表板中的应用与实践1. 什么是Plotly Dash?1.1

Redis 中的热点键和数据倾斜示例详解

《Redis中的热点键和数据倾斜示例详解》热点键是指在Redis中被频繁访问的特定键,这些键由于其高访问频率,可能导致Redis服务器的性能问题,尤其是在高并发场景下,本文给大家介绍Redis中的热... 目录Redis 中的热点键和数据倾斜热点键(Hot Key)定义特点应对策略示例数据倾斜(Data S

Python实现将MySQL中所有表的数据都导出为CSV文件并压缩

《Python实现将MySQL中所有表的数据都导出为CSV文件并压缩》这篇文章主要为大家详细介绍了如何使用Python将MySQL数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到... python将mysql数据库中所有表的数据都导出为CSV文件到一个目录,并压缩为zip文件到另一个

Java强制转化示例代码详解

《Java强制转化示例代码详解》:本文主要介绍Java编程语言中的类型转换,包括基本类型之间的强制类型转换和引用类型的强制类型转换,文中通过代码介绍的非常详细,需要的朋友可以参考下... 目录引入基本类型强制转换1.数字之间2.数字字符之间引入引用类型的强制转换总结引入在Java编程语言中,类型转换(无论

SpringBoot整合jasypt实现重要数据加密

《SpringBoot整合jasypt实现重要数据加密》Jasypt是一个专注于简化Java加密操作的开源工具,:本文主要介绍详细介绍了如何使用jasypt实现重要数据加密,感兴趣的小伙伴可... 目录jasypt简介 jasypt的优点SpringBoot使用jasypt创建mapper接口配置文件加密

使用Python高效获取网络数据的操作指南

《使用Python高效获取网络数据的操作指南》网络爬虫是一种自动化程序,用于访问和提取网站上的数据,Python是进行网络爬虫开发的理想语言,拥有丰富的库和工具,使得编写和维护爬虫变得简单高效,本文将... 目录网络爬虫的基本概念常用库介绍安装库Requests和BeautifulSoup爬虫开发发送请求解

Oracle存储过程里操作BLOB的字节数据的办法

《Oracle存储过程里操作BLOB的字节数据的办法》该篇文章介绍了如何在Oracle存储过程中操作BLOB的字节数据,作者研究了如何获取BLOB的字节长度、如何使用DBMS_LOB包进行BLOB操作... 目录一、缘由二、办法2.1 基本操作2.2 DBMS_LOB包2.3 字节级操作与RAW数据类型2.