WITH shift_data AS (
SELECT
TO_DATE(STARTTIME, 'YYYYMMDD HH24MISS') AS start_dt,
TO_DATE(ENDTIME, 'YYYYMMDD HH24MISS') AS end_dt
FROM SDB_TB_DATE_SHIFT
WHERE
REGEXP_LIKE(STARTTIME, '^\d{8} \d{6}$')
AND REGEXP_LIKE(ENDTIME, '^\d{8} \d{6}$')
),
filtered_log AS (
SELECT
a.equipmentid,
TO_DATE(SUBSTR(a.updatetime, 1, 15), 'YYYYMMDD HH24MISS') AS dt,
a.alarmtext,
ROW_NUMBER() OVER (PARTITION BY a.equipmentid ORDER BY a.updatetime) AS rn
FROM
DR01.sdb_tb_alarm_log_table a
WHERE
a.updatetime IS NOT NULL
AND LENGTH(a.updatetime) >= 15
AND REGEXP_LIKE(SUBSTR(a.updatetime, 1, 15), '^\d{8} \d{6}$')
AND a.equipmentid IN ('EDPTC01', 'EDPTC05', 'EDPTC06', 'FMPTC81', 'FDPTC01')
AND (a.alarmtext IS NULL OR a.alarmtext != 'Eqp1 Port1 change to Local.')
AND EXISTS (
SELECT 1
FROM shift_data b
WHERE TO_DATE(SUBSTR(a.updatetime, 1, 15), 'YYYYMMDD HH24MISS')
BETWEEN b.start_dt AND b.end_dt
)
),
lagged_log AS (
SELECT
equipmentid,
dt,
alarmtext,
rn,
LAG(dt) OVER (PARTITION BY equipmentid ORDER BY dt, rn) AS prev_dt
FROM
filtered_log
),
grouped_log AS (
SELECT
equipmentid,
dt,
alarmtext,
rn,
SUM(CASE
WHEN (dt - prev_dt) * 24 * 60 > 30 THEN 1
ELSE 0
END) OVER (PARTITION BY equipmentid ORDER BY dt, rn) AS grp
FROM
lagged_log
),
group_summary AS (
SELECT
equipmentid,
dt AS start_dt,
alarmtext,
grp,
COUNT(*) OVER (PARTITION BY equipmentid, grp) AS group_count,
MAX(dt) OVER (PARTITION BY equipmentid, grp) AS end_dt,
MIN(dt) OVER (PARTition BY equipmentid, grp) AS min_dt,
CASE
WHEN COUNT(*) OVER (PARTITION BY equipmentid, grp) > 1
THEN ROUND((MAX(dt) OVER (PARTITION BY equipmentid, grp) - MIN(dt) OVER (PARTITION BY equipmentid, grp)) * 24 * 60, 2)
ELSE 10
END AS time,
ROW_NUMBER() OVER (PARTITION BY equipmentid, grp ORDER BY dt, rn) AS group_rn
FROM
grouped_log
)
SELECT
equipmentid,
TO_CHAR(start_dt, 'YYYYMMDD HH24MISS') AS starttime,
CASE
WHEN group_count > 1 THEN TO_CHAR(end_dt, 'YYYYMMDD HH24MISS')
ELSE NULL
END AS endtime,
alarmtext,
time,
TO_CHAR(start_dt, 'YYYYMMDD') AS alarm_date
FROM
group_summary
WHERE
group_rn = 1
ORDER BY
equipmentid, start_dt;
怎么检索出来没有结果，但是不可能没有结果，一定有结果
本文详细介绍了Oracle数据库中REGEXP_SUBSTR函数的使用方法，包括其语法、参数说明及实例应用，展示了如何通过正则表达式从字符串中提取特定模式的数据。
扫一扫
1910
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
