建设银行积分兑换商城官方网站,近期的新闻消息,成立新公司企业策划书,网站备案地址不是我的地址怎么办日常工作中#xff0c;为了让数据的可读性更强#xff0c;经常会对数据格式进行转化操作。总结一下日常工作中遇到的关于行列操作问题。
单行拆分成多行 --创建测试数据
CREATE TABLE fwj.customer
(
id STRING,
name STRING,
mobiles STRING);INSERT INTO fwj.customer
SEL…日常工作中为了让数据的可读性更强经常会对数据格式进行转化操作。总结一下日常工作中遇到的关于行列操作问题。
单行拆分成多行 --创建测试数据
CREATE TABLE fwj.customer
(
id STRING,
name STRING,
mobiles STRING);INSERT INTO fwj.customer
SELECT 1,jim,139,177,158 FROM system.dual;-- 解法一不建议选择
SELECT a.id,a.name,substr(a.mobiles,1,3) mobiles FROM fwj.customer a
UNION ALL
SELECT a.id,a.name,substr(a.mobiles,5,3) mobiles FROM fwj.customer a
UNION ALL
SELECT a.id,a.name,substr(a.mobiles,9,3) mobiles FROM fwj.customer a -- 解法二
SELECT b.id,a.mobiles,b.name FROM (
SELECT explode(split(t.mobiles,,)) mobiles FROM fwj.customer t )a , fwj.customer b;
-- 优化但不行。explode 这类UDTF函数不支持和其他字段一块被select。
SELECT t.id,t.name,explode(split(t.mobiles,,)) mobiles FROM fwj.customer t -- 解法三可以理解成在一次查询中
-- 先生成了一个视图 mob 包含了行转列后的数据之后从mob 中取出转换后的数据
-- 其他字段仍旧从原表中取。
SELECT a.id,a.name,mob.mobile
FROM fwj.customer a lateral view explode(split(a.mobiles,,)) mob AS mobile;split(str,sep): 该函数的作用是拆分指定分隔符分割的字符串返回一个列表。
SELECT split(a.mobiles,,) FROM fwj.customer a;result:
[139,177,158]explode(arr): 该函数是一个表生成函数。输入一个列表参数将列表中的每个值都转换为一行。
SELECT explode(plit(a.mobiles,,)) mobiles FROM fwj.customer a;result:mobiles
139
177
158行列互换
测试集
/*创建数据库测试表*/
CREATE TABLE [Scores]([ID] INT IDENTITY(1, 1) PRIMARY KEY ,[Student] VARCHAR(20) ,[Subject] VARCHAR(30) ,[Score] FLOAT)/*插入数据库测试数据信息*/INSERT INTO Scores( Student, Subject, Score )VALUES ( test001, 语文, 90 )INSERT INTO Scores( Student, Subject, Score )VALUES ( test001, 英语, 85 )INSERT INTO Scores( Student, Subject, Score )VALUES ( text002, 语文, 90 )INSERT INTO Scores( Student, Subject, Score )VALUES ( text002, 英语, 80 )INSERT INTO Scores( Student, Subject, Score )VALUES ( test003, 语文, 95 )INSERT INTO Scores( Student, Subject, Score )VALUES ( test003, 英语, 85 )1. case when …then else …end 用法行列转换
SELECT Student AS 姓名 ,MAX(CASE SubjectWHEN 语文 THEN ScoreELSE 0END) AS 语文 ,--如果这个行是“语文”就选此行作为列MAX(CASE SubjectWHEN 英语 THEN ScoreELSE 0END) AS 英语
FROM ScoresGROUP BY StudentORDER BY Student2. pivot(聚合函数(要转成列值的列名) for 要转换的列 in目标列名
SELECT Student AS 姓名 ,AVG(语文) AS 语文 ,AVG(英语) AS 英语
FROM Scores PIVOT( AVG(Score) FOR Subject IN ( 语文, 英语 ) )as NewScores
GROUP BY Student
ORDER BY Student ASC拓展题
将上表转化为下表的格式
-- 解法一实用性不如解法二但可能图二无法满足推荐使用解法二
select t1.item,t1.[time]as startdate,min(t2.[time])as endtime
from t1,t1 as t2
where t1.typestart
and t1.typet2.type
and t2.[time]t1.[time]
group by t1.item,t1.[time]-- 解法二
Select * From T1;
With S1 As (Select SeqNo, Item,Type,IIf(TypeStart, Time,Null) sTime,IIf(TypeEnd, Time,Null) eTime, Lag(Type,1,0) Over (Partition By Item Order By Time) LastType From T1 ),S2 As ( Select SeqNo,Item,Type,sTime,eTime,LastType,Sum(IIF(TypeStart,1,IIF(TypeEnd And LastTypeStart,0,1)))Over (Partition By Item Order By SeqNo) Gn From S1 )
Select Item,Gn,Max(sTime) sTime,Max(eTime) eTime From S2 Group By Item,Gn解法二计算过程 1.Time 的类型 设置为 Time(0) 2.S2代码思路累加 分组 编号 …避免万一 中间 没有漏掉了 Start 记录 却有 End 记录 的情况。如果是 Start 直接加1 如果是End 看下 前面是不是 Start 是 就不加了如果是第1条记录 还是加 1。
