ORACLE 取连续值、时间段的区间

主要是通过当前行，和行号做减法操作，若是连续的值，此列新生成的值将一致

---

取连续值区间##

--测试数据
CREATE TABLE Z_NUMS AS 
SELECT LEVEL AS NUM1 FROM DUAL CONNECT BY LEVEL <=1000;
DELETE FROM Z_NUMS WHERE NUM1 LIKE '%7%';
COMMIT;

SELECT MIN(BIZ_DATE) AS BIZ_DATE1,
       MAX(BIZ_DATE) AS BIZ_DATE2,
       COUNT(*) AS FNUM
  FROM (SELECT BIZ_DATE,
               TO_DATE(BIZ_DATE, 'yyyymmdd') - ROW_NUMBER() OVER(ORDER BY BIZ_DATE) AS BIZ_DATE0
          FROM Z_DAYS) A
 GROUP BY BIZ_DATE0
 ORDER BY 1

---

取连续时间区间

SELECT MIN(BIZ_DATE) AS BIZ_DATE1,
       MAX(BIZ_DATE) AS BIZ_DATE2,
       COUNT(*) AS FNUM
  FROM (SELECT BIZ_DATE,
               TO_DATE(BIZ_DATE, 'yyyymmdd') - ROW_NUMBER() OVER(ORDER BY BIZ_DATE) AS BIZ_DATE0
          FROM Z_DAYS) A
 GROUP BY BIZ_DATE0
 ORDER BY 1