【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

相关文章

Spring 请求之传递 JSON 数据的操作方法

《Spring请求之传递JSON数据的操作方法》JSON就是一种数据格式,有自己的格式和语法,使用文本表示一个对象或数组的信息,因此JSON本质是字符串,主要负责在不同的语言中数据传递和交换,这... 目录jsON 概念JSON 语法JSON 的语法JSON 的两种结构JSON 字符串和 Java 对象互转

C++如何通过Qt反射机制实现数据类序列化

《C++如何通过Qt反射机制实现数据类序列化》在C++工程中经常需要使用数据类,并对数据类进行存储、打印、调试等操作,所以本文就来聊聊C++如何通过Qt反射机制实现数据类序列化吧... 目录设计预期设计思路代码实现使用方法在 C++ 工程中经常需要使用数据类,并对数据类进行存储、打印、调试等操作。由于数据类

SpringBoot使用GZIP压缩反回数据问题

《SpringBoot使用GZIP压缩反回数据问题》:本文主要介绍SpringBoot使用GZIP压缩反回数据问题,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录SpringBoot使用GZIP压缩反回数据1、初识gzip2、gzip是什么,可以干什么?3、Spr

SpringBoot集成Milvus实现数据增删改查功能

《SpringBoot集成Milvus实现数据增删改查功能》milvus支持的语言比较多,支持python,Java,Go,node等开发语言,本文主要介绍如何使用Java语言,采用springboo... 目录1、Milvus基本概念2、添加maven依赖3、配置yml文件4、创建MilvusClient

SpringValidation数据校验之约束注解与分组校验方式

《SpringValidation数据校验之约束注解与分组校验方式》本文将深入探讨SpringValidation的核心功能,帮助开发者掌握约束注解的使用技巧和分组校验的高级应用,从而构建更加健壮和可... 目录引言一、Spring Validation基础架构1.1 jsR-380标准与Spring整合1

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

SpringBatch数据写入实现

《SpringBatch数据写入实现》SpringBatch通过ItemWriter接口及其丰富的实现,提供了强大的数据写入能力,本文主要介绍了SpringBatch数据写入实现,具有一定的参考价值,... 目录python引言一、ItemWriter核心概念二、数据库写入实现三、文件写入实现四、多目标写入

使用Python将JSON,XML和YAML数据写入Excel文件

《使用Python将JSON,XML和YAML数据写入Excel文件》JSON、XML和YAML作为主流结构化数据格式,因其层次化表达能力和跨平台兼容性,已成为系统间数据交换的通用载体,本文将介绍如何... 目录如何使用python写入数据到Excel工作表用Python导入jsON数据到Excel工作表用

Mysql如何将数据按照年月分组的统计

《Mysql如何将数据按照年月分组的统计》:本文主要介绍Mysql如何将数据按照年月分组的统计方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql将数据按照年月分组的统计要的效果方案总结Mysql将数据按照年月分组的统计要的效果方案① 使用 DA

鸿蒙中Axios数据请求的封装和配置方法

《鸿蒙中Axios数据请求的封装和配置方法》:本文主要介绍鸿蒙中Axios数据请求的封装和配置方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录1.配置权限 应用级权限和系统级权限2.配置网络请求的代码3.下载在Entry中 下载AxIOS4.封装Htt