实例详解构建数仓中的行列转换
1.数仓实践丨主动预防-DWS关键工具安装确认2.一条SQL如何被MySQL架构中的各个组件操作执行的?3.GaussDB(DWS)网络流控与管控效果4.GaussDB(DWS)字符串处理函数返回错误结果集排查5.从缓存的本质说起,说服技术大佬用Redis6.这年头怕数据泄露?全密态数据库:无所谓,我会出手7.华为云新一代分布式数据库GaussDB,给世界一个更优选择8.GaussDB技术解读丨高级压缩9.掌数科技携手华为云GaussDB,助力金融科技创新,联合打造行业标杆10.一文带你全面了解openGemini11.GaussDB(for Redis)多租户:读写权限控制和数据库隔离的完美融合12.5分钟迁移关系型数据库到图数据库13.数仓现网案例丨超大结果集接收异常14.DWS轻量化更新黑科技:宽表加工优化15.数据库行业需要什么样的人才?高校老师这样说16.数仓性能优化:倾斜优化-表达式计算倾斜的hint优化17.GaussDB技术解读系列之SQL Audit,面向应用开发的SQL审核工具18.带你认识数仓的监控系统TopSQL19.带你走进数仓大集群内幕丨详解关于作业hang及残留问题定位20.实时入库不用愁,HStore帮分忧21.openGauss数据库在CentOS上的安装实践22.揭秘华为云GaussDB(for Redis)丨大key治理23.GaussDB(DWS)函数不同写法引发的结果差异24.数仓中典型的几种不下推语句整改案例25.GaussDB技术解读系列之应用无损透明(ALT)26.华为云GaussDB(for Influx)单机版上线,企业降本增效利器来了27.数仓备份经验分享丨详解roach备份原理及问题处理套路28.中国云数据仓库,双第一!29.华为云GaussDB打造最可信的数据库,给世界一个更优选择30.GaussDB技术解读系列:高级压缩之OLTP表压缩31.十年磨一剑的华为云GES,高明在哪32.使用DWS集群,用户被锁定如何解锁33.GaussDB技术解读系列:高安全之密态等值34.GaussDB技术解读:应用无损透明(ALT)35.数仓资源管控理论已掌握,是时候实战了36.row_number函数的不稳定性37.GaussDB技术解读丨数据库迁移创新实践38.聊聊GaussDB AP是如何执行SQL的39.Navicat 携手华为云GaussDB,联合打造便捷高效的数据库开发和建模工具方案40.GaussDB技术解读系列丨运维自动驾驶探索41.一次性全讲透GaussDB(DWS)锁的问题42.GaussDB(DWS)性能调优:Sort+Groupagg聚集引起的性能瓶颈案例43.多主架构:VLDB技术论文《Taurus MM: bringing multi-master to the cloud》解读44.GaussDB(for Redis)游戏实践:玩家下线行为上报45.一文详解数据仓库的物理细粒度备份恢复46.华为云HBase冷热分离最佳实践47.四问复合索引,让你的数据查询速度飞起48.GaussDB(DWS)案例丨MERGE场景下语句不下推引起的性能瓶颈问题49.如何强制SQL走性能更优的hash join50.如何使用GaussDB(DWS)的本地临时表进行数据处理51.华为云GaussDB亮相金融业数据库技术大会52.2个数仓中不等值关联优化案例53.数仓实时场景下表行数估算不准确引起的的性能瓶颈问题案例54.详解GuassDB数据库权限命令:GRANT和REVOKE55.DWS临时内存不可用报错: memory temporarily unavailable56.华为云GaussDB城市沙龙活动走进安徽,助力金融行业数字化转型57.理论+应用,带你了解数据库资源池58.人人用数不用愁,动态数据脱敏为您解忧59.实例讲解数据库的数据去重60.数仓实践丨表扫描时过滤行数过多引起的性能瓶颈问题
61.实例详解构建数仓中的行列转换
62.Proxy下的Prepare透传,让GaussDB(for MySQL)更稳固,性能更卓越63.浅析KV存储之长尾时延解决办法64.实例讲解数据库的定义重载函数65.详解数据库SQL中的三个语句:DROP、TRUNCATE 、DELETE66.华为云GaussDB助力工商银行、华夏银行斩获“十佳卓越实践奖”67.Navicat 基于 GaussDB 主备版的快速入门68.数仓实时算子难以观测,快来试试算子级监控吧69.列举数据库缓存使用场景实例和命令速查表70.带你认识多模数据库GeminiDB架构与应用实践71.3招解决时序数据高基数难题,性能多维度提升!72.数仓性能调优:row_number() over(p)-rn=1性能瓶颈发现和改写套路73.数仓实践丨常量标量子查询做全连接导致整体慢74.细说GaussDB(DWS)的2种查询优化技术75.细说SQL与ETL之间的小秘密76.从概念到实践,带你掌握层次递归查询77.GeminiDB Cassandra接口新特性PITR发布:支持任意时间点恢复78.你的JoinHint为什么不生效79.六步走向无忧,华为云数据库高可用的秘密武器80.数仓调优实践丨SQL改写消除相关子查询81.GaussDB(for MySQL)新特性TDE发布:支持透明数据加密82.详解GaussDB(DWS)通信安全的小妙招:连接认证机制83.GaussDB(for MySQL) RegionlessDB发布:全球数据库技术84.5分钟带您了解DRS录制回放85.ICDM'23 BICE论文解读:基于双向LSTM和集成学习的模型框架86.数仓如何递归查询视图依赖87.支撑核心系统分布式改造,GaussDB为江南农商银行筑稳根基88.近6成金融机构的选择!华为云GaussDB加快金融核心系统转型89.GaussDB(for MySQL)剪枝功能,让查询性能提升70倍!90.2023年度十佳课题公布:华为云GaussDB获权威认可91.详解如何在数仓中搭建细粒度容灾应用92.对话苏光牛:国内数据库市场已进入关键转折点,2024年或是分水岭93.GaussDB通信运维:详解stream连接池设计原理94.GaussDB(for MySQL) Serverless全面商用:无感弹性,极致性价比95.华为云GaussDB支撑农行超级网银业务,性能和稳定性备受认可96.实例详解数据库的游标管理97.数仓实践丨从CU入手优化HStore表98.数仓的等待视图中,为什么会有Hashjoin-nestloop99.如何基于Sharding-JDBC实现GaussDB在客户端应用的读写分离100.如何迅速并识别处理MDL锁阻塞问题本文分享自华为云社区《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数据为平台,为大家做了简单的讲述 ,欢迎测试。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 我与微信审核的“相爱相杀”看个人小程序副业
2022-11-08 动手实践丨使用华为云IoT边缘体验“边云协同”
2022-11-08 想会用synchronized锁,先掌握底层核心原理
2022-11-08 OpenHarmony移植案例: build lite源码分析之hb命令__entry__.py
2021-11-08 教你如何在Spark Scala/Java应用中调用Python脚本
2021-11-08 云上远程运维的最后那点担心,“云梯”帮你解决
2021-11-08 基于软件分析的智能化开发新型服务与技术
2021-11-08 Scrum Master们,难道每天都在摸鱼?