背景
在判断指标更新是否及时的过程中，需要根据指标的更新周期和更新时间判断指标更新是否及时，业务更新周期分别有每日、每小时、实时、每天、每月、每季度、每半年、每年、不定期、不更新等，代码中最细粒度只到天维度，小时级和分钟级不统计；
代码
case when ysjgxzq = '每日' or ysjgxzq = '每小时' or ysjgxzq = '实时' then
case when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -1,'滞后','正常') -- yyyy-mm-dd hh:mm:ss
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -1,'滞后','正常')-- yyyy-mm-dd hh:mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -1,'滞后','正常') -- yyyy-mm-dd
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}$')
then if(ywsj_update <> sbsj ,'滞后','正常') -- yyyy-mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}$')
then '格式不合规' -- yyyy
else '格式不合规' end
when ysjgxzq = '每月' then
case when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -31,'滞后','正常') -- yyyy-mm-dd hh:mm:ss
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -31,'滞后','正常')-- yyyy-mm-dd hh:mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -31,'滞后','正常') -- yyyy-mm-dd
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}$')
then if((substring(sbsj,1,4) - substring(ywsj_update,1,4))* 12 + (substring(sbsj,6,2) - substring(ywsj_update,6,2) ) > 1,'滞后','正常')-- yyyy-mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}$')
then '格式不合规' -- yyyy
else '格式不合规' end
when ysjgxzq = '每季度' then
case when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*3,'滞后','正常') -- yyyy-mm-dd hh:mm:ss
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*3,'滞后','正常')-- yyyy-mm-dd hh:mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*3,'滞后','正常') -- yyyy-mm-dd
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}$')
then if((substring(sbsj,1,4) - substring(ywsj_update,1,4))* 12 + (substring(sbsj,6,2) - substring(ywsj_update,6,2) ) > -(31)*3,'滞后','正常')-- yyyy-mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}$')
then '格式不合规' -- yyyy
else '格式不合规' end
when ysjgxzq = '每半年' then
case when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*6,'滞后','正常') -- yyyy-mm-dd hh:mm:ss
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*6,'滞后','正常')-- yyyy-mm-dd hh:mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*6,'滞后','正常') -- yyyy-mm-dd
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}$')
then if((substring(sbsj,1,4) - substring(ywsj_update,1,4))* 12 + (substring(sbsj,6,2) - substring(ywsj_update,6,2) ) > -(31)*3,'滞后','正常')-- yyyy-mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}$')
then if(substr(sbsj,0,4) - ywsj_update > 1 ,'滞后','正常') -- yyyy
else '格式不合规' end
when ysjgxzq = '每年' then
case when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*12,'????','????') -- yyyy-mm-dd hh:mm:ss
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2} [0-9]{2}:[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*12,'滞后','正常')-- yyyy-mm-dd hh:mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')
then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*12,'滞后','正常') -- yyyy-mm-dd
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}$')
then if((substring(sbsj,1,4) - substring(ywsj_update,1,4))* 12 + (substring(sbsj,6,2) - substring(ywsj_update,6,2) ) > -(31)*3,'滞后','正常')-- yyyy-mm
when REGEXP_LIKE(ywsj_update, '^[0-9]{4}$')
then if(substr(sbsj,0,4) - ywsj_update > 1 ,'滞后','正常') -- yyyy
else '格式不合规' end
when ysjgxzq = '不定时' then
if(ywsj_update = '格式不合规',ywsj_update,'正常')
when ysjgxzq = '不更新' then
if(ywsj_update = '格式不合规',ywsj_update,'正常')
else '格式不合规' end check_result
