SQL常用函数整理

-- 创建视图
create view table_A as 
select st.*,
	a.s_score 课程1分数,
	b.s_score 课程2分数
from student st
join (select s_id, s_score from score where c_id = "01") a
on st.s_id = a.s_id 
join (select s_id, s_score from score where c_id = "02") b
on st.s_id = b.s_id 
where a.s_score > b.s_score;

-- week(): 获取周数
select week(now());

-- substring_index(): 按指定分隔符拆分，得到指定个数
select substring_index('2021/12/05', '/', 1);
-- 2021
select substring_index('2021/12/05','/',2);
--2021/12
select substring_index('2021/12/05','/',-1);
--02

--extract(): 获取时间、日期
select extract(month from now());

-- 声明变量
select s_id, (@rnk := @rnk + 1) as 'N'
from student, (select @rnk := 0) r

-- 1.group_concat(): 连接同一分组的值
select group_concat(c_id) from score where s_id = '01'

-- 1.concat_ws('+','a','b'): 指定连接符连接
select CONCAT_WS('+','a','b');
-- 1.concat(): 拼接 / round(num, n): 四舍五入保留n位小数
select concat(round(0.8000 * 100, 1), '%') as '留存';
select round(0.86, 1)
select concat(10, '%') 'percent'
select concat('熊', '桑', 'tql') 'percent'
select concat(round(0.12, 1), '%') 'percent'


-- 2.CAST(expr AS type): 转换类型
select cast(now() as date) 'date';


-- 3.SUBSTR(str FROM pos FOR len): 从指定下标开始截取自定义长度的字符串 中文时不用
select substr('yangkaiwang', 5, 3) res;

-- 4.SUBSTRING(str FROM pos FOR len): 从指定下标开始截取自定义长度的字符串
select substring('yangkaiwang', 8, 4) res;


-- 5.REPLACE(str,from_str,to_str): 替换指定字符串
select replace('yadada', 'ya', 'yang') name;

-- 6.left(str, len): 从左截取指定长度字符串
select left(17607947515, 7);

-- 7.right(str, len): 从右截取指定长度字符串
select right(17607947515, 8);

-- 8.mod(n, m): 取余
select mod(11, 2);

-- 9.mid(str, pos, len): 截取子串
select mid('yangdada', 5, 4)

-- 10.repeat(str, n): 文本重复
select repeat('*', 3);

-- 11.sqrt(n): 开根号
select sqrt(4);

-- IF(expr1,expr2,expr3): expr1为真返回expr2,假返回expr3
select if(True, '真', '假') res;
select if(False, '真', '假') res;


-- IFNULL(expr1,expr2): expr1为null返回expr2, 不为null返回本身
select ifnull(name, 1) res from n;
select ifnull(age, 18) res from n;

-- LAG(col,n,default_val)：获取往前第n行数据，col是列名，n是往上的行数，当第n行为null的时候取default_val
select
	'熊桑' Name,
	cur_stage,
	repay_amount,
	lag(repay_amount, 1, 0) over (order by cur_stage) lag_repay_amt1, -- 往上取一行,空值用0代替
	lag(repay_amount, 1, -1) over (order by cur_stage) lag_repay_amt2, -- 往上取一行,空值用-1代替
	lag(repay_amount, 3, -3) over (order by cur_stage) lag_repay_amt3 -- 往上取一行,空值用-3代替
from jq;


-- LEAD(col,n, default_val)：往后第n行数据，col是列名，n是往下的行数，当第n行为null的时候取default_val)
select
	'熊桑' Name,
	cur_stage,
	repay_amount,
	lead(repay_amount, 1, 0) over (order by cur_stage) lead_repay_amt1, -- 往下取一行,空值用0代替
	lead(repay_amount, 1, -1) over (order by cur_stage) lead_repay_amt2, -- 往下取一行,空值用-1代替
	lead(repay_amount, 3, -3) over (order by cur_stage) lead_repay_amt3 -- 往下取一行,空值用-3代替
from jq;

-- NTILE(N): 将有序分区中的行分发到指定数目的组中, N=2,有序分组的排序只能包含1,2
select 
	'熊桑' Name,
	km,
	cj,
	ntile(2) over (partition by km order by cj desc) tile
from score

-- 模糊查询 like / %: 任意多个字符 / _: 任意单个字符
-- like中的语法不是正则， rlike中的语法是正则
-- like: 语法不是正则，只有%和_
select *
from test1_pagevisit
where visit_time like '2021%3';

select *
from test1_pagevisit
where visit_time like '2021-09-_3';

-- rlike: 语法是正则， 正则的语法都能用
select *
from test1_pagevisit
where visit_time rlike '2021.*3';

select *
from test1_pagevisit
where visit_time rlike '2021-09-.3';

select *
from test1_pagevisit
where visit_page rlike 'logi[n]{1}';

-- regexp
select *
from test1_pagevisit
where visit_ip regexp '127.*';

select *
from test1_pagevisit
where visit_time regexp '2021-10.*';

-- hive中正则提取和替换函数
-- regexp_extract

-- regexp_repalce