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

需求描述

image

例如:产品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
posted @ 2018-05-16 17:47  OLIVER_QIN  阅读(559)  评论(0编辑  收藏  举报