质量Q图关键SQL研究之对日期数据的处理实例
--背景:帮功力不足的同事做点基础研究 --快四年没有写代码了,受限于以往知识和经验,上述实现可能不是最优,但是能够满足业务需要-- --前端UI:一个DatePicker,查询指定年月的质量Q图;一个日历控件,基于FullCalendar实现 --质量Q图主要是基于PCR看板数据,对日历Cell着色: ----绿色:历史日期、当天未发生任务质量问题 ----黄色:历史日期、当天发生内部或者供应商质量问题 ----红色:历史日期、当天发生顾客质量问题;红色优先级高于黄色、黄色高于绿色 --查询条件作为参数,通过datepicker值处理后(yyyymm)传入sql,比较对象为“质量问题发生日期” select distinct replace(t.PUB_DATE, '-') from T_PCR t where replace(substr(t.PUB_DATE, 1, 7), '-') ='201711'; --数据源:快反看板问题,即PCR看板 --建立测试表 create table qsb_data_test01 ( occur_date varchar2(10), prob_type varchar2(10), prob_desc varchar2(100),--测试不需要 grade varchar2(10)--测试不需要 ) --本地测试数据准备 insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180101', '顾客', '脱焊', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180102', '内部', '虚焊', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180103', '供应商', '冲孔小', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180104', '顾客', '冲孔大', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180104', '内部', '开裂', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20180106', '供应商', '包装问题', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20171231', '顾客', '脱焊', ''); insert into qsb_data_test01 (OCCUR_DATE, PROB_TYPE, PROB_DESC, GRADE) values ('20171230', '供应商', '冲孔小', ''); select * from qsb_data_test01 t; --基础回顾:本月第1天 select to_char(trunc(sysdate, 'mm'),'yyyymmdd') from dual; --基础回顾:今天 select to_char(sysdate,'yyyymmdd') from dual; --关于着色对象(日期)的处理思路 --如果DatePicker选择了上个月怎么着色?需要对比选取月份是否等于本月;如果等于,则应考虑截止当天为止; --否则,则需对全月各天着色。 --或者统一为:日期不大于今天,并且月份等于DatePicker中指定月份【此为建议方案】 --从本月第一天,到今天,期间日期作为着色对象;结果集只体现了有问题发生的日期,没有问题发生的日期(绿色)没有体现 select * from qsb_data_test01 t where substr(t.occur_date, 1, 6) ='201801' and t.occur_date<=to_char(sysdate,'yyyymmdd'); --根据查询结果,定义问题级别(假设顾客问题-3,内部/供应商问题-2) select t.occur_date, t.prob_type, (case t.prob_type when '顾客' then '3' when '内部' then '2' when '供应商' then '2' end) as 级别 from qsb_data_test01 t; --当天有重复问题的,只检查问题级别最高的用于着色 select t.occur_date as 问题发生日期, max(case t.prob_type when '顾客' then '3' when '内部' then '2' when '供应商' then '2' end) as 级别 from qsb_data_test01 t group by t.occur_date order by t.occur_date; --当天有重复问题的,只检查问题级别最高的用于着色;仍然是对于有问题发生的日期 select cc.occur_date, (case cc.grade when '3' then 'red' when '2' then 'yello' end) q_color from (select t.occur_date, max(case t.prob_type when '顾客' then '3' when '内部' then '2' when '供应商' then '2' end) as grade from qsb_data_test01 t where substr(t.occur_date, 1, 6) = '201712' and t.occur_date <= to_char(sysdate, 'yyyymmdd') --日期条件设置 group by t.occur_date order by t.occur_date) cc --上面完成对问题日期的着色数据准备 --接下来,需要单独写一个函数(及其SQL):在处理前面的问题日期着色以后,找到没有发生质量问题的全部日期,统一着色为绿色 --除上述思路以外,还有第二种思路: ----1.结合查询条件,找到符合条件的日期区间,即:月份==查询条件,日期<=今天 ----2.对日期区间内的每一天统一着色为绿色 ----3.对日期区间内,发生内部、供应商问题的,统一着色为黄色(可能覆盖原来绿色) ----4.对日期区间内,发生顾客问题的,统一着色为红色(可能覆盖原来黄色) ----5.相当于对于FullCalendar,应准备三个Events数据源,需要考虑覆盖效果 --关于无问题发生日期的查询和数据准备 --基础研究:Oracle 取某日期期间内每一天的日期 SELECT TO_DATE('2017-01-01', 'yyyy-MM-dd') + ROWNUM - 1 as daylists FROM DUAL CONNECT BY ROWNUM <= trunc( to_date('2017-12-31', 'yyyy-MM-dd') - --结束日期 to_date('2017-01-01', 'yyyy-MM-dd') --开始日期 ) + 1; --基础研究:今天之前(包含今天)X天,参数设置为X=60,则返回60行记录 SELECT trunc(sysdate, 'dd') + ROWNUM - 60 as daylists FROM DUAL CONNECT BY ROWNUM <= trunc(trunc(sysdate, 'dd') - (trunc(sysdate, 'dd') - 60)); --结合上面的测试表,查询一定日期期间内,没有发生质量问题的日期 select t1.daylists from (SELECT to_char(trunc(sysdate, 'dd') + ROWNUM - 30, 'yyyymmdd') as daylists FROM DUAL CONNECT BY ROWNUM <= trunc(trunc(sysdate, 'dd') - (trunc(sysdate, 'dd') - 30))) t1 where t1.daylists not in (select t.occur_date from qsb_data_test01 t) order by t1.daylists; --效率优化:用not exist代替 not in select t1.daylists from (SELECT to_char(trunc(sysdate, 'dd') + ROWNUM - 30, 'yyyymmdd') as daylists FROM DUAL CONNECT BY ROWNUM <= trunc(trunc(sysdate, 'dd') - (trunc(sysdate, 'dd') - 30))) t1 where not exists (select t.occur_date from qsb_data_test01 t where t.occur_date = t1.daylists) order by t1.daylists; --------------------------------END.