1、常用聚合函数
- max min avg
2、开窗函数
- row_number() over(pritition by a order by b ) as rn 相同的顺序随机连续排序
- dense_rank() over(partition by a order by b ) as rn 排序相同时会重复,总数会减少
- rank() over(partition by a order by b) as rn 排序相同时会重复,总数不会变
- min(score) over(distribute by dt sort by score desc) as rn 类似 sum() avg() max() min()
-
lag() lead() first_value() last_value()
3、udf、udaf函数
4、其他常用函数
- concat
- concat_ws
- collect_set
- collect_list
- coalesce
5、instr(str,substr) 返回substr在str第一次出现的位置(从1开始计数),如果substr在str中不存在则返回0,
select instr('23e,wec',',') -- 4
select instr('23e,wec','f') -- 0
select instr('23e,wec','') -- 1
select instr('2f3ef,wec','f') -- 2
select instr('23e,wec','wec') -- 5
select instr('23e,wec','wecv') -- 0