系统函数篇
# Oracle
# 正则替换函数REGEXP_REPLACE(不规则日期修改)
-- 列举出可能情况,使用正则表达式进行处理
select CASE WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{1})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{1})$', '\1-0\2-0\3')
WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{2})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{1})[-|/](\d{2})$', '\1-0\2-\3')
WHEN REGEXP_LIKE('2021-1-02', '^(\d{4})[-|/](\d{2})[-|/](\d{1})$') THEN REGEXP_REPLACE('2021-1-02', '^(\d{4})[-|/](\d{2})[-|/](\d{1})$', '\1-\2-0\3')
ELSE '2021-1-02' END
from dual;
-- 转为日期格式,抽取出年、月、日,左补全 位数
select extract(YEAR from date'2022-8-11') || LPAD(extract(MONTH from date'2022-8-11'), 2, '0') || LPAD(extract(DAY from date'2022-8-11'), 2, '0') year from dual;
# 聚合函数lag/lead
lead(value_expr [,offset][,default]) over([query_partition_clause] order by Order_by_clause)
lag(exp_str,offset,defval) over()
exp_str 是要做对比的字段
offset 是exp_str字段的偏移量 比如说 offset 为2 则 拿exp_str的第一行和第三行对比,第二行和第四行,依次类推,offset的默认值为1!
defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。
# 聚合函数wm_concat
注:Oracle 19c中,wm_concat失效。wm_concat函数是oracle的非公开函数,在新版的oracle中不支持该函数。
解决方法:
- 创建 wm_concat 函数, 参考连接:https://blog.csdn.net/sun2012930/article/details/111712882
- 使用
listagg(多行转单行的列,分隔符) within group(order by 排序字段)
写法替换(返回类型为字符串,长度最大为4000)- 使用
xmlagg(xmlparse( content(多行转单行的列) ) order by 排序字段).getclobval()
写法替换
多行变一行
- 类似于 MySQL 中的 GROUP_CONCAT
GROUP_CONCAT( [distinct] 要连接的字段 [ORDER BY 排序字段 ASC/DESC ] [separator '分隔符'] )
- 类似于 Hive 中的 concat_ws + collect_list(collect_set)效果
concat_ws('_', collect_set(id))
# 单行函数L[R]PAD
语法
LPAD(string,padded_length,[ pad_string]) :从左开始填充,L:left 左,PAD:pad 填充
RPAD(string,padded_length,[ pad_string]) :从右开始填充,R:right 右,PAD:pad 填充
解释
string:原数据,即要被填充的数据;
padded_length:填充后的长度;
pad_string:填充字符串 可选填,如果不填就粘贴空格
# 单行函数MD5加密
select utl_raw.cast_to_raw(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING => 'test')) from dual
# SYS.UTL_MATCH.edit_distance_similarity 文本相似度计算
点击查看
过程梳理:
由于有两个数据量过万的表数据进行对比,且编码不一致不能进行比对,只能通过同一区划,相似名称进行比对,确认一对一关系。
确定思路是把数据分成多部分,能确认一对一关系的先插入结果表中。
先能想到的是通过等值关联取能关联上的先插入到结果表。
后通过instr(str1, str2) > 0关联,将此部分数据插入到结果表。
后由于instr只能找出连续子串,非连续的简称一类并不能解决,采用自定义函数通过遍历短字符串的每个字符,到长字符串中查找如果有找不到的字符,返回-1;若全能找到返回短串的长度。
后由于有些名称有错别字或其他问题,上面的自定义函数并不能解决,故考虑判断字符串的相似度,Oracle自带判断文本相似度函数,故用其作为关联条件。
SELECT A.AGENCY_ID,A.AGENCY_CODE,A.AGENCY_NAME,A.MOF_DIV_CODE,A.MOF_DIV_NAME,B.GUID,B.NAME,B.CODE,PROVINCE
FROM YTHDWXX A
JOIN DZXTDWXX B
ON SYS.UTL_MATCH.edit_distance_similarity(a.agency_name,b.name) > 80
AND MOF_DIV_CODE = rpad(PROVINCE, 9, 0)
# oracle实现数据炸裂效果
--oracle根据分隔符将一行拆分为多行
with tmp as --临时数据集
(select '1,2,3' val
from dual
union all
select '4,5,6' val
from dual)
select regexp_substr(t.val, '[^,]+', 1, t2.lv)--截取对应行数的数据
from tmp t,
(select level lv--生成行数序列数据 1到最大行数
from (select max(regexp_count(a.val, '[^,]+', 1)) r_count--计算数据集中拆分后最大的行数
from tmp a) b
connect by level <= b.r_count) t2
where regexp_substr(t.val, '[^,]+', 1, t2.lv) is not null-- 排除掉空的数据
# oracle行列转换
# 行转列
- PIVOT
SELECT * FROM (数据查询集)
PIVOT
(
SUM(Score/*行转列后 列的值*/) FOR
coursename/*需要行转列的列*/ IN (转换后列的值)
)
-- 通俗理解就是把一列拆成了多列,被拆的列不能再被select?
- 通过 group by + case when 判断实现
# 列转行
- unpivot
select 字段 from 数据集
unpivot(自定义列名/*列的值*/ for 自定义列名 in(列名))
- 利用union all 进行拼接
# 自定义函数(存储过程)
用于匹配简称
-- 一个字符串包含另一个字符串中的所有字符
create or replace function checks(v_a varchar2,v_b varchar)
return number
as
num number;
cou number;
j number;
index1 number;
begin
num := -1;
cou:=0;
j := 0;
index1 := 1;
for i in 1..length(v_b) loop
j := instr( substr(v_a,index1,length(v_a)), substr(v_b,i,1) );
if j > 0 then
cou:=cou+1;
index1 := index1 + j;
else
return num;
end if;
end loop;
return cou;
--dbms_output.put_line(cou||' '||length(v_b));
end;
# MERGE INTO 语法
MERGE INTO是Oracle SQL中用于将一个表中的数据合并到另一个表中的命令。该命令可以根据指定的条件来确定在目标表中是插入还是更新记录。
MERGE INTO target_table
USING source_table
ON (condition)
WHEN MATCHED THEN
UPDATE SET target_column = source_column
WHEN NOT MATCHED THEN
INSERT (target_column1, target_column2, ...) VALUES (source_column1, source_column2, ...)
其中
- target_table是要合并到的目标表;
- source_table是要从中获取数据的源表;
- condition是用于匹配目标表和源表的条件;
- UPDATE SET语句用于更新目标表中已有的记录;
- INSERT语句用于将源表中不存在的记录插入到目标表中。
编辑 (opens new window)
上次更新: 2023/07/30, 14:54:46