数据清洗
方案一:
每半小时为一个基点,基点前最后一笔数据必保留,然后向下保留基点后最近两笔不重复数据。。
如图所示:示例数据将保留选中部分
CREATE OR REPLACE PROCEDURE CLEAR_CD_GUIDEWAY_DATARECORD(IN_DATE IN DATE) IS
TIME_INTERVAL NUMBER := 30; -- 清洗数据时间基准。单位: 分钟
CURSOR DATARECORD IS
SELECT T.*
FROM TEST_CD_GUIDEWAY_DATARECORD T
WHERE TRUNC(T.PRODATE) = TRUNC(IN_DATE)
ORDER BY T.MACHINEIP, T.PRODATE;
ITEM DATARECORD%ROWTYPE;
LAST_1_ITEM DATARECORD%ROWTYPE; -- 上一条记录
REFER_TIME DATE := TRUNC(IN_DATE, 'DD'); -- 参照时间
ICOUNT NUMBER := 0;
BEGIN
FOR ITEM IN DATARECORD LOOP
/*调整参照时间*/
REFER_TIME := TRUNC(ITEM.CREATEDATE, 'HH');
IF (ITEM.CREATEDATE - REFER_TIME) * 24 * 60 >= TIME_INTERVAL THEN
REFER_TIME := TRUNC(ITEM.CREATEDATE, 'HH') + TIME_INTERVAL / 24 / 60;
-- DBMS_OUTPUT.PUT_LINE(TO_CHAR(REFER_TIME, 'yyyy/mm/dd hh24:mi:ss'));
END IF;
IF LAST_1_ITEM.MACHINEIP = ITEM.MACHINEIP AND
LAST_1_ITEM.QUANTITY = ITEM.QUANTITY THEN
/* ★★★ 仅为演示:所以修改其 UPDATER 字段为 delete 意味着删除 ★★★ */
UPDATE TEST_CD_GUIDEWAY_DATARECORD T
SET T.UPDATER = 'delete'
WHERE T.SYSID = ITEM.SYSID;
ELSE
IF LAST_1_ITEM.MACHINEIP IS NULL OR
LAST_1_ITEM.CREATEDATE < REFER_TIME THEN
/* 基准数据 */
DBMS_OUTPUT.PUT_LINE(ITEM.SYSID ||
TO_CHAR(ITEM.CREATEDATE,
'yyyy/mm/dd hh24:mi:ss'));
ICOUNT := 0;
ELSIF ICOUNT > 2 THEN
UPDATE TEST_CD_GUIDEWAY_DATARECORD T
SET T.UPDATER = 'delete'
WHERE T.SYSID = LAST_1_ITEM.SYSID;
END IF;
ICOUNT := ICOUNT + 1;
LAST_1_ITEM := ITEM;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END CLEAR_CD_GUIDEWAY_DATARECORD;
运行结果:
exec CLEAR_CD_GUIDEWAY_DATARECORD(TO_DATE('20190916', 'yyyymmdd'));
方案二:
每半小时为一个基点,基点数据必保留,然后向上、向下保留基点与前后最近的一笔不重复数据。最开始和最结束的数据保留。
如图所示:示例数据将保留选中部分
CREATE OR REPLACE PROCEDURE CLEAR_CD_GUIDEWAY_DATARECORD(IN_DATE IN DATE) IS
CURSOR BASE_DATE_RECORD IS
SELECT P.*
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY T.MACHINEIP, TRUNC(T.PRODATE, 'hh24'), SIGN(T.PRODATE - TRUNC(T.PRODATE, 'hh24') - 30 / 60 / 24) ORDER BY T.CREATEDATE) NO,
DECODE(SIGN(T.PRODATE - TRUNC(T.PRODATE, 'hh24') -
30 / 60 / 24),
-1,
TRUNC(T.PRODATE, 'hh24'),
TRUNC(T.PRODATE, 'hh24') + 30 / 60 / 24) BDATE
FROM CD_GUIDEWAY_DATARECORD T
WHERE T.PRODATE BETWEEN TRUNC(IN_DATE) AND TRUNC(IN_DATE + 1) + 30 / 60 / 24) P
WHERE P.NO = 1;
BASE_DATE BASE_DATE_RECORD%ROWTYPE;
BEGIN
FOR BASE_DATE IN BASE_DATE_RECORD LOOP
/**/
DBMS_OUTPUT.PUT_LINE(TO_CHAR(BASE_DATE.BDATE, 'yyyy/dd/mm hh24:mi:ss'));
/**/
UPDATE CD_GUIDEWAY_DATARECORD T
SET T.UPDATER = 'delete'
/*DELETE FROM CD_GUIDEWAY_DATARECORD T*/
WHERE T.SYSID <> BASE_DATE.SYSID
AND TRUNC(T.PRODATE) = TRUNC(IN_DATE)
AND T.PRODATE BETWEEN BASE_DATE.BDATE - 15 / 60 / 24 AND
BASE_DATE.BDATE + 15 / 60 / 24
AND T.MACHINEIP = BASE_DATE.MACHINEIP
AND T.SYSID NOT IN
(SELECT P.SYSID
FROM (SELECT T.*,
ROW_NUMBER() OVER(PARTITION BY SIGN(T.PRODATE - TRUNC(T.PRODATE, 'hh24') - 30 / 60 / 24) ORDER BY ABS(T.PRODATE - BASE_DATE.PRODATE)) NO
FROM CD_GUIDEWAY_DATARECORD T
WHERE T.MACHINEIP = BASE_DATE.MACHINEIP
AND T.PRODATE BETWEEN BASE_DATE.BDATE - 15 / 60 / 24 AND
BASE_DATE.BDATE + 15 / 60 / 24
AND T.QUANTITY <> BASE_DATE.QUANTITY
ORDER BY T.PRODATE DESC) P
WHERE P.NO <= 1);
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END CLEAR_CD_GUIDEWAY_DATARECORD;
运行结果:
exec CLEAR_CD_GUIDEWAY_DATARECORD(TO_DATE('20190916', 'yyyymmdd'));