Re: Aggregation Problem

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 23 Oct 2007 18:48:47 +0200
Message-ID: <471e25ef$0$12461$426a74cc@news.free.fr>

"Björn Wächter" <bwc_at_p3-solutionsKILL_SPAM.de> a écrit dans le message de news: 5o66hvFl9hevU1_at_mid.dfncis.de...
| 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 Received on Tue Oct 23 2007 - 11:48:47 CDT