学记笔记—sql中基础函数的使用

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;

如果 product_data 这个字段不是一个数组，是一个字符串，比如是：鞋底穿着痕迹/鞋面穿着痕迹
那个需要使用split函数将他拆分为一个数组，如下：

select 
        a.dp,
        a.product_id,
        a.product_data,
        data_id
from  dim_product_info as a 
lateral view explode(split(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;