SQL常用的几个计算一

1.上、下一个结息日的计算

银行里面需要根据当前日期（字符串格式）计算上/下一个结息日,如果今天是2024/6/30，那么上一个结息日是2024/6/21，下一个结息日是2024/7/21，如果当天是21号，下一个结息日取当天。

思路，判断当前是大于，还是小于21号，目标字段和当前日期年月相同的把年月截取了，再链接一个'21'，不同的转化成日期类型，做加减，再转化为字符串，截取，拼接

WITH t AS (SELECT to_char(trunc(SYSDATE+rownum),'YYYY/MM/DD') due_dt FROM dual CONNECT BY rownum<=31)--创建测试表
SELECT 
due_dt
,CASE WHEN substr(due_dt,-2,2)<='21' THEN substr(due_dt,1,8)||'21' ELSE substr(to_char(add_months(to_date(due_dt,'yyyy/mm/dd'),1),'yyyy/mm/dd'),1,8)||'21' end Next21 
,CASE WHEN substr(due_dt,-2,2)>'21' THEN substr(due_dt,1,8)||'21' ELSE substr(to_char(add_months(to_date(due_dt,'yyyy/mm/dd'),-1),'yyyy/mm/dd'),1,8)||'21' end Last21
FROM t

2.小数位向上取整

        业务要求小数位保留到第6位，第七位如果不是0，那么需要进位。比如1.12345601为1.123456，1.1234567，结果为1.123457.把前同事给难住了，来问我。

算法一：直接分析第7位是不是0，用这个数减去这个数截取小数点后6位，如果大于10的负7次方则不为0，否则为0

with t as (select 1.123456+rownum*0.00000001 a from dual connect by rownum<=15)
select a 加工前,
case when a-trunc(a,6)>=0.0000001 then trunc(a,6)+0.000001 else trunc(a,6) end 加工后
from t

算法二：截取到第七位，用ceil去判断第七位是否为0

with t as (select 1.123456+rownum*0.00000001 a from dual connect by rownum<=15)
select a 加工前,
ceil(trunc(a,7)*1000000)/1000000
from t

3.转化为秒

同事需要把“X天X小时X分”转化成秒

算法1：

以时间单位为定位点，截取时间数字，然后将这些数字*系数

with t as (select rownum||'天'||rownum||'小时'||rownum||'分钟' a from dual connect by rownum<60) 
select a,
substr(a,0, instr(a,'天')-1)*24*3600
+substr(a, instr(a,'天')+1, instr(a,'小时')- instr(a,'天')-1)*3600
+substr(a,instr(a,'小时')+2,instr(a,'分钟')- instr(a,'小时')-2)*60 Seconds
,rownum*(24*3600+3600+60)验证列
from t

算法2：

正则表达式函数regexp_substr去匹配数字，然后再计算

select '1天2小时3分钟'||'=' TimeSpent,regexp_substr('1天2小时3分钟','(\d+)天',1,1,'i',1)*24*3600
+regexp_substr('1天2小时3分钟','(\d+)小时',1,1,'i',1)*3600
+regexp_substr('1天2小时3分钟','(\d+)分钟',1,1,'i',1)*60 Seconds
 from dual

正则函数用法详见：Oracle 正则表达式详解（regexp_substr、regexp_instr、regexp_replace、regexp_like）_oracle正则表达式-CSDN博客