【DB2】判断连续时间，如果间断则新增一条记录

需求描述

例如：产品A01 2017-01-02到2017-01-03产品状态都是差，那么就是一条记录 但是在2017-01-04这天的状态不是差，到了5日这天又是差了 就是另外一条记录了

需求处理

DROP TABLE tb;
CREATE TABLE tb(product VARCHAR(100),dt TIMESTAMP,status VARCHAR(100));

INSERT INTO tb
SELECT 'A01','2017-1-1','优秀' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A01','2017-1-2','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A01','2017-1-3','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A01','2017-1-4','中等' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A01','2017-1-5','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A01','2017-1-6','中等' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A02','2017-1-6','中等' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A02','2017-1-7','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A03','2017-1-6','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A03','2017-1-7','差' FROM SYSIBM.DUAL
UNION ALL
SELECT 'A03','2017-1-9','差' FROM SYSIBM.DUAL
-------

SELECT PRODUCT,MIN(DT) AS START_DT,MAX(DT) AS END_DATE
FROM (SELECT A.*,ROW_NUMBER() OVER(ORDER BY PRODUCT,DT) RN FROM tb A WHERE status='差')
GROUP BY PRODUCT,DAYS(DT)-RN
ORDER BY PRODUCT

转载于:https://www.cnblogs.com/OliverQin/p/9047220.html