SQL常见函数以及使用_sql函数的使用方法及实例大全

窗口函数：

over()
select rank() over(partition by b order by a )–排序按照1134#需要先partition再order逻辑是先group by再提取select所以要注意后面是否有group by
select dense_rank() over(partition by b order by a)–排序1123
select row_number() over(partition by b order by a)–排序1234–一般求中位数可以使用这个数据的值等于某个值
select lead(create_time,1,null) over(partition by b order by create_time)–对b进行分类，再对create_time进行排序，获得新的一列是create_time的值，但是是当前的create_time的下一个值，有点next_status的意思。
select sum(a) over(order by b rows between unbounded preceding and current row)–求累加和，每一行都是之前行的累加，改变参数才能避免同样的值累计值相同的情况，表示第一行到当前行
#可以看到窗口函数就是针对over()里的这个窗口取出的数据，可以换成其他的函数，包括max，avg等
取下一行的数据lead(),取上一行的数据lag()
https://blog.csdn.net/Schaffer_W/article/details/109029630

前10%的用户可以使用ntile(10) over(order by cnt desc)就能筛选出1对应的就是前10%，参数10就是将数据进行分组，分成了10组

字符函数

sub_str(a,4,2)–对a从第4位开始，取2位数据
char_length(‘10,8,2’)–计算字符串的长度
拆分“逗号”，lateral view & explode & split
https://blog.csdn.net/zhide2011bgs3/article/details/105182677

select
	user_id,
	case when b.room_num='[]' then Null else b.room_num end as room_num
from
( --初始样本数据
select * from 
table1 
where dt = '{
   { ds }}'
) a 
lateral view explode(split(a.room_list,',')) b as room_num
1
2
3
4
5
6
7
8
9
10
11

其他函数

replace(‘alshjddsfs’,‘a’,‘l’)–将字符串中的a替换成l

SELECT *
FROM employees
LIMIT 10,5–分页查询，5行每页，取第3页（5*（3-1）=10）
nvl(null,0)–将null值替换为0
nvl(A,B)–如果A是null就使用B来替换
coalesce(a.emoji_id,b.emoji_id,c.emoji_id)–如果a是null，选b，b是null，选c

1. 读取json字段：get_json_object(extra, ‘$.name’) 其中的extra是一个json字段，需要转一下提取其中的name中的内容
2. collect_list（有重复数据），collect_set（无重复），将分组中的某列转为一个数组返回
3. concat_ws(’,’,collect_list(concat(emoji_id,’-’,cnt)))#逗号可以插入到多个数值之间

将列转成行处理：collect_set（去重），collect_list（不去重）

-- 去重的合并
select userid, collect_set(username) username
from temp.strategy_temp_20200813_function_test t
group by userid
1
2
3
4

group_concat也可以实现：

SELECT id,GROUP_CONCAT(DISTINCT score) FROM testgroup GROUP BY id

SELECT id,GROUP_CONCAT(score ORDER BY score DESC) FROM testgroup GROUP BY id--排序
SELECT id,GROUP_CONCAT(score SEPARATOR ';') FROM testgroup GROUP BY id--设置分隔符

1
2
3
4
5

逻辑函数

in & not in

select deptno
from dept
where deptno in ( 10,50,null )
--相当于
select deptno
from dept
where (deptno=10 or deptno=50 or deptno=null)
--dept表中只有10的情况下，返回结果为10

select deptno
from dept
where deptno not in ( 10,50,null )
--相当于
select deptno
from dept
where not (deptno=10 or deptno=50 or deptno=null)
--返回值为空not in和null连用就是空的

--不要使用not in null，替换为
select d.deptno
from dept d
where not exists ( select null
from emp e
where d.deptno = e.deptno )
--把 EXISTS/NOT EXISTS 和关联子查询一起使用时，SELECT 列表里的项目并不重要，因此这个例子中用了 SELECT NULL，这是为了把注意力放到子查询的连接操作上，而非 SELECT 列表的项目上。

--其他例子
select *
from
father a 
where exists(select 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30