Oracle - 按记录范围分组的最小值
问题描述 投票:0回答:3
举个例子,假设我在表中有以下值:
---------------------------------------
| ID_USER | START_DATE |
---------------------------------------
| 1 | 01/01/2018 08:00:00 |
| 1 | 01/01/2018 08:15:00 |
| 2 | 01/01/2018 08:30:00 |
| 1 | 01/01/2018 08:45:00 |
| 1 | 01/01/2018 09:00:00 |
| 2 | 01/01/2018 09:15:00 |
| 2 | 01/01/2018 09:30:00 |
| 1 | 01/01/2018 09:45:00 |
---------------------------------------
现在我想按ID_USER分组,只要它们是相关的,就选择最小的START_DATE值。解决方案是:
---------------------------------------
| ID_USER | START_DATE |
---------------------------------------
| 1 | 01/01/2018 08:00:00 |
| 2 | 01/01/2018 08:30:00 |
| 1 | 01/01/2018 08:45:00 |
| 2 | 01/01/2018 09:15:00 |
| 1 | 01/01/2018 09:45:00 |
---------------------------------------
你知道我怎么能进行这个查询吗?
sql
oracle
oracle12c
3个回答
1
投票
使用LAG比较当前行和上一行:
with cte as
(
select ID_USER, START_DATE,
lag(ID_USER, 1, -1) over (order by START_DATE) as prev_user
from myTable
)
select *
from cte
where ID_USER <> prev_user
1
投票
with s (id_user, start_date) as (
select 1, to_date('01/01/2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 08:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 08:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 08:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 09:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 09:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 09:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 09:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual)
select id_user, start_date
from
(select s.*, lag(id_user) over (order by start_date) prev_user
from s
)
where lnnvl(prev_user = id_user);
ID_USER START_DATE
---------- -------------------
1 2018-01-01 08:00:00
2 2018-01-01 08:30:00
1 2018-01-01 08:45:00
2 2018-01-01 09:15:00
1 2018-01-01 09:45:00
0
投票
-- Oracle 12c+: Pattern matching
with s (id_user, start_date) as (
select 1, to_date('01/01/2018 08:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 08:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 08:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 08:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 09:00:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 09:15:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 2, to_date('01/01/2018 09:30:00', 'dd.mm.yyyy hh24:mi:ss') from dual union all
select 1, to_date('01/01/2018 09:45:00', 'dd.mm.yyyy hh24:mi:ss') from dual)
select id_user, start_date
from s
match_recognize(
order by start_date
measures
id_user as id_user,
start_date as start_date
pattern (v+)
define v as id_user = first(id_user)
);
ID_USER START_DATE
---------- -------------------
1 2018-01-01 08:15:00
2 2018-01-01 08:30:00
1 2018-01-01 09:00:00
2 2018-01-01 09:30:00
1 2018-01-01 09:45:00
热门问题