|
Сгруппировать записи по дате в диапазоне 10 сек.
#39643604
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
Участник
Откуда: Middlebury, CT USA
Сообщения: 10 588
|
|
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
with t(id, dt) as (
select 1, to_date('27.07.10 08:25:15','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('14.05.14 07:06:29','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 09:43:21','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 09:43:23','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 09:43:25','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:25','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:27','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:29','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:31','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:33','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:35','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:37','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:39','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:41','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:43:58','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:44:07','dd.mm.rr hh24:mi:ss') from dual union all
select 1, to_date('26.02.18 20:44:08','dd.mm.rr hh24:mi:ss') from dual
)
select id,
to_char(dt,'mm/dd/yyyy hh24:mi:ss') dt,
to_char(g_id,'mm/dd/yyyy hh24:mi:ss') g_id,
grp
from t
match_recognize(
partition by id
order by dt
measures match_number() as grp,
first(dt) as g_id
all rows per match
pattern(grp+)
define grp as dt - first(dt) < 10/86400
)
/
ID DT G_ID GRP
--- ------------------- ------------------- ----------
1 07/27/2010 08:25:15 07/27/2010 08:25:15 1
1 05/14/2014 07:06:29 05/14/2014 07:06:29 2
1 02/26/2018 09:43:21 02/26/2018 09:43:21 3
1 02/26/2018 09:43:23 02/26/2018 09:43:21 3
1 02/26/2018 09:43:25 02/26/2018 09:43:21 3
1 02/26/2018 20:43:25 02/26/2018 20:43:25 4
1 02/26/2018 20:43:27 02/26/2018 20:43:25 4
1 02/26/2018 20:43:29 02/26/2018 20:43:25 4
1 02/26/2018 20:43:31 02/26/2018 20:43:25 4
1 02/26/2018 20:43:33 02/26/2018 20:43:25 4
1 02/26/2018 20:43:35 02/26/2018 20:43:35 5
1 02/26/2018 20:43:37 02/26/2018 20:43:35 5
1 02/26/2018 20:43:39 02/26/2018 20:43:35 5
1 02/26/2018 20:43:41 02/26/2018 20:43:35 5
1 02/26/2018 20:43:58 02/26/2018 20:43:58 6
1 02/26/2018 20:44:07 02/26/2018 20:43:58 6
1 02/26/2018 20:44:08 02/26/2018 20:44:08 7
17 rows selected.
SQL>
SY. |
|
|
