本文目录：
一、行列转换
二、排名中取他值
三、累计求值
四、窗口大小控制
五、产生连续数值
六、数据扩充与收缩
七、合并与拆分
八、模拟循环操作
九、不使用distinct或group by去重
十、容器--反转内容
十一、多容器--成对提取数据
十二、多容器--转多行
十三、抽象分组--断点排序
十四、业务逻辑的分类与抽象--时效
十五、时间序列--进度及剩余
十六、时间序列--构造日期
十七、时间序列--构造累积日期
十八、时间序列--构造连续日期
十九、时间序列--取多个字段最新的值
二十、时间序列--补全数据
二十一、时间序列--取最新完成状态的前一个状态
二十二、非等值连接--范围匹配
二十三、非等值连接--最近匹配
二十四、N指标--累计去重
七、合并与拆分
表名：t7
表字段及内容：
a b
2014 A
2014 B
2015 B
2015 D
问题一：合并
输出结果如下所示：
2014 A、B
2015 B、D
参考答案:
select
a,
concat_ws('、', collect_set(t.b)) b
from t7
group by a;
问题二：拆分
问题描述：将分组合并的结果拆分出来
参考答案：
select
t.a,
d
from
(
select
a,
concat_ws('、', collect_set(t7.b)) b
from t7
group by a
)t
lateral view
explode(split(t.b, '、')) table_tmp as d;
八、模拟循环操作
表名：t8
表字段及内容：
a
1011
0101
问题一：如何将字符'1'的位置提取出来
输出结果如下所示:
1,3,4
2,4
参考答案：
select
a,
concat_ws(",",collect_list(cast(index as string))) as res
from (
select
a,
index+1 as index,
chr
from (
select
a,
concat_ws(",",substr(a,1,1),substr(a,2,1),substr(a,3,1),substr(a,-1)) str
from t8
) tmp1
lateral view posexplode(split(str,",")) t as index,chr
where chr = "1"
) tmp2
group by a;
九、不使用distinct或group by去重
表名：t9
表字段及内容：
a b c d
2014 2016 2014 A
2014 2015 2015 B
问题一：不使用distinct或group by去重
输出结果如下所示：
2014 A
2016 A
2014 B
2015 B
参考答案：
select
t2.year
,t2.num
from
(
select
*
,row_number() over (partition by t1.year,t1.num) as rank_1
from
(
select
a as year,
d as num
from t9
union all
select
b as year,
d as num
from t9
union all
select
c as year,
d as num
from t9
)t1
)t2
where rank_1=1
order by num;
十、容器--反转内容
表名：t10
表字段及内容：
a
AB,CA,BAD
BD,EA
问题一：反转逗号分隔的数据：改变顺序，内容不变
输出结果如下所示：
BAD,CA,AB
EA,BD
参考答案：
select
a,
concat_ws(",",collect_list(reverse(str)))
from
(
select
a,
str
from t10
lateral view explode(split(reverse(a),",")) t as str
) tmp1
group by a;
问题二：反转逗号分隔的数据：改变内容，顺序不变
输出结果如下所示：
BA,AC,DAB
DB,AE
参考答案：
select
a,
concat_ws(",",collect_list(reverse(str)))
from
(
select
a,
str
from t10
lateral view explode(split(a,",")) t as str
) tmp1
group by a;
十一、多容器--成对提取数据
表名：t11
表字段及内容：
a b
A/B 1/3
B/C/D 4/5/2
问题一：成对提取数据，字段一一对应
输出结果如下所示：
a b
A 1
B 3
B 4
C 5
D 2
参考答案:
select
a_inx,
b_inx
from
(
select
a,
b,
a_id,
a_inx,
b_id,
b_inx
from t11
lateral view posexplode(split(a,'/')) t as a_id,a_inx
lateral view posexplode(split(b,'/')) t as b_id,b_inx
) tmp
where a_id=b_id;
十二、多容器--转多行
表名：t12
表字段及内容：
a b c
001 A/B 1/3/5
002 B/C/D 4/5
问题一：转多行
输出结果如下所示：
a d e
001 type_b A
001 type_b B
001 type_c 1
001 type_c 3
001 type_c 5
002 type_b B
002 type_b C
002 type_b D
002 type_c 4
002 type_c 5
参考答案:
select
a,
d,
e
from
(
select
a,
"type_b" as d,
str as e
from t12
lateral view explode(split(b,"/")) t as str
union all
select
a,
"type_c" as d,
str as e
from t12
lateral view explode(split(c,"/")) t as str
) tmp
order by a,d;
十三、抽象分组--断点排序
表名：t13
表字段及内容：
a b
2014 1
2015 1
2016 1
2017 0
2018 0
2019 -1
2020 -1
2021 -1
2022 1
2023 1
问题一：断点排序
输出结果如下所示：
a b c
2014 1 1
2015 1 2
2016 1 3
2017 0 1
2018 0 2
2019 -1 1
2020 -1 2
2021 -1 3
2022 1 1
2023 1 2
参考答案:
select
a,
b,
row_number() over( partition by b,repair_a order by a asc) as c--按照b列和[b的组首]分组，排序
from
(
select
a,
b,
a-b_rn as repair_a--根据b列值出现的次序,修复a列值为b首次出现的a列值,称为b的[组首]
from
(
select
a,
b,
row_number() over( partition by b order by a asc ) as b_rn--按b列分组,按a列排序,得到b列各值出现的次序
from t13
)tmp1
)tmp2--注意，如果不同的b列值，可能出现同样的组首值，但组首值需要和a列值 一并参与分组，故并不影响排序。
order by a asc;
网友评论