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
59
60
61
62
63
64
65
66
67
| with cte_MaTable (ColDate, AppName) as
(
select 42324.33862, null from dual union all
select 42324.33865, null from dual union all
select 42324.33869, 'LockAppHost' from dual union all
select 42324.33882, 'LockAppHost' from dual union all
select 42324.33889, 'SearchUI' from dual union all
select 42324.33915, 'explorer' from dual union all
select 42324.33916, 'explorer' from dual union all
select 42324.33917, 'explorer' from dual union all
select 42324.33918, 'OUTLOOK' from dual union all
select 42324.33918, 'OUTLOOK' from dual union all
select 42324.33921, 'explorer' from dual union all
select 42324.33922, 'explorer' from dual union all
select 42324.33929, 'OUTLOOK' from dual union all
select 42324.33929, 'explorer' from dual union all
select 42324.33935, 'firefox' from dual union all
select 42324.33936, 'firefox' from dual union all
select 42324.33937, 'firefox' from dual union all
select 42324.33937, 'firefox' from dual
)
, cte_tmp_1 (rn, coldate, appname) as
(
select row_number() over(order by coldate asc, appname desc)
, ColDate, AppName
from cte_matable
)
, cte_tmp_2 (rn, grp, coldate, appname) as
(
select rn
, rn
- row_number() over(partition by AppName order by rn asc) as grp
, ColDate, AppName
from cte_tmp_1
)
, cte_tmp_3 (rn, coldate_min, coldate, appname) as
(
select rn
, min(coldate) over(partition by grp, appname)
, coldate, appname
from cte_tmp_2
)
select dense_rank() over(order by coldate_min asc, appname desc) as groupe
, coldate, appname
from cte_tmp_3
order by rn asc;
GROUPE COLDATE APPNAME
---------- ----------- -----------
1 42324.33862
1 42324.33865
2 42324.33869 LockAppHost
2 42324.33882 LockAppHost
3 42324.33889 SearchUI
4 42324.33915 explorer
4 42324.33916 explorer
4 42324.33917 explorer
5 42324.33918 OUTLOOK
5 42324.33918 OUTLOOK
6 42324.33921 explorer
6 42324.33922 explorer
7 42324.33929 OUTLOOK
8 42324.33929 explorer
9 42324.33935 firefox
9 42324.33936 firefox
9 42324.33937 firefox
9 42324.33937 firefox |
