DB2 运用开窗函数统计数据
SELECT T3.FRAMENO AS FRAMENO , CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9' END AS MaintainGrade, CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/4M@' END AS MaintainMsg FROM (select distinct Max(t1.WalkDistance) over(partition by t1.FrameNo) as MaxWalkDistance,Min(t1.WalkDistance) over(partition by t1.FrameNo) as MinWalkDistance ,Count(t1.WalkDistance) over(partition by t1.FrameNo) as WalkDistanceCount ,t1.FrameNo from rt_repair t1 left join RT_RepairType t2 on t1.RepairNo = T2.RepairNo where T2.RepairTypeCode = '2' order by t1.FrameNo) AS T3
改进版本
SELECT T3.FRAMENO AS FRAMENO , CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9' END AS MaintainGrade, CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' END AS MaintainMsg FROM (select distinct Max(t1.WalkDistance) over(partition by t1.FrameNo) as MaxWalkDistance,Min(t1.WalkDistance) over(partition by t1.FrameNo) as MinWalkDistance ,Count(t1.WalkDistance) over(partition by t1.FrameNo) as WalkDistanceCount ,t1.FrameNo from rt_repair t1 left join RT_RepairType t2 on t1.RepairNo = T2.RepairNo where T2.RepairTypeCode = '2' AND DATE(T1.INPUTTIME) between '2010-1-1' and '2012-01-01' ) AS T3
数据统计
SELECT DISTINCT T2.ChangeType,COUNT(T2.CHANGETYPE) OVER(partition by T2.CHANGETYPE) FROM( SELECT DISTINCT T1.* FROM (SELECT D2.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 ON D2.FRAMENO=D1.FRAMENO UNION ALL SELECT D1.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 ON D2.FRAMENO=D1.FRAMENO) AS T1) AS T2
数据统计,增加增减标志
SELECT DISTINCT T2.ChangeType,COUNT(T2.CHANGETYPE) OVER(partition by T2.CHANGETYPE), T2.ADDFLAG FROM( SELECT DISTINCT T1.* FROM (SELECT D2.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType, CASE WHEN VALUE(D1.MAINTAINGRADE,'L0') > VALUE(D2.MAINTAINGRADE,'L0') THEN '0' WHEN VALUE(D1.MAINTAINGRADE,'L0') < VALUE(D2.MAINTAINGRADE,'L0') THEN '1'END AS ADDFLAG FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 ON D2.FRAMENO=D1.FRAMENO UNION ALL SELECT D1.FRAMENO,VALUE(D1.MAINTAINGRADE,'L0')||'->'||VALUE(D2.MAINTAINGRADE,'L0') AS ChangeType, CASE WHEN VALUE(D1.MAINTAINGRADE,'L0') > VALUE(D2.MAINTAINGRADE,'L0') THEN '0' WHEN VALUE(D1.MAINTAINGRADE,'L0') < VALUE(D2.MAINTAINGRADE,'L0') THEN '1'END AS ADDFLAG FROM (SELECT * FROM RT_MaintainSumDT WHERE ID=1) D1 LEFT JOIN (SELECT * FROM RT_MaintainSumDT WHERE ID=2) D2 ON D2.FRAMENO=D1.FRAMENO) AS T1) AS T2
数据统计,INSERT INTO
INSERT INTO RT_MaintainSumDT (ID,MONTHS,FRAMENO,MaintainGrade,MaintainMsg ) SELECT ID,MONTHS,FRAMENO,VALUE(MaintainGrade,'L1'),VALUE(MaintainMsg,'L1@@') FROM (SELECT 1 AS ID, 24 AS MONTHS ,T3.FRAMENO AS FRAMENO , CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9'END AS MaintainGrade, CASE WHEN T3.WalkDistanceCount = 1 THEN 'L1@@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) < 4999 THEN 'L4@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 5000 AND 5999 THEN 'L5@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 6000 AND 6999 THEN 'L6@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 7000 AND 7999 THEN 'L7@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) BETWEEN 8000 AND 8999 THEN 'L8@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@' WHEN 9000 < (T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1) THEN 'L9@'||RTRIM(CHAR((T3.MaxWalkDistance - T3.MinWalkDistance)/(T3.WalkDistanceCount - 1)))||'/'||RTRIM(CHAR(24/T3.WalkDistanceCount))||'M@'END AS MaintainMsg FROM (SELECT DISTINCT Max(T1.WalkDistance) over(partition by T1.FrameNo) as MaxWalkDistance,Min(T1.WalkDistance) over(partition by T1.FrameNo) as MinWalkDistance,Count(T1.WalkDistance) over(partition by T1.FrameNo) as WalkDistanceCount,T1.FrameNo FROM RT_REPAIR T1 LEFT JOIN RT_RepairType T2 on T1.RepairNo = T2.RepairNo WHERE T2.RepairTypeCode = '2' AND DATE(T1.INPUTTIME) BETWEEN '2010-09-18' AND '2012-09-18' ) AS T3 ) AS T4