高斯DWS数据库根据数据业务时间以及指标周期判断是否滞后

背景

在判断指标更新是否及时的过程中，需要根据指标的更新周期和更新时间判断指标更新是否及时，业务更新周期分别有每日、每小时、实时、每天、每月、每季度、每半年、每年、不定期、不更新等，代码中最细粒度只到天维度，小时级和分钟级不统计；

代码

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