快速实现分列转到行(SQL版)一个问题,三种解法!
大家好,我是热心读者。
首先,这是一篇水文,但是作为一个系列的三胞胎之一,我觉得有必要通过一题多解来扩散一下思维,正所谓“条条大路通罗马”。
前两篇文章分别为:
数据源以及效果大致是这样的:
通过观察数据,我们发现其实达到的效果,就是列转行,读懂了题意,那么解法就很容易出来了。
下文语法为hivesql,其实在mysql或者sqlserver中也可以实现,不过是需要换一换函数即可,关系性数据库的作业 就留给读者来实现了。
好的,我们就先来构造一下数据:首先建表,两个字段:
create table if not exists student_info(
class string COMMENT '年级',
students string COMMENT '姓名'
);
其次,往表中插入数据:
INSERT INTO TABLE yht_dw.student_info
SELECT *
FROM (
SELECT '1年级' AS class
,'A1;B1;C1' AS name
UNION ALL
SELECT '2年级' AS class
,'A2;B2;C2' AS name
UNION ALL
SELECT '3年级' AS class
,'A3;B3;C3' AS name
UNION ALL
SELECT '4年级' AS class
,'A4;B4;C4' AS name
UNION ALL
SELECT '5年级' AS class
,'A5;B5;C5' AS name
UNION ALL
SELECT '6年级' AS class
,'A6;B6;C6' AS name
UNION ALL
SELECT '7年级' AS class
,'A7;B7;C7' AS name
UNION ALL
SELECT '8年级' AS class
,'A8;B8;C8' AS name
UNION ALL
SELECT '9年级' AS class
,'A9;B9;C9' AS name
) t
;
表中数据情况:
下面就直接公布答案:
SELECT t.class
,item AS name
FROM yht_dw.student_info t
LATERAL VIEW explode(split(t.students,';')) names AS item
;
上结果图:
来解释一下这里的语法
split(string,seprator)
split函数传入两个参数,对string按照seprator进行拆分,这里就相当于把一个字符串变成了一个array
explode
顾名思义,就是“爆炸”函数,就是把上面拆分的array给炸开,由一行三列,变为三行一列
lateral view
跟 explode是一个固定搭配,相当于一种笛卡尔积,将爆炸函数得到的行跟class关联起来
正常来讲,文章到此就结束了,但是太水我觉得内心不安;
而有的读者也会有疑问,我列转行了,那我如何“回去”呢?——也就是如何进行转列呢?
下面接着上脚本:
SELECT class
,collect_set(name) AS names
FROM (
SELECT t.class
,item AS name
FROM yht_dw.student_info t
LATERAL VIEW explode(split(t.students,';')) names AS item
) tt
GROUP BY class
;
上结果图:
我懒,没有重新构造数据,直接用的子查询,但是看到结果,可能有读者会有疑问,这个咋还有方括号,还有双引号,这个也太不美观了。
别急,我先解释完语法,解释完,再来优化。
其实hive里有一个类似于“汇总数据”sum的函数,collect_set是专注于文本的汇总杀器,而重要的是:它会剔除重复记录。
那如何解决方括号和双引号的问题呢?
其实也很简单,上脚本:
SELECT class
,concat_ws(';',collect_set(name)) AS names
FROM (
SELECT t.class
,item AS name
FROM yht_dw.student_info t
LATERAL VIEW explode(split(t.students,';')) names AS item
) tt
GROUP BY class
;
上优化后结果图:
其实就是加了一个concat_ws函数,此时就把方括号跟双引号给去除了。
好了,今天的文章就是这样,希望没有接触hive语法的同学有所得,已经熟悉hive的同学莫要嘲笑知识点的简单。
相关文章
- 从本体论开始说起——运营商关系图谱的构建及应用
- 如何成为一名数据科学家?
- 从未见过的堂兄杀了人,你的DNA是关键证据
- 20个安全可靠的免费数据源,各领域数据任你挑
- 20个安全可靠的免费数据源,各领域数据任你挑
- 阿里云李飞飞:All in Cloud时代,云原生数据库优势明显
- 基于Hadoop生态系统的一高性能数据存储格式CarbonData(性能篇)
- 大数据告诉你:10年漫威,到底有多少角色
- TigerGraph:实时图数据库助力金融风控升级
- Splunk利用Splunk Connected Experiences和Splunk Business Flow 扩大数据访问
- 大数据开发常见的9种数据分析手段
- 以免在景区看人,我爬了5W条全国景点门票数据...
- 【实战解析】基于HBase的大数据存储在京东的应用场景
- 数据科学家告诉你哪些计算机科学书籍是你应该看的
- Kafka作为大数据的核心技术,你了解多少?
- Spring Boot 整合 Redis 实现缓存操作
- 大数据学习必须掌握的五大核心技术有哪些?
- 基于Antlr在Apache Flink中实现监控规则DSL化的探索实践
- 甲骨文再次被Gartner评为分析型数据管理解决方案魔力象限领导者
- 爬取吴亦凡微博102118条转发数据,扒一扒流量的真假