sql的一些整理

窗口函数 还是 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字符串中取出我们想要的字段 介绍略 可参考文档https://help.aliyun.com/document_detail/455461.html

但是如果是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"