oracle根据范围分组,sql - Oracle - 按记录范围分组的最小值 - SO中文参考 - www.soinside.com...

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

热门问题