-- 创建表
-- Create table
create table CCS2_DBA.T_SYF_20200410
(
name_id VARCHAR2(32),
monitor_name VARCHAR2(200),
operate_date DATE,
operate_type VARCHAR2(10)
);
-- Add comments to the table
comment on table CCS2_DBA.T_SYF_20200410
is '名单监控表';
-- Add comments to the columns
comment on column CCS2_DBA.T_SYF_20200410.name_id
is '名单编号';
comment on column CCS2_DBA.T_SYF_20200410.monitor_name
is '名单';
comment on column CCS2_DBA.T_SYF_20200410.operate_date
is '操作时间';
comment on column CCS2_DBA.T_SYF_20200410.operate_type
is '操作类型（1：开启监控，2：关闭监控）';
-- 准备数据
-- 正常情况
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('001','名单一',TO_DATE('20200101','YYYYMMDD'),'1');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('001','名单一',TO_DATE('20200111','YYYYMMDD'),'2');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('001','名单一',TO_DATE('20200121','YYYYMMDD'),'1');
-- 模拟重复关闭
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('002','名单二',TO_DATE('20200201','YYYYMMDD'),'1');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('002','名单二',TO_DATE('20200211','YYYYMMDD'),'1');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('002','名单二',TO_DATE('20200221','YYYYMMDD'),'2');
-- 模拟先关闭再开启
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('003','名单三',TO_DATE('20200301','YYYYMMDD'),'2');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('003','名单三',TO_DATE('20200311','YYYYMMDD'),'1');
-- 模拟重复开启
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('004','名单四',TO_DATE('20200401','YYYYMMDD'),'1');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('004','名单四',TO_DATE('20200411','YYYYMMDD'),'1');
INSERT INTO CCS2_DBA.T_SYF_20200410(NAME_ID, MONITOR_NAME, OPERATE_DATE, OPERATE_TYPE)VALUES('004','名单四',TO_DATE('20200421','YYYYMMDD'),'2');
COMMIT;
-- 查询语句
SELECT NAME_ID, MONITOR_NAME, MIN(ST) AS START_DT, ED_DT AS END_DT
FROM (SELECT MIN(A.NAME_ID) OVER(PARTITION BY MONITOR_NAME) AS NAME_ID,
A.MONITOR_NAME,
OPERATE_DATE,
CASE
WHEN OPERATE_TYPE = '1' THEN
OPERATE_DATE
ELSE
NULL
END AS ST,
NVL(MIN(CASE
WHEN OPERATE_TYPE = '2' THEN
OPERATE_DATE
ELSE
NULL
END)
OVER(PARTITION BY MONITOR_NAME ORDER BY OPERATE_DATE ROWS BETWEEN CURRENT
ROW AND UNBOUNDED FOLLOWING),
TO_DATE('30001231', 'YYYYMMDD')) AS ED_DT,
OPERATE_TYPE,
ROW_NUMBER() OVER(PARTITION BY MONITOR_NAME ORDER BY OPERATE_DATE ASC) AS RW
FROM CCS2_DBA.T_SYF_20200410 A)
WHERE ST IS NOT NULL
GROUP BY NAME_ID, ED_DT, MONITOR_NAME
ORDER BY 1,3;
--结果
