Re: Aggregation Problem


"Björn Wächter" <bwc@xxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message de news: 5o66hvFl9hevU1@xxxxxxxxxxxxxxxx
| Hi all,
|
|
| I'm looking for a function that can calculate the following:
| I have a table like this:
|
| STATE_ID | SERVICE_STATE
| -------------------------------------
| 1 | OK
| 2 | OK
| 3 | ERROR
| 4 | ERROR
| 5 | ERROR
| 6 | ERROR
| 7 | ERROR
| 8 | OK
| 9 | OK
|
| I want a query thats shows all rows of the table but has a new
| column. Which has a kind of group_id that is not changing if the
| SERVICE_SATE is not changing from one STATE_ID to the next per
| SEVICE_ID:
|
| STATE_ID | SERVICE_STATE | CONTINUE_GROUP
| --------------------------------------------------
| 1 | OK | 1
| 2 | OK | 1
| 3 | ERROR | 2
| 4 | ERROR | 2
| 5 | ERROR | 2
| 6 | ERROR | 2
| 7 | ERROR | 2
| 8 | OK | 3
| 9 | OK | 3
|
| So in this example the SERVICE_STATE is changing for
| STATE_ID 2 to 3 from OK to ERROR so there is a new
| CONTINUE_GROUP = 2. And again a change from ERROR to OK
| for STATE_ID 7 to 8 and the new CONTINUE_GROUP = 3.
| Is there an aggregation function that can do this?
|
| Thanks Björn

SQL> select * from t order by state_id;
STATE_ID SERVICE_ST
---------- ----------
1 OK
2 OK
3 ERROR
4 ERROR
5 ERROR
6 ERROR
7 ERROR
8 OK
9 OK

9 rows selected.

SQL> with
2 data as (
3 select state_id, service_state,
4 case
5 when lag(service_state) over (order by state_id) = service_state
6 then null
7 else row_number() over (order by state_id)
8 end grp
9 from t
10 ),
11 grouping as (
12 select state_id, service_state,
13 max(grp) over (order by state_id) grp
14 from data
15 )
16 select state_id, service_state,
17 dense_rank() over (order by grp) continue_group
18 from grouping
19 order by state_id
20 /
STATE_ID SERVICE_ST CONTINUE_GROUP
---------- ---------- --------------
1 OK 1
2 OK 1
3 ERROR 2
4 ERROR 2
5 ERROR 2
6 ERROR 2
7 ERROR 2
8 OK 3
9 OK 3

9 rows selected.

Regards
Michel Cadot


.

Relevant Pages