Re: Aggregation Problem

#1

On 23 oct, 14:08, Björn Wächter <bwc (AT) p3-solutionsKILL_SPAM (DOT) de> wrote:

Quote:

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

Analytics rock, analytics roll...

Cheers.

Carlos.

#2

"Björn Wächter" <bwc (AT) p3-solutionsKILL_SPAM (DOT) de> a écrit dans le message de news: 5o66hvFl9hevU1 (AT) mid (DOT) dfncis.de...

Quote:

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: Aggregation Problem

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Aggregation Problem - 10-23-2007 , 07:59 AM

Re: Aggregation Problem - 10-23-2007 , 11:48 AM