窗口函数 还是 group by
如果我想取某个字段聚合之后其他字段取最新时间的那个值,怎么取呢,是不是只要窗口函数row_number按照字段partition by 按照时间order by desc 然后取第一个就行了例如:
SELECT *
FROM (
SELECT *
,ROW_NUMBER() OVER (PARTITION BY … ORDER BY … DESC ) rn
from …
)
where rn =1
例子:
col1 col2 col3 col4 col5
1, a, 啊, 4, 2023-01-11
1, b, 吧, 2, 2023-01-12
1, c, 成, null, 2023-01-13
结果:
1, c, 成, null, 2023-01-13
但是 如果我有的字段想用最新时间的,有的字段用最早时间的,有的字段是用最新的一条不为空的比如
col1 col2 col3 col4 col5
1, a, 啊, 4, 2023-01-11
1, b, 吧, 2, 2023-01-12
1, null, 成, null, 2023-01-13
我想要的结果:
1, null, 啊, 2, 2023-01-13
解释如下:
col2取最新时间的也就是 null,col3取最早时间的也就是啊,col4取最新时间的但是如果最新时间的是null就取上一个最新时间直到取到不为null的,col5取最新时间
怎么办呢
其实不用窗口函数也可以实现这个功能,我们需要和时间字段进行拼接然后再分组最后再切割(注意 concat中包含null结果就是null,max和min聚合中包含null不加入计算)
试例代码如下:
with t1 as
(
select
id,
concat(col5,'&&',nvl(col2,'null')) as tmp_sort_col2,
concat(col5,'&&',nvl(col3,'null')) as tmp_sort_col3,
concat(col5,'&&',col4) as tmp_sort_col4,
col5
from ...
),t2 as(
select
id,
max(substring_index(tmp_sort_col2,'&&',-1)) as col2,
min(substring_index(tmp_sort_col3,'&&',-1)) as col3,
max(substring_index(tmp_sort_col4,'&&',-1)) as col4,
max(col5) as col5
from
t1
group by
id
)
select
id,
if(col2 ='null',null,col2) as col2,
if(col3='null',null,col3) as col3,
col4,
col5
from
t2
json相关
1.string get_json_object(string , string
但是如果是json数组该怎么取比如
[
{
“empCode”: “1018”,
“id”: 20,
“realName”: “a”,
“userName”: “lddd”
},
{
“empCode”: “9443”,
“id”:65,
“realName”: “b”,
“userName”: “sai.tan”
},
{
“empCode”: “9d932”,
“id”: 27,
“realName”: “c”,
“userName”: “dddfdsf”
}
]
我想要取出所有的realName形成一个数组[“a”,“b”,“c”]这个时候可以使用json_tuple
2.string json_tuple(string , string , string ,…)
select users
lateral view
json_tuple('[{"empCode": "1018","id": 20,"realName": "a","userName": "lddd"},{"empCode": "9443","id":65,"realName": "b","userName": "sai.tan"},{"empCode": "9d932","id": 27,"realName": "c","userName": "dddfdsf"}]',"[*].realName") q as users
结果:[“a”,“b”,“c”]
注意:这个是字符串类型的不是数组类型的,只是长得像数组
当数组中只有一个json的时候不会加[] 比如[{“empCode”: “1018”,“id”: 20,“realName”: “a”,“userName”: “lddd”}]只会返回"a"
字符串相关:
string substring_index(string , string , int )
如果让你从字符串"ab[cd"中取出[后面的数怎么取呢?是不是要拿到[的下标然后substring呢现在直接用substring_index就可以 而且想要之前之后或者第几个都可以
select substring_index('ab[cd','[',-1) 结果cd
select substring_index('ab[cd','[',1) 结果ab
正则相关
涉及函数
1.string regexp_extract(string , string [, bigint ]) 一般是从一段字符串中取出你想要的内容
举例
select regexp_extract(‘普通告警’,‘\\(.?)\\*’); 结果:普通告警
2.string regexp_replace(string , string , string <replace_string>[, bigint ])
举例
select regexp_replace(‘[“a”,“b”,“c”]’,‘[\[\]]’,“”) 结果:“a”,“b”," c"
推荐网址
www.regexr-cn.com
https://c.runoob.com/front-end/854/
https://codejiaonang.com/
基础入门
字符组 []
[1-9] [1-9a-z] [^1-9] [[]
快捷方式
\w 与任意单词字符匹配,任意单词字符表示 [A-Z]、 [a-z]、[0-9]、_
\d 与任意数字匹配
\s快捷方式可以匹配空白字符,比如空格,tab、换行等
\b 匹配的是单词的边界,
取反就是大写
^ 开头 $结尾
. 任意的东西
?可有可无
.? 结合使用
匹配多个数据
{} 重复的数
\d{9} 匹配9位数
\d{8,9} 优先匹配9位
\d{8,9}? 优先8位 非贪婪模式
{0,} >=0位 相当于*
{1,} >=1位 相当于+
a.+ a开头的所有 a不能匹配
a.* a开头的所有 a能匹配
⚠️对于字符串abxsw
.+?是匹配到a .表示任意,+表示>=1, ?表示尽可能少也就是1
.*?是匹配到空白 .表示任意,+表示>=0, ?表示尽可能少也就是0
select regexp_extract(‘[“a”,“b”,“c”]’,‘\[(.*?)\]’); 结果:“a”,“b”," c"
提升进阶
分组 ()
或 |
回溯分组 \1
对于下面的内容,想提取对称标签的内容
提示
提示
使用 <\w+>(.*?)</\1> 才会提取到
先行断言
正向先行断言:(?=表达式),指在某个位置向右看,表示所在位置右侧必须能匹配表达式
例如:
我喜欢你 我喜欢 我喜欢我 喜欢 喜欢你
如果要取出喜欢两个字,要求这个喜欢后面有你,这个时候就要这么写:喜欢(?=你),这就是正向先行断言。
反向先行断言
(?!表达式)的作用是保证右边不能出现某字符。
例如: 我喜欢你 我喜欢 我喜欢我 喜欢 喜欢你
如果要取出喜欢两个字,要求这个喜欢后面没有你,这个时候就要这么写:喜欢(?!你),这就是反向先行断言
正向后行断言
(?<=表达式),指在某个位置向左看,表示所在位置左侧必须能匹配表达式
例如:如果要取出喜欢两个字,要求喜欢的前面有我,后面有你,这个时候就要这么写:(?<=我)喜欢(?=你)。
反向后行断言
(?<!表达式),指在某个位置向左看,表示所在位置左侧不能匹配表达式
例如:如果要取出喜欢两个字,要求喜欢的前面没有我,后面没有你,这个时候就要这么写:(?<!我)喜欢(?!你)。
select regexp_extract(‘[“a”,“b”,“c”]’,‘(?<=[).*?(?=])’); 结果:“a”,“b”," c"