质量Q图关键SQL研究之对日期数据的处理实例

质量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.
(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