实例详解构建数仓中的行列转换
本文分享自华为云社区《GaussDB数据库SQL系列-行列转换》,作者:Gauss松鼠会小助手2。
一、前言
在构建数据仓库或做数据分析时,需要对原始数据的结构进行一定的处理,有时涉及到“行转列”,有时涉及到“列转行”,那么这两个转换的方式具体是什么,有什么差异,怎么实现,今天我们将以GaussDB数据库为例,给大家做一下讲解。
二、简述
1、行转列概念
即将多行一列数据转为一行多列显示。通常转化后将某一列分类后的值作为新的列名,将此值对应的多行数据显示成一行。
2、列转行概念
即将一行多列数据转成多行一列显示。通常将转化后的列名为某一行中某一列的值,来识别原先对应的数据。
三、GaussDB数据库的行列转换实验示例
用一张学生成绩来举例:从老师的角度,在录入成绩时,每科老师都会单独录入每个学生的本科成绩。而从学生的角度,学生只关心自己各科的成绩分别是多少。所以如果把老师录入数据作为原始表,那么学生查看自己的成绩时就要用到行转列,如果让学生上报自己各科的成绩,然后老师去查对应学科的学生考试成绩时,那就是列转行了。
1、行转列示例
1)创建实验表(行存表)
--创建实验表(行存表) CREATE TABLE grade( name VARCHAR(10) ,course VARCHAR(10) ,score INT); --初始化测试数据 INSERT INTO grade VALUES ('张三','数学',80); INSERT INTO grade VALUES ('张三','英语',88); INSERT INTO grade VALUES ('张三','语文',95); INSERT INTO grade VALUES ('李四','数学',88); INSERT INTO grade VALUES ('李四','英语',70); INSERT INTO grade VALUES ('李四','语文',93); --查看结果 SELECT * FROM grade ORDER BY course;
2)静态行转列
--静态行转列 SELECT name ,sum(case when course = '数学' then score else 0 end) AS "数学" ,sum(case when course = '英语' then score else 0 end) AS 英语 ,sum(case when course = '语文' then score else 0 end) AS 语文 FROM grade GROUP BY name;
使用sum、case when的方式:
3)行转列(结果值:拼接式)
使用listagg within group:
--行转列(结果值:拼接式) SELECT name, LISTAGG(score,',') WITHIN GROUP (ORDER BY course) FROM grade GROUP BY name;
4)动态行转列(拼接SQL式)
通过“listagg + 创建FUNCTION + VIEW”的方式实现
--动态行转列(SQL拼接式) SELECT listagg(concat('SUM(CASE WHEN course = ''', course, ''' THEN score ELSE 0 END) AS "', course,'"'),',') WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade); --concat_text的结果: SUM(CASE WHEN course = '数学' THEN score ELSE 0 END) AS "数学",SUM(CASE WHEN course = '英语' THEN score ELSE 0 END) AS "英语",SUM(CASE WHEN course = '语文' THEN score ELSE 0 END) AS "语文" --创建一个函数。 CREATE OR REPLACE FUNCTION fun_test() RETURNS VOID LANGUAGE SQL AS $$ DECLARE s_sql text; rec record; BEGIN s_sql := 'SELECT listagg(CONCAT(''SUM(CASE WHEN course = '''''', course, '''''' THEN score ELSE 0 END) AS "'', course, ''"'' ),'','' ) WITHIN GROUP(ORDER BY 1) AS concat_text FROM (SELECT DISTINCT course FROM grade);'; EXECUTE s_sql INTO rec; s_sql := 'DROP VIEW IF EXISTS v_score; CREATE VIEW v_score AS SELECT name, ' || rec.concat_text || ' FROM grade GROUP BY name;'; EXECUTE s_sql; END $$; --调用 CALL fun_test(); --查看执行结果 select * from v_score;
Tip:请注意SQL拼写时的单引号、双引号。
2、列转行示例
1)创建实验表(复用前面的测试数据)
--创建实验表(复用前面的测试数据) CREATE TABLE grade1 AS SELECT name ,sum(case when course = '数学' then score else 0 end) AS "数学" ,sum(case when course = '英语' then score else 0 end) AS 英语 ,sum(case when course = '语文' then score else 0 end) AS 语文 FROM grade GROUP BY name; --查看结果 SELECT * FROM grade1;
2)使用union all,将各科目(数学、英语、语文)整合为一列
--使用union all,将各科目(数学、英语、语文)整合为一列 SELECT * FROM ( SELECT name, '数学' AS course, 数学 AS score FROM grade1 union all SELECT name, '英语' AS course, 英语 AS score FROM grade1 union all SELECT name, '语文' AS course, 语文 AS score FROM grade1 ) order by name;
四、小结
行列互转在一些数据库使用场景中经常用到,比如数据分析、数仓建设等。但不同的数据库软件有着不同处理方式,但是行列换的基本思路是一致的。本文主要是以GaussDB数据为平台,为大家做了简单的讲述 ,欢迎测试。
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
如果你也会这样调试,那真是泰酷辣
一、引言 工欲善其事,必先利其器,学会如何利用 IDEA 高效 debug ,将会大大提升我们的工作效率。基本的断点调试方式就不讲解了,网上有很多优秀的文章,这里会主要举一些我们日常工作中重要但是容易被忽视的调试技巧。 二、正文 2.1 不暂停的 debug 通常情况下,我们断点的时候,会卡住当前线程。假设我们在主预发或者公用环境进行debug,就老感觉背后有人在骂我,谁又在debug,环境怎么又不通了!所以当我们想要愉快在公用环境debug的时候要: 查看变量值 不暂停直接观察相关变量值 强行改变变量值 是谁调用了我 可以直接观察到调用堆栈,类似 Arthas 的 trace 你进来了不 如果执行了断点所在位置,会在控制台打出一行日志: 2.2 快速执行表达式 我们通常在调试的时候,利用 Evaluate Expression...(option + F8),可以进行我们的表达式执行。 有一定开发经验的小伙伴肯定使用过这个功能,因为在工作中,这个调试技巧使用的频率还蛮高的,我们可以利用快捷键高效调试,例如下图: 快捷键:alt + d 唤起执行窗口 -> option + F9...
- 下一篇
【云栖2023】林伟:大数据AI一体化的解读
本文根据2023云栖大会演讲实录整理而成,演讲信息如下: 演讲人:林伟 | 阿里云研究员,阿里云计算平台事业部首席架构师,阿里云人工智能平台PAI和大数据开发治理平台DataWorks负责人 演讲主题:大数据AI一体化的解读 今年是AI大爆发的一年,大语言模型的诞生推动了席卷整个行业的大模型热潮,许多人认为“AI的iPhone时代”到来了。训练大模型其实不简单,因为模型参数量的增加意味着需要更好的算力、更多的数据去锤炼,并且需要合适的工具让开发者快速迭代模型,只有这样才能更快地提高模型精度。这几年来阿里云一直在宣传AI工程化和规模化,其实是这轮AI爆发的主要推手。 我们看一个典型的模型开发过程,包括数据预训练、模型训练到模型部署。我们往往会非常关注训练,而忽视了整个生产流程。但是要训练出好的模型,数据越来越重要。包括数据采集、数据清理、特征提取、数据管理,再到训练过程中,需要分发哪些数据参与训练、哪些数据用来评测模型质量。所有数据都需要有验证部分,用于验证质量,这一步非常关键。低质量数据对模型的伤害力是超出想象的。这也是为什么吴达恩一直宣传了一个观点,就是更好的机器学习是80%的数据处...
相关文章
文章评论
共有0条评论来说两句吧...