Home » SQL & PL/SQL » SQL & PL/SQL » Cumulative agreegating strings (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Cumulative agreegating strings [message #634885] |
Tue, 17 March 2015 14:53  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I have a table like below:
WITH temp
AS (SELECT 1 id, 'ABC' val, SYSDATE dt_tm FROM DUAL
UNION ALL
SELECT 1, 'ABCD', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 2, 'DEF', SYSDATE FROM DUAL
UNION ALL
SELECT 2, 'DEF', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 2, 'XYZ', SYSDATE + 2 / 24
FROM DUAL
UNION ALL
SELECT 3, 'ABC', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 3, 'MNO', SYSDATE + 2 / 24
FROM DUAL
UNION ALL
SELECT 3, 'MNO', SYSDATE + 3 / 24
FROM DUAL
UNION ALL
SELECT 3, 'XYZ', SYSDATE + 4 / 24
FROM DUAL)
SELECT *
FROM temp;
I have to cumulatively agreegate the values based on id and dt_tm.
Rules are:
1. If id is same, then order on dt_tm, and merge values with a '|' except the first value.
2. If two consecutive values are same, then merge only before those two values.
So the result would look like:
1 ABC DATETIME ABC
1 ABCD DATETIME ABCD|ABC
2 DEF DATETIME DEF
2 DEF DATETIME DEF
2 XYZ DATETIME XYZ|DEF
3 ABC DATETIME ABC
3 MNO DATETIME MNO|ABC
3 MNO DATETIME MNO|ABC
3 XYZ DATETIME XYZ|MNO|ABC
Can someone please help.
Manu
[Updated on: Tue, 17 March 2015 15:00] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Cumulative agreegating strings [message #634888 is a reply to message #634887] |
Tue, 17 March 2015 15:05  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
No, we don't have to keep one.
If values are same for the same id, we don't have to merge, we have to merge it with next different value.
I was trying something like below:
WITH temp
AS (SELECT 1 id, 'ABC' val, SYSDATE dt_tm FROM DUAL
UNION ALL
SELECT 1, 'ABCD', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 2, 'DEF', SYSDATE FROM DUAL
UNION ALL
SELECT 2, 'DEF', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 2, 'XYZ', SYSDATE + 2 / 24
FROM DUAL
UNION ALL
SELECT 3, 'ABC', SYSDATE + 1 / 24
FROM DUAL
UNION ALL
SELECT 3, 'MNO', SYSDATE + 2 / 24
FROM DUAL
UNION ALL
SELECT 3, 'MNO', SYSDATE + 3 / 24
FROM DUAL
UNION ALL
SELECT 3, 'XYZ', SYSDATE + 4 / 24
FROM DUAL),
temp_ranked
AS (SELECT id,
val,
dt_tm,
DENSE_RANK ()
OVER (PARTITION BY id ORDER BY NVL (val, dt_tm))
FROM temp)
SELECT *
FROM temp_ranked;
But not sure, what to do next, I am not even able to rank them properly.
Manu
[Updated on: Tue, 17 March 2015 15:07] Report message to a moderator |
|
|
|
|
|
| Re: Cumulative agreegating strings [message #634890 is a reply to message #634888] |
Tue, 17 March 2015 15:10 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
So for each line, first value will not be merged with any value, as there's no value before (before means basis of dt_tm)
The next all values will be merged, if the all are distinct, if any value in between is same as previous value, it will go up, and check, what was the distinct value previously and then merge with it (e.g. for id = 3)
Thanks,
Manu
|
|
|
|
|
|
| Re: Cumulative agreegating strings [message #634892 is a reply to message #634891] |
Tue, 17 March 2015 15:16 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
1 ABC DATETIME ABC
Because the above is first value for id = 1, no merge
1 ABCD DATETIME ABCD|ABC
Because the above is 2nd value for id = 1, and it's not same as it's previous value, merge
2 DEF DATETIME DEF
Because above is the first value for id = 2, no merge
2 DEF DATETIME DEF
Because above is the second value for id = 2, but it's same as previous value, no merge.
2 XYZ DATETIME XYZ|DEF
Because above is the third value for id = 2, and not same as previous value, merge.
3 ABC DATETIME ABC
Because the above is first value for id = 3, no merge
3 MNO DATETIME MNO|ABC
Because above is the second value for id = 3, but it's not same as previous value.
3 MNO DATETIME MNO|ABC
Because above is the third value for id = 3, but it's same as previous value, go up, till you find the different value, what was the merged column in that, merge it here with current value (or simply copy from the above row)
3 XYZ DATETIME XYZ|MNO|ABC
Because above is the fourth value for id = 3, and not same as previous value, merge with the previously merged value of previous row.
Thanks,
Manu
|
|
|
|
| Re: Cumulative agreegating strings [message #634894 is a reply to message #634892] |
Tue, 17 March 2015 15:38 |
 |
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can do it using hierarchical query or faster using the following variation of T. Kyte's STRAGG analytic function:
create or replace type stragg_type as object
(
result varchar2(4000),
static function ODCIAggregateInitialize (sctx IN OUT stragg_type)
return number,
member function ODCIAggregateIterate (self IN OUT stragg_type,
value IN varchar2)
return number,
member function ODCIAggregateTerminate (self IN stragg_type,
returnValue OUT varchar2,
flags IN number)
return number,
member function ODCIAggregateMerge (self IN OUT stragg_type,
ctx2 IN stragg_type)
return number
);
/
create or replace type body stragg_type
is
static function ODCIAggregateInitialize (sctx IN OUT stragg_type)
return number
is
begin
sctx := stragg_type (null);
return ODCIConst.Success;
end;
member function ODCIAggregateIterate (self IN OUT stragg_type,
value IN varchar2)
return number
is
begin
self.result := value || '|' || self.result;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate (self IN stragg_type,
returnValue OUT varchar2,
flags IN number)
return number
is
begin
returnValue := rtrim (self.result, '|');
return ODCIConst.Success;
end;
member function ODCIAggregateMerge (self IN OUT stragg_type,
ctx2 IN stragg_type)
return number
is
begin
self.result := ctx2.result || self.result;
return ODCIConst.Success;
end;
end;
/
sho err
CREATE or replace FUNCTION stragg (input varchar2)
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING stragg_type;
/
SQL> WITH temp
2 AS (SELECT 1 id, 'ABC' val, SYSDATE dt_tm FROM DUAL
3 UNION ALL
4 SELECT 1, 'ABCD', SYSDATE + 1 / 24
5 FROM DUAL
6 UNION ALL
7 SELECT 2, 'DEF', SYSDATE FROM DUAL
8 UNION ALL
9 SELECT 2, 'DEF', SYSDATE + 1 / 24
10 FROM DUAL
11 UNION ALL
12 SELECT 2, 'XYZ', SYSDATE + 2 / 24
13 FROM DUAL
14 UNION ALL
15 SELECT 3, 'ABC', SYSDATE + 1 / 24
16 FROM DUAL
17 UNION ALL
18 SELECT 3, 'MNO', SYSDATE + 2 / 24
19 FROM DUAL
20 UNION ALL
21 SELECT 3, 'MNO', SYSDATE + 3 / 24
22 FROM DUAL
23 UNION ALL
24 SELECT 3, 'XYZ', SYSDATE + 4 / 24
25 FROM DUAL),
26 data as (
27 select id, val, dt_tm,
28 lag(val) over (partition by id order by dt_tm) prev_val
29 from temp
30 )
31 select id, val, dt_tm,
32 stragg(decode(val, prev_val, null, val)) over (partition by id order by dt_tm) val2
33 from data
34 order by id, dt_tm
35 /
ID VAL DT_TM VAL2
---------- ---------- ------------------- ------------------------------
1 ABC 17/03/2015 21:36:04 ABC
1 ABCD 17/03/2015 22:36:04 ABCD|ABC
2 DEF 17/03/2015 21:36:04 DEF
2 DEF 17/03/2015 22:36:04 DEF
2 XYZ 17/03/2015 23:36:04 XYZ|DEF
3 ABC 17/03/2015 22:36:04 ABC
3 MNO 17/03/2015 23:36:04 MNO|ABC
3 MNO 18/03/2015 00:36:04 MNO|ABC
3 XYZ 18/03/2015 01:36:04 XYZ|MNO|ABC
9 rows selected. |
|
|
|
|
|
| Re: Cumulative agreegating strings [message #634930 is a reply to message #634896] |
Wed, 18 March 2015 02:55 |
|
Michel Cadot
Messages: 68776
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
You can do it with hierarchical query like:
SQL> WITH temp
2 AS (SELECT 1 id, 'ABC' val, SYSDATE dt_tm FROM DUAL
3 UNION ALL
4 SELECT 1, 'ABCD', SYSDATE + 1 / 24
5 FROM DUAL
6 UNION ALL
7 SELECT 2, 'DEF', SYSDATE FROM DUAL
8 UNION ALL
9 SELECT 2, 'DEF', SYSDATE + 1 / 24
10 FROM DUAL
11 UNION ALL
12 SELECT 2, 'XYZ', SYSDATE + 2 / 24
13 FROM DUAL
14 UNION ALL
15 SELECT 3, 'ABC', SYSDATE + 1 / 24
16 FROM DUAL
17 UNION ALL
18 SELECT 3, 'MNO', SYSDATE + 2 / 24
19 FROM DUAL
20 UNION ALL
21 SELECT 3, 'MNO', SYSDATE + 3 / 24
22 FROM DUAL
23 UNION ALL
24 SELECT 3, 'XYZ', SYSDATE + 4 / 24
25 FROM DUAL),
26 data as (
27 select id, val, dt_tm,
28 row_number() over (partition by id order by dt_tm) rn
29 from temp
30 )
31 select id, val, dt_tm,
32 trim(both '|' from
33 regexp_replace(
34 sys_connect_by_path(decode(val, prior val, null, val), '|'),
35 '[|]+', '|')
36 ) val2
37 from data
38 connect by prior id = id and prior rn = rn-1
39 start with rn=1
40 /
ID VAL DT_TM VAL2
---------- ---- ------------------- ------------------------------
1 ABC 18/03/2015 08:53:06 ABC
1 ABCD 18/03/2015 09:53:06 ABC|ABCD
2 DEF 18/03/2015 08:53:06 DEF
2 DEF 18/03/2015 09:53:06 DEF
2 XYZ 18/03/2015 10:53:06 DEF|XYZ
3 ABC 18/03/2015 09:53:06 ABC
3 MNO 18/03/2015 10:53:06 ABC|MNO
3 MNO 18/03/2015 11:53:06 ABC|MNO
3 XYZ 18/03/2015 12:53:06 ABC|MNO|XYZ
9 rows selected.
Unfortunately, sys_connect_by_path does not allow you to get the elements in reverse order.
I once opened an enhancement request to Oracle to make them add a second parameter to the function which will indicate the order: "normal", "reverse"... but no news for the moment.
|
|
|
|
| Re: Cumulative agreegating strings [message #634968 is a reply to message #634885] |
Wed, 18 March 2015 08:01 |
Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just for fun, MODEL solution:
WITH temp AS (
SELECT 1 id, 'ABC' val, SYSDATE dt_tm FROM DUAL UNION ALL
SELECT 1, 'ABCD', SYSDATE + 1 / 24 FROM DUAL UNION ALL
SELECT 2, 'DEF', SYSDATE FROM DUAL UNION ALL
SELECT 2, 'DEF', SYSDATE + 1 / 24 FROM DUAL UNION ALL
SELECT 2, 'XYZ', SYSDATE + 2 / 24 FROM DUAL UNION ALL
SELECT 3, 'ABC', SYSDATE + 1 / 24 FROM DUAL UNION ALL
SELECT 3, 'MNO', SYSDATE + 2 / 24 FROM DUAL UNION ALL
SELECT 3, 'MNO', SYSDATE + 3 / 24 FROM DUAL UNION ALL
SELECT 3, 'XYZ', SYSDATE + 4 / 24 FROM DUAL
)
SELECT id,
val,
dt_tm,
val2
FROM temp
MODEL
PARTITION BY(id)
DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY id ORDER BY dt_tm,val) rn)
MEASURES(dt_tm,val,CAST(val as VARCHAR2(4000)) val2)
RULES(
val2[rn > 1] ORDER BY rn = CASE
WHEN val[CV()] = val[CV() - 1] THEN val2[CV() - 1]
WHEN val[CV()] IS NULL AND val[CV() - 1] IS NULL THEN val2[CV() - 1]
ELSE val2[CV() - 1] || '|' || val[CV()]
END
)
/
ID VAL DT_TM VAL2
---------- ---- ------------------- --------------------
1 ABC 18/03/2015 09:00:35 ABC
1 ABCD 18/03/2015 10:00:35 ABC|ABCD
2 DEF 18/03/2015 09:00:35 DEF
2 DEF 18/03/2015 10:00:35 DEF
2 XYZ 18/03/2015 11:00:35 DEF|XYZ
3 ABC 18/03/2015 10:00:35 ABC
3 MNO 18/03/2015 11:00:35 ABC|MNO
3 MNO 18/03/2015 12:00:35 ABC|MNO
3 XYZ 18/03/2015 13:00:35 ABC|MNO|XYZ
9 rows selected.
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Aug 27 17:24:11 CDT 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
