2-看板重构

需求：

1.产品实际产能取工艺路线第一个工序的产能数据（多个工单数量相加）。

2.班次固定白夜班，08:30~20:30白班，其他时间夜班

3.效率未达到95%需显示红色，功能直通率/外观直通率未到98%需显示红色

1.本次SQL学习

1.1 case字句

用来根据条件判断加入一个标记列
end和as结尾

样例：

case  
    WHEN to_char(msf.end_time, 'HH24:MI:SS') < '20:30:00' then '白班'  
    else '晚班'  
end as DAYTIME

1.2 with as字句

把一个查询语句当成一个子表用来供后面的查询
with as后要接上select语句，不然会报错

//单个命名  
with as T(  
    select * from a  
)select * from T  
//多个  
with as T(),  
as A(),  
as B()  
select * from A,B,T;

1.3 Group by

结合聚集函数使用
假设要聚合A字段，并且select中包含的是A，B，C，D字段，除了A字段，B和C都要加入到Group by后
先where后Group by 后Order by
Group by后可以加Having条件限制分组

SELECT  
        T.START_TIME as START_TIME,  
        T.END_TIME as END_TIME,  
        T.DAYTIME as DAYTIME,  
        SUM(T.ALL_COUNT) as ALL_COUNT  
    FROM T  
        GROUP BY  
            T.START_TIME,  
            T.END_TIME,  
            T.DAYTIME  
        ORDER BY T.START_TIME

1.4 ROW_NUMBER（）

按照条件对数据进行分组，内部会排序，并且按照1,2,3这样的顺序排序

ROW_NUMBER() OVER (PARTITION BY B.START_TIME, B.END_TIME ORDER BY B.CREATEDDATE DESC) AS rn
ROW_NUMBER() OVER (PARTITION BY 分组的列(或者数据) ORDER BY 排序的列 DESC) AS 命名
//同时可以结合SUM函数
SUM(列) OVER (partition BY 分组的列 ORDER BY 排序的列) as 命名

1.5 连接

外连接有左外和右外，外连接就是算上自己的和别人共有的
left join和right join后面的on接条件，可以加and
内连接join on，类似求交集

1.6 按行分组求和

SUM(P.ALL_COUNT) OVER (ORDER BY P.START_TIME) AS SUM_COUNT

这里还可以结合使用PARTITION BY分组求和。

1.7 字符串拼接

本次遇到的主要问题就是保留小数点和百分号拼接的问题，使用如下函数：

TO_CHAR((M.P_PASS_COUNT) / (M.P_ALL_COUNT)*100, 'FM9999990.00') || '%' AS P_RATE

即可以百分比形式保存并保留小数点后两位

2.优化后的SQL

with T as (
    select
       S.site as SITE,
       S.shop_order as SHOP_ORDER,
       split(S.planned_item_bo,2) ITEM,
       MSF.start_time as START_TIME,
       MSF.end_time as END_TIME,
       IT.description as DESCRIPTION,
       MSF.all_count as ALL_COUNT,
       MSF.pass_count as PASS_COUNT,
       MSF.OPERATION as OPERATION,
       MSF.CREATEDDATE as CREATEDDATE,
       MSF.LINE as LINE,
           MSF.CAUSE as CAUSE,
           MSF.PROCESS as PROCESS,
       ROW_NUMBER() OVER (PARTITION BY  MSF.START_TIME, MSF.END_TIME ORDER BY MSF.START_TIME DESC) AS RN
  from (SELECT SHOP_ORDER from MOM_SFC_INDUCTION WHERE TRUNC(SYSDATE) = TRUNC(START_TIME) GROUP BY SHOP_ORDER) A
  join SHOP_ORDER S on S.SHOP_ORDER = A.SHOP_ORDER
  join item_t IT on IT.item_bo = S.planned_item_bo and IT.locale = 'zh'
  join MOM_SFC_INDUCTION MSF on MSF.SHOP_ORDER = S.SHOP_ORDER
  JOIN router_step R ON S.planned_router_bo = R.router_bo AND R.SEQUENCE = 1
  JOIN router_operation RO ON R.handle = RO.router_step_bo AND MSF.OPERATION = split(RO.operation_bo,2)
  where S.site = '1001'  and MSF.line = 'HA5D003' and TRUNC(SYSDATE) = TRUNC(MSF.START_TIME) order by MSF.CREATEDDATE DESC
),
P as (
        SELECT
            T.START_TIME as START_TIME,
            T.END_TIME as END_TIME,
            SUM(T.ALL_COUNT) as ALL_COUNT,
            SUM(T.PASS_COUNT) as PASS_COUNT,
            WM_CONCAT(T.CAUSE) as CAUSE
        FROM T
            GROUP BY
                T.START_TIME,
                T.END_TIME
            ORDER BY T.START_TIME),
