如何按Broker分组计算当前行CLOSE_TRADE与下一行OPEN_TRADE的时间差？

需求说明 #

• 涉及三列数据：OPEN_TRADE（开仓日期，数据按此列排序）、CLOSE_TRADE（平仓日期）、BROKER（经纪商）
• 计算同一经纪商下，当前交易的CLOSE_TRADE与下一笔交易的OPEN_TRADE的时间间隔（单位：秒）
• 仅在同一BROKER分组内计算，每组最后一笔交易的间隔为NULL

示例数据 #

期望结果 #

当前代码及问题 #

当前查询语句 #

with dummy as 
(
SELECT 1 AS ID, DATE '2024-12-11 04:45:07.000' as OPEN_TRADE, DATE '2024-12-11 04:45:08.000' as CLOSE_TRADE, 'BINANCE' AS BROKER from dual union all
select 2, DATE '2024-12-11 04:45:09.000', DATE '2024-12-11 04:45:27.000', 'BINANCE' from dual union all
select 3, DATE '2024-12-11 04:50:07.000', DATE '2024-12-11 04:50:52.000', 'BINANCE' from dual union all
select 4, DATE '2024-12-11 04:50:07.000', DATE '2024-12-11 04:50:32.000', 'COINBASE' from dual union all
select 5, DATE '2024-12-11 04:55:06.000', DATE '2024-12-11 04:55:08.000', 'COINBASE' from dual union all
select 6, DATE '2024-12-11 04:55:07.000', DATE '2024-12-11 04:55:25.000', 'BITGET' from dual union all
select 7, DATE '2024-12-11 05:05:07.000', DATE '2024-12-11 05:05:14.000', 'BITGET' from dual union ALL
select 8, DATE '2024-12-11 05:05:32.000', DATE '2024-12-11 05:06:01.000', 'BINANCE' from dual 
)
SELECT extract(SECOND FROM OPEN_TRADE - CLOSE_TRADE), 'fm00.000' AS TIME_LAPS -- TIME_BETWEEN_2_TRADES
--MAX(SECOND FROM OPEN_TRADE - CLOSE_TRADE)
--  OVER (ORDER BY OPEN_TRADE ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) max 
FROM dummy
ORDER BY OPEN_TRADE;

遇到的问题 #

1. Oracle中使用DATE '2024-12-11 04:55:06.000'创建带时分秒的日期数据报错，因为Oracle的DATE字面量仅支持YYYY-MM-DD格式，无法直接包含时分秒。
2. 无法实现按BROKER分组，关联当前行与同组下一行计算时间间隔的逻辑。

解决方案 #

1. 修复示例数据的日期格式 #

Oracle中创建带时分秒的日期需使用TO_DATE函数指定格式，修正后的CTE如下：

with dummy as 
(
SELECT 1 AS ID, TO_DATE('2024-12-11 04:45:07', 'YYYY-MM-DD HH24:MI:SS') as OPEN_TRADE, TO_DATE('2024-12-11 04:45:08', 'YYYY-MM-DD HH24:MI:SS') as CLOSE_TRADE, 'BINANCE' AS BROKER from dual union all
select 2, TO_DATE('2024-12-11 04:45:09', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:45:27', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual union all
select 3, TO_DATE('2024-12-11 04:50:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:50:52', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual union all
select 4, TO_DATE('2024-12-11 04:50:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:50:32', 'YYYY-MM-DD HH24:MI:SS'), 'COINBASE' from dual union all
select 5, TO_DATE('2024-12-11 04:55:06', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:55:08', 'YYYY-MM-DD HH24:MI:SS'), 'COINBASE' from dual union all
select 6, TO_DATE('2024-12-11 04:55:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:55:25', 'YYYY-MM-DD HH24:MI:SS'), 'BITGET' from dual union all
select 7, TO_DATE('2024-12-11 05:05:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 05:05:14', 'YYYY-MM-DD HH24:MI:SS'), 'BITGET' from dual union ALL
select 8, TO_DATE('2024-12-11 05:05:32', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 05:06:01', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual 
)

2. 使用LEAD分析函数实现分组关联下一行 #

利用LEAD分析函数按BROKER分区、OPEN_TRADE排序，获取同组下一行的OPEN_TRADE，再计算时间差（日期相减结果为天数，乘以86400转换为秒）：

完整查询语句：

with dummy as 
(
SELECT 1 AS ID, TO_DATE('2024-12-11 04:45:07', 'YYYY-MM-DD HH24:MI:SS') as OPEN_TRADE, TO_DATE('2024-12-11 04:45:08', 'YYYY-MM-DD HH24:MI:SS') as CLOSE_TRADE, 'BINANCE' AS BROKER from dual union all
select 2, TO_DATE('2024-12-11 04:45:09', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:45:27', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual union all
select 3, TO_DATE('2024-12-11 04:50:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:50:52', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual union all
select 4, TO_DATE('2024-12-11 04:50:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:50:32', 'YYYY-MM-DD HH24:MI:SS'), 'COINBASE' from dual union all
select 5, TO_DATE('2024-12-11 04:55:06', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:55:08', 'YYYY-MM-DD HH24:MI:SS'), 'COINBASE' from dual union all
select 6, TO_DATE('2024-12-11 04:55:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 04:55:25', 'YYYY-MM-DD HH24:MI:SS'), 'BITGET' from dual union all
select 7, TO_DATE('2024-12-11 05:05:07', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 05:05:14', 'YYYY-MM-DD HH24:MI:SS'), 'BITGET' from dual union ALL
select 8, TO_DATE('2024-12-11 05:05:32', 'YYYY-MM-DD HH24:MI:SS'), TO_DATE('2024-12-11 05:06:01', 'YYYY-MM-DD HH24:MI:SS'), 'BINANCE' from dual 
)
SELECT 
    ID,
    CASE 
        WHEN next_open IS NOT NULL THEN ROUND((next_open - CLOSE_TRADE) * 86400) 
        ELSE NULL 
    END AS TIME_LAPS,
    BROKER
FROM (
    SELECT 
        ID,
        OPEN_TRADE,
        CLOSE_TRADE,
        BROKER,
        LEAD(OPEN_TRADE) OVER (PARTITION BY BROKER ORDER BY OPEN_TRADE) AS next_open
    FROM dummy
)
ORDER BY OPEN_TRADE;

关键逻辑说明 #

• LEAD(OPEN_TRADE) OVER (PARTITION BY BROKER ORDER BY OPEN_TRADE)：按BROKER分组，每组内按OPEN_TRADE排序，获取当前行的下一行OPEN_TRADE，最后一行无下一行则返回NULL。
• (next_open - CLOSE_TRADE) * 86400：日期相减得到天数，乘以86400转换为秒数，ROUND用于取整。

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