Oracle选择查找状态对的出现

Question

我有以下数据：

状态日期时间B 2018 1011 13:28:27B 20181011 13:36:05B 20181011 15:28:40I 20181011 15:28:57I 20181011 15:41:56I 20181018 08:21:43B 20181018 13:38:00I 20181019 17:03:00B 2018 1023 09:45:54I 2018 1023 10:35:44I 20181023 10:38:11

每次我有一个STATUS‘B’序列时，我都必须使用最后一个，STATUS‘I’也是如此，这样我就可以根据上面的规则获得每个状态，并计算它们之间的时间。我尝试了一些选择，但没有成功，我也找不到任何像我这样的问题。

编辑：我希望选择可以做出以下结果：

状态日期时间B 20181011 15:28:40I 20181018 08:21:43B 20181018 13:38:00I 20181019 17:03:00B 2018 1023 09:45:54I 20181023 10:38:11

Answer 1

这里有一个选项，它通过CTE引导您跟踪正在发生的事情。我建议您逐个执行它们。

此外，我希望实际上有一个DATE数据类型列；否则，如果有两个(日期和时间)，则将它们存储到VARCHAR2列中(可能)，这是个坏主意——我建议您更改模型并使用一个DATE数据类型列。

我们开始了：

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> with test (status, datum) as
2    (select 'B', to_date('11.10.2018 13:28:27', 'dd.mm.yyyy hh24:mi:ss') from dual union all
3     select 'B', to_date('11.10.2018 13:36:05', 'dd.mm.yyyy hh24:mi:ss') from dual union all
4     select 'B', to_date('11.10.2018 15:28:40', 'dd.mm.yyyy hh24:mi:ss') from dual union all
5     select 'I', to_date('11.10.2018 15:28:57', 'dd.mm.yyyy hh24:mi:ss') from dual union all
6     select 'I', to_date('11.10.2018 15:41:56', 'dd.mm.yyyy hh24:mi:ss') from dual union all
7     select 'I', to_date('18.10.2018 08:21:43', 'dd.mm.yyyy hh24:mi:ss') from dual union all
8     select 'B', to_date('18.10.2018 13:38:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
9     select 'I', to_date('19.10.2018 17:03:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
10     select 'B', to_date('23.10.2018 09:45:54', 'dd.mm.yyyy hh24:mi:ss') from dual union all
11     select 'I', to_date('23.10.2018 10:35:44', 'dd.mm.yyyy hh24:mi:ss') from dual union all
12     select 'I', to_date('23.10.2018 10:38:11', 'dd.mm.yyyy hh24:mi:ss') from dual
13    ),
14  inter as
15    (select status, lag(status) over (order by datum) lag_status, datum
16     from test
17    ),
18  inter_2 as
19    (select status, datum,
20       sum(case when status = nvl(lag_status, status) then 0 else 1 end) over (order by datum) grp
21    from inter
22    )
23  select status, max(datum) datum
24  from inter_2
25  group by status, grp
26  order by datum;
S DATUM
- -------------------
B 11.10.2018 15:28:40
I 18.10.2018 08:21:43
B 18.10.2018 13:38:00
I 19.10.2018 17:03:00
B 23.10.2018 09:45:54
I 23.10.2018 10:38:11
6 rows selected.
SQL>

• INTERCTE从前一行中选择STATUS值
• INTER_2根据前一行和当前行的STATUS列值是相同还是不同来创建组
• 最终查询按STATUS和GRP(组)对数据进行分组，并选择MAXDATUM值

Answer 2

首先获取符合条件的所有日期-时间对，并连接到主表：

select tablename.* from (
select mydate, mytime from tablename t
where not exists (
select 1 from tablename 
where 
tablename.status = t.status 
and 
concat(tablename.mydate, tablename.mytime) = (
select min(concat(tablename.mydate, tablename.mytime)) from tablename where concat(mydate, mytime) > concat(t.mydate, t.mytime)
)
)
) d
inner join tablename
on d.mydate = tablename.mydate and d.mytime = tablename.mytime;

参见演示

Answer 3

这是间隙和孤岛问题的一个版本。对于这个版本，我认为lead()是最好的方法：

select status, date, time
from (select t.*,
lead(status) over (order by date, time) as next_status
from t
) t
where next_status is null or next_status <> status;