M as (
    SELECT
    T.START_TIME AS START_TIME,
    T.END_TIME AS END_TIME,
    SUM(CASE WHEN T.OPERATION LIKE '%QCD%' OR T.OPERATION LIKE '%AOI%' THEN T.ALL_COUNT ELSE 0 END) AS P_ALL_COUNT,
    SUM(CASE WHEN T.OPERATION LIKE '%QCD%' OR T.OPERATION LIKE '%AOI%' THEN T.PASS_COUNT ELSE 0 END) AS P_PASS_COUNT,
    SUM(CASE WHEN T.OPERATION LIKE'%ICT%' OR T.OPERATION LIKE '%FCT%'  OR T.OPERATION LIKE '%BUR%' THEN T.PASS_COUNT ELSE 0 END) AS F_PASS_COUNT,
    SUM(CASE WHEN T.OPERATION LIKE'%ICT%' OR T.OPERATION LIKE '%FCT%'  OR T.OPERATION LIKE '%BUR%' THEN T.ALL_COUNT ELSE 0 END) AS F_ALL_COUNT
    FROM
        MOM_SFC_INDUCTION T
    WHERE   TRUNC(T.START_TIME) = TRUNC(SYSDATE)
    GROUP BY
        T.START_TIME,
        T.END_TIME
    ORDER BY
        T.START_TIME
)
SELECT
    T.SHOP_ORDER,
    T.ITEM,
    T.LINE,
    T.PROCESS,
    TO_CHAR(P.START_TIME, 'HH24:MI') || '~' || TO_CHAR(P.END_TIME, 'HH24:MI') AS TIME_RANGE,
    CASE
        WHEN TO_CHAR(P.END_TIME, 'HH24:MI:SS') < '20:30:00' AND '08:15:00' < TO_CHAR(P.END_TIME, 'HH24:MI:SS') THEN '白班'
        ELSE '晚班'
    END AS SCHEDULE,
    T.DESCRIPTION,
    T.OPERATION,
        P.ALL_COUNT,
        SUM(P.ALL_COUNT) OVER (ORDER BY P.START_TIME) AS SUM_COUNT,
    TO_CHAR((M.F_PASS_COUNT) / (M.F_ALL_COUNT)*100, 'FM9999990.00') || '%' AS F_RATE,
    TO_CHAR((M.P_PASS_COUNT) / (M.P_ALL_COUNT)*100, 'FM9999990.00') || '%' AS P_RATE,
    P.CAUSE
FROM
    P
JOIN
    T ON P.START_TIME = T.START_TIME AND P.END_TIME = T.END_TIME
JOIN
    M ON P.START_TIME = M.START_TIME AND P.END_TIME = M.END_TIME
WHERE
    T.RN = 1 
    AND (
        (TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') BETWEEN '08:15:00' AND '20:30:00' AND TO_CHAR(P.END_TIME, 'HH24:MI:SS') < '20:30:00' AND '08:15:00' < TO_CHAR(P.END_TIME, 'HH24:MI:SS')) 
        OR
        (TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS') NOT BETWEEN '08:15:00' AND '20:30:00' AND (TO_CHAR(P.END_TIME, 'HH24:MI:SS') >= '20:30:00' OR '08:15:00' >= TO_CHAR(P.END_TIME, 'HH24:MI:SS'))) 
    );

主要业务逻辑：

先按日期查询今天的一个工单号情况
拿到工单号连接工序表查找首工序(此步反转可以节省很大的查询时间)
对数据底表进行拼接分组，按照时间分组出来并且计算首工序的一个产能
再次对数据底表进行拼接分组，按照时间分组设置条件遴选外观通过率和功能通过率的指标数据，进行累加

3.接口设计

3.1数据类型设计

一开始对项目的理解有误，写好SQL后的主要任务是：

将SQL封到ServiceImpl类中，能够使用Service来查询到数据库数据
写Controller接口，要求如下：
- 使用一个常量常驻缓存，用来保存从SAP接口中请求来的标准产能和标准人数
- SAP接口传参要求[工单号，工序]，Controller接口传参要求[站点，线别]
- 要求这个常量缓存不重复保存数据，只在存在差异的时候进行更新
- 根据SAP接口查找到的标准产能来计算效率，并且返回到接口的List中

4.上线测试手记

接口完成后需要发布上线后进行测试，此次记录具体步骤：

将本地开发分支合并dev，发布dev
到Jenkins中发布测试机
Jenkins中可以看到对应的分支配置文件
启动后可以到eureka中查看网关，对应在yml配置文件中
走网关到application中
eureka会把几个服务器管理起来，访问服务的时候走eureka的网关去访问，同时也可以通过网关内部访问其他应用的服务，微服务
走url的时候，网关+服务名+接口
如果出现token不被认证的错误，需要去对应网关下的BASE中开启白名单，开白名单使用部分匹配即可，类似LIKE子句
另外一种token认证的方法也可以在浏览器中获取到相应的Authorization,测试接口的时候使用Authorization方式即可

1.本次SQL学习​

1.1 case字句​

1.2 with as字句​

1.3 Group by​

1.4 ROW_NUMBER（）​

1.5 连接​

1.6 按行分组求和​

1.7 字符串拼接​

2.优化后的SQL​

3.接口设计​

3.1数据类型设计​

4.上线测试手记​