Home » SQL & PL/SQL » SQL & PL/SQL » Divide in Groups (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
| Divide in Groups [message #630334] |
Wed, 24 December 2014 14:03  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
Hi,
From below table, I have to divide the rows into groups.
WITH temp AS (SELECT 1 EMPID, NULL TYPE_OF_SERVICE FROM DUAL
UNION ALL
SELECT 2 EMPID, NULL FROM DUAL
UNION ALL
SELECT 3 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 4 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 5 EMPID, NULL FROM DUAL
UNION ALL
SELECT 6 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 7 EMPID, NULL FROM DUAL
UNION ALL
SELECT 8 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 9 EMPID, NULL FROM DUAL
UNION ALL
SELECT 10 EMPID, NULL FROM DUAL
UNION ALL
SELECT 11 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 12 EMPID, NULL FROM DUAL
UNION ALL
SELECT 13 EMPID, NULL FROM DUAL)
SELECT *
FROM temp
ORDER BY EMPID;
Rules are as below:
--1. If there are blanks in beginning, they should be considered under same group as the first NOT NULL value
--2. If there are following blanks after NOT NULL, they should be considered into same group as the first NOT NULL value
--3. Group number will change, whenever NOT NULL value will change.
NULL 1
NULL 1
ACCOUNT Services 1
ACCOUNT Services 1
NULL 1
OTHER Services 2
NULL 2
ACCOUNT Services 3
NULL 3
NULL 3
OTHER Services 4
NULL 4
NULL 4
Can you please help, how can I achieve grouping like this.
Thanks,
Manu
[Updated on: Wed, 24 December 2014 14:04] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Divide in Groups [message #630337 is a reply to message #630336] |
Wed, 24 December 2014 14:50  |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I tried below, working on to find out correct windowing clause.
WITH temp AS (SELECT 1 EMPID, NULL TYPE_OF_SERVICE FROM DUAL
UNION ALL
SELECT 2 EMPID, NULL FROM DUAL
UNION ALL
SELECT 3 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 4 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 5 EMPID, NULL FROM DUAL
UNION ALL
SELECT 6 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 7 EMPID, NULL FROM DUAL
UNION ALL
SELECT 8 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 9 EMPID, NULL FROM DUAL
UNION ALL
SELECT 10 EMPID, NULL FROM DUAL
UNION ALL
SELECT 11 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 12 EMPID, NULL FROM DUAL
UNION ALL
SELECT 13 EMPID, NULL FROM DUAL)
SELECT empid,
type_of_service,
FIRST_VALUE (new_type_of_service IGNORE NULLS)
OVER (ORDER BY empid ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) NEW_TYPE_OF_SERVICE
FROM ( SELECT empid,
type_of_service,
LAST_VALUE (type_of_service IGNORE NULLS)
OVER (ORDER BY empid)
new_type_of_service
FROM temp
ORDER BY EMPID);
Above seems to give right result, can anyone please verify, and let me know if there are more optimized way, or if I am not clear on requirements.
Manu
[Updated on: Wed, 24 December 2014 15:07] Report message to a moderator |
|
|
|
|
|
| Re: Divide in Groups [message #630358 is a reply to message #630334] |
Thu, 25 December 2014 05:20 |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> WITH temp AS (SELECT 1 EMPID, NULL TYPE_OF_SERVICE FROM DUAL
2 UNION ALL
3 SELECT 2 EMPID, NULL FROM DUAL
4 UNION ALL
5 SELECT 3 EMPID, 'ACCOUNT Services' FROM DUAL
6 UNION ALL
7 SELECT 4 EMPID, 'ACCOUNT Services' FROM DUAL
8 UNION ALL
9 SELECT 5 EMPID, NULL FROM DUAL
10 UNION ALL
11 SELECT 6 EMPID, 'OTHER Services' FROM DUAL
12 UNION ALL
13 SELECT 7 EMPID, NULL FROM DUAL
14 UNION ALL
15 SELECT 8 EMPID, 'ACCOUNT Services' FROM DUAL
16 UNION ALL
17 SELECT 9 EMPID, NULL FROM DUAL
18 UNION ALL
19 SELECT 10 EMPID, NULL FROM DUAL
20 UNION ALL
21 SELECT 11 EMPID, 'OTHER Services' FROM DUAL
22 UNION ALL
23 SELECT 12 EMPID, NULL FROM DUAL
24 UNION ALL
25 SELECT 13 EMPID, NULL FROM DUAL
26 UNION ALL
27 SELECT 14 EMPID, 'OTHER Services' FROM DUAL
28 UNION ALL
29 SELECT 15 EMPID, NULL FROM DUAL
30 ),
31 grouped as (
32 select empid, type_of_service,
33 nvl(type_of_service,
34 nvl(last_value(type_of_service ignore nulls) over (order by empid),
35 first_value(type_of_service ignore nulls)
36 over (order by empid
37 rows between current row and unbounded following)))
38 group_name,
39 case
40 when row_number() over (order by empid) = 1
41 or type_of_service !=
42 last_value(type_of_service ignore nulls)
43 over (order by empid
44 rows between unbounded preceding and 1 preceding)
45 then 'NEW GROUP'
46 end flag
47 from temp
48 )
49 select empid, type_of_service, group_name,
50 count(flag) over (order by empid) group_nb
51 from grouped
52 order by empid
53 /
EMPID TYPE_OF_SERVICE GROUP_NAME GROUP_NB
---------- ---------------- ---------------- ----------
1 ACCOUNT Services 1
2 ACCOUNT Services 1
3 ACCOUNT Services ACCOUNT Services 1
4 ACCOUNT Services ACCOUNT Services 1
5 ACCOUNT Services 1
6 OTHER Services OTHER Services 2
7 OTHER Services 2
8 ACCOUNT Services ACCOUNT Services 3
9 ACCOUNT Services 3
10 ACCOUNT Services 3
11 OTHER Services OTHER Services 4
12 OTHER Services 4
13 OTHER Services 4
14 OTHER Services OTHER Services 4
15 OTHER Services 4
15 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Divide in Groups [message #630386 is a reply to message #630385] |
Thu, 25 December 2014 12:06 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I think order is maintained by below
41 or type_of_service !=
42 last_value(type_of_service ignore nulls)
43 over (order by empid
44 rows between unbounded preceding and 1 preceding)
45 then 'NEW GROUP'
it's just for the very first row. row_number() is not required for ordering, because we don't need order in each group, we just need it for very first row.
Can you tell me any scenario in which, order will be required.
Thanks,
Manu
[Updated on: Thu, 25 December 2014 12:07] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Divide in Groups [message #630389 is a reply to message #630388] |
Thu, 25 December 2014 12:43 |
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
|
Senior Member |
|
|
I am completely agree with - rownum is non-deterministic.
What I am trying to ask is below:
If I execute below query again and again, will there be any chance if I will get different result (note that I added 2 rows in last)
Seems like something is maintaining order already, so we may not need row_number()
or it may be the case - it seems to maintain the order for now, but I actually don't know which part is maintaining order, and will it maintain for each run?
WITH temp AS (SELECT 1 EMPID, NULL TYPE_OF_SERVICE FROM DUAL
UNION ALL
SELECT 2 EMPID, NULL FROM DUAL
UNION ALL
SELECT 3 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 4 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 5 EMPID, NULL FROM DUAL
UNION ALL
SELECT 6 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 7 EMPID, NULL FROM DUAL
UNION ALL
SELECT 8 EMPID, 'ACCOUNT Services' FROM DUAL
UNION ALL
SELECT 9 EMPID, NULL FROM DUAL
UNION ALL
SELECT 10 EMPID, NULL FROM DUAL
UNION ALL
SELECT 11 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 12 EMPID, NULL FROM DUAL
UNION ALL
SELECT 13 EMPID, NULL FROM DUAL
UNION ALL
SELECT 14 EMPID, 'OTHER Services' FROM DUAL
UNION ALL
SELECT 15 EMPID, NULL FROM DUAL
UNION ALL
SELECT 1 EMPID, 'NEW Services' FROM DUAL
UNION ALL
SELECT 17 EMPID, NULL FROM DUAL)
SELECT empid,
type_of_service,
CASE
WHEN ROWNUM = 1
OR type_of_service !=
LAST_VALUE (
type_of_service IGNORE NULLS)
OVER (ORDER BY empid
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
THEN
'NEW GROUP'
END
flag
FROM temp;
Manu
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 07:48:07 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
