本文主要是介绍高斯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:sswhen 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:mmwhen REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')then if(timestampdiff(day,sbsj, ywsj_update) < -1,'滞后','正常') -- yyyy-mm-ddwhen 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:sswhen 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:mmwhen REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')then if(timestampdiff(day,sbsj, ywsj_update) < -31,'滞后','正常') -- yyyy-mm-ddwhen 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:sswhen 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:mmwhen REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*3,'滞后','正常') -- yyyy-mm-ddwhen 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:sswhen 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:mmwhen REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*6,'滞后','正常') -- yyyy-mm-ddwhen 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:sswhen 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:mmwhen REGEXP_LIKE(ywsj_update, '^[0-9]{4}-[0-9]{2}-[0-9]{2}$')then if(timestampdiff(day,sbsj, ywsj_update) < -(31)*12,'滞后','正常') -- yyyy-mm-ddwhen 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这篇关于高斯DWS数据库根据数据业务时间以及指标周期判断是否滞后的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
