Oracle SQL：基于时间差重置计数器并筛选指定记录

需求说明 #

需要在查询中新增计数器列，并筛选特定范围的数据：

• 计数器列规则：根据UnixDateTime_Diff（上一行UnixDateTime - 当前行UnixDateTime）判断取值：
  • 当UnixDateTime_Diff < -100时，计数器值为1
  • 当UnixDateTime_Diff > -100时，计数器值为0
• 最终仅保留最后一条计数器值为0的记录之后的所有数据

当前查询语句 #

with products as (
select * from (
select  distinct 
ID,
UnixDateTime,
OrderNumber,
to_date('1970-01-01','YYYY-MM-DD') + numtodsinterval(UnixDateTime,'SECOND')+ 1/24  as "Date_Time"
from DB
where
(date '1970-01-01' + UnixDateTime * interval '1' second) + interval '1' hour 
> sysdate - interval '2' day
)
),
prod_prev AS (
 SELECT p.*, 
        lag("Date_Time")over(order by "Date_Time" ASC) as Previous_Time,
        lag(UnixDateTime)over(order by "Date_Time" ASC) as UnixDateTime_Previous_Time, -- 原语句此处存在语法错误，已修正
        "Date_Time" - Previous_Time AS diff -- 原语句位置错误，已调整
   FROM products p    
), 
run_sum AS (
 SELECT p.*, "Date_Time"-Previous_Time as "Diff", UnixDateTime_Previous_Time-UnixDateTime AS "UnixDateTime_Diff"
   FROM prod_prev p    
)
SELECT * FROM run_sum
ORDER By UnixDateTime, "Date_Time" DESC

注：原prod_prev子查询存在语法错误，已在上述代码中修正

当前查询结果 #

解决方案 #

实现思路 #

1. 新增计数器列：用CASE表达式根据UnixDateTime_Diff计算取值，首行无前置数据时默认设为0
2. 定位分界点：找到最后一条计数器为0的记录的时间戳
3. 筛选目标数据：保留所有晚于该分界点的记录

完整SQL语句 #

WITH products AS (
    SELECT DISTINCT
        ID,
        UnixDateTime,
        OrderNumber,
        TO_DATE('1970-01-01', 'YYYY-MM-DD') + NUMTODSINTERVAL(UnixDateTime, 'SECOND') + 1/24 AS "Date_Time"
    FROM DB
    WHERE
        (DATE '1970-01-01' + UnixDateTime * INTERVAL '1' SECOND) + INTERVAL '1' HOUR 
        > SYSDATE - INTERVAL '2' DAY
),
prod_prev AS (
    SELECT 
        p.*,
        LAG("Date_Time") OVER(ORDER BY "Date_Time" ASC) AS Previous_Time,
        LAG(UnixDateTime) OVER(ORDER BY "Date_Time" ASC) AS UnixDateTime_Previous_Time
    FROM products p
),
run_sum AS (
    SELECT 
        p.*,
        "Date_Time" - Previous_Time AS "Diff",
        UnixDateTime_Previous_Time - UnixDateTime AS "UnixDateTime_Diff",
        -- 新增计数器列
        CASE
            WHEN UnixDateTime_Previous_Time - UnixDateTime < -100 THEN 1
            ELSE 0
        END AS counter
    FROM prod_prev p
),
-- 获取最后一条counter=0的记录的时间戳
last_zero AS (
    SELECT MAX(UnixDateTime) AS last_zero_unix
    FROM run_sum
    WHERE counter = 0
)
-- 筛选目标数据
SELECT rs.*
FROM run_sum rs
CROSS JOIN last_zero lz
WHERE rs.UnixDateTime > lz.last_zero_unix
ORDER BY rs.UnixDateTime, rs."Date_Time" DESC;

结果说明 #

针对示例数据，最后一条counter=0的是ID=3（UnixDateTime=1662615765），筛选后返回ID=4、5、6的记录：

若需包含最后一条counter=0的记录，将WHERE rs.UnixDateTime > lz.last_zero_unix改为>=即可

内容的提问来源于stack exchange，提问作者Jdbfkfo