1、时间函数
--当前系统时间:2020-12-13 22:21:43
select getdate();--2020-12-13 22:21:43
select substr(getdate(),1,10);--2020-12-13
select dateadd(getdate(),10,'dd');--2020-12-23 22:39:02 加10天
select dateadd(getdate(),-10,'dd');--2020-12-03 22:40:18 减10天
select datediff('2020-12-20','2020-12-10');--10
select to_date('2020-12-01 12:33:33','yyyy-mm-dd hh:mi:ss');--2020-12-01 12:33:33
select unix_timestamp(getdate());--1607870896
select from_unixtime(unix_timestamp(getdate()));--2020-12-13 22:49:42
2、窗口函数
①专用窗口函数
rank() over(partition by …order by …)–相同数值的排名相同,相同排名之后会跳过排名位次
dense rank() over(partition by …order by …)–相同数值的排名相同,相同排名之后不会跳过排名位次
row_number() over(partition by …order by …)–排名具有唯一性,相同数值的随机排名
如下,下图来源:https://zhuanlan.zhihu.com/p/92654574
② 聚合函数做窗口函数:
count() over(partition by …order by …)–返回指定分组的目标记录数
sum() over(partition by …order by …)-- 返回指定分组的目标求和数
avg() over(partition by …order by …)-- 返回指定分组的目标平均数
max() over(partition by …order by …)–返回指定分组的目标最大值
min() over(partition by …order by …)–返回指定分组的目标最小值
3、 lag():返回上行中指定的类型
select
cx_id,
city_name,
manage_id,
lag(manage_id,1,-1) over (partition by city_name order by manage_id ) as rank
from sql_practice_1
where
manage_id <> 0;
4、lateral view explode():炸裂字段
select
a.dp,
a.product_id,
a.product_data,
data_id
from dim_product_info as a
lateral view explode(product_data) b as data_id --炸裂目标字段product_doctor,并且形成视图命名为b 命名不可缺失
where
a.dp = '2020-12-01'
AND a.product_status = 1
limit 1000;
结果如下:
5、填充函数:lpad 与 rpad ,左填充与右填充
1、左填充
select rpad('早上好,陌生人',11,'xxoo');
输出结果:xxoo早上好,陌生人
2、右填充
select lpad('早上好,陌生人',11,'xxoo');
输出结果:早上好,陌生人xxoo
6、字符串查询函数
1、instr:返回字符串中目标字符所在的位置序号
select instr('hello','o')
输出结果:5
2、find_inset()
7、手机版本号的判定
SELECT concat(split('8.21.0','\\.')[0],lpad(concat(split('8.21.0','\\.')[1]),2,0),lpad(concat(split('8.21.0','\\.')[2]),2,0)) >= 82100;