时间戳

首页 > 解决方案 > Oracle中流数据的开始和结束时间

sql - Oracle中流数据的开始和结束时间

问题描述

我是 SQL 新手，需要您的帮助。我在 oracle 数据库中有一个表，其中包含如下流数据：

| time              | code | n1   | n2    | link    |
| 10.11.20 09:41:00 | abc  | 1.02 | 2.45  | abc/1   |
| 10.11.20 09:42:00 | abc  | 1.03 | 2.44  | abc/2   |
| 10.11.20 09:43:00 | abc  | 1.04 | 2.43  | abc/3   |
| 10.11.20 11:14:00 | abc  | 5.45 | 10.24 | abc/1_1 |
| 10.11.20 11:15:00 | abc  | 5.46 | 10.23 | abc/2_2 |
| 10.11.20 11:16:00 | abc  | 5.47 | 10.22 | abc/3_3 |
| 11.11.20 21:09:00 | def  | 3.55 | 6.98  | def/1   |
| 11.11.20 21:10:00 | def  | 3.56 | 6.97  | def/2   |
| 11.11.20 21:11:00 | def  | 3.57 | 6.96  | def/3   |

我需要从每个时间差为一分钟的代码中获取开始时间、开始 n1、开始 n2、开始链接、结束时间、结束 n1、结束 n2 和结束链接。像这样的东西：

| start_time        | end_time          | start_n1 | start_n2 | end_n1 | end_n2 | start_link | end_link  | code |
| 10.11.20 09:41:00 | 10.11.20 09:43:00 | 1.02     | 2.45     | 1.04   | 2.43   | abc/1      | abc/3     | abc  |
| 10.11.20 11:14:00 | 10.11.20 11:16:00 | 5.45     | 10.24    | 5.47   | 10.22  | abc/1_1    | abc/3_3   | abc  |
| 11.11.20 21:09:00 | 10.11.20 21:11:00 | 3.55     | 6.98     | 3.57   | 6.96   | def/1      | def/3     | def  |

我怎样才能做到这一点？？

非常感谢您！

标签： sqloracle

解决方案

您可以使用MATCH_RECOGNIZE来执行逐行比较：

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY code
  ORDER     BY time
  MEASURES
    FIRST(time) AS start_time,
    LAST(time)  AS end_time,
    FIRST(n1)   AS start_n1,
    LAST(n1)    AS end_n1,
    FIRST(n2)   AS start_n2,
    LAST(n2)    AS end_n2,
    FIRST(link) AS start_link,
    LAST(link)  AS end_link
  ONE ROW PER MATCH
  PATTERN ( adjacent_minutes* last_minute )
  DEFINE adjacent_minutes AS NEXT(time) = LAST(time) + INTERVAL '1' MINUTE
)

其中，对于样本数据：

CREATE TABLE table_name ( time, code, n1, n2, link ) AS
SELECT DATE '2020-11-10' + INTERVAL '09:41:00' HOUR TO SECOND, 'abc', 1.02,  2.45, 'abc/1'   FROM DUAL UNION ALL
SELECT DATE '2020-11-10' + INTERVAL '09:42:00' HOUR TO SECOND, 'abc', 1.03,  2.44, 'abc/2'   FROM DUAL UNION ALL
SELECT DATE '2020-11-10' + INTERVAL '09:43:00' HOUR TO SECOND, 'abc', 1.04,  2.43, 'abc/3'   FROM DUAL UNION ALL
SELECT DATE '2020-11-10' + INTERVAL '11:14:00' HOUR TO SECOND, 'abc', 5.45, 10.24, 'abc/1_1' FROM DUAL UNION ALL
SELECT DATE '2020-11-10' + INTERVAL '11:15:00' HOUR TO SECOND, 'abc', 5.46, 10.23, 'abc/2_2' FROM DUAL UNION ALL
SELECT DATE '2020-11-10' + INTERVAL '11:16:00' HOUR TO SECOND, 'abc', 5.47, 10.22, 'abc/3_3' FROM DUAL UNION ALL
SELECT DATE '2020-11-11' + INTERVAL '21:09:00' HOUR TO SECOND, 'def', 3.55,  6.98, 'def/1'   FROM DUAL UNION ALL
SELECT DATE '2020-11-11' + INTERVAL '21:10:00' HOUR TO SECOND, 'def', 3.56,  6.97, 'def/2'   FROM DUAL UNION ALL
SELECT DATE '2020-11-11' + INTERVAL '21:11:00' HOUR TO SECOND, 'def', 3.57,  6.96, 'def/3'   FROM DUAL;

输出：

代码 | START_TIME | END_TIME | START_N1 | END_N1 | START_N2 | END_N2 | START_LINK | END_LINK
:--- | :----------------- | :----------------- | --------: | -----: | --------: | -----: | :--------- | :--------
美国广播公司 | 2020-11-10 09:41:00 | 2020-11-10 09:43:00 | 1.02 | 1.04 | 2.45 | 2.43 | abc/1 | ABC/3   
美国广播公司 | 2020-11-10 11:14:00 | 2020-11-10 11:16:00 | 5.45 | 5.47 | 10.24 | 10.22 | abc/1_1 | abc/3_3
定义 | 2020-11-11 21:09:00 | 2020-11-11 21:11:00 | 3.55 | 3.57 | 6.98 | 6.96 | 定义/1 | 定义/3

db<>在这里摆弄

推荐阅读