Home » SQL & PL/SQL » SQL & PL/SQL » Ranking data by a set of data maybe using analytical function (12.2)
Ranking data by a set of data maybe using analytical function [message #673014] |
Thu, 01 November 2018 17:27 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
I Have data like:
System Err_Code Err_Code_Cnt
system 1 2 10
system 1 3 10
system 1 4 10
system 2 2 5
system 2 3 15
system 2 4 10
I want to sum all "err_code_cnt" values by System. I want to display all systems but determine which system has the most sum(err_code_cnt) values. If there is a tie, I want to use as a tiebreaker(s) "err_code" value by desc order. I want the entire result set sorted by system, "err_code" desc order.
I want the result to be:
System Err_Code Err_Code_Cnt
system 2 4 10
system 2 3 15
system 2 2 5
system 1 4 10
system 1 3 10
system 1 2 10
Thus, Both system have a sum(err_code_cnt) of 30 and because both system(s) have a "err_code_cnt" of 10 for "err_code" 4, I had to compare "err_code" 3 for both systems. System 2 for "err_code" 3 has a value of 15 while System 1 for "err_code" 3 is 10. Thus System 2 should be ranked first in this list.
My question is can this result be done using a "dense_rank" function??
I did a pivot of each system and "err_code" to determine the "ranking
These columns weren't actual, I just "typed" them in. This is close to what my table "ranked_system_data" looks like
System Code_2 Code_3 Code_4 Cnt_All Ranking
system 2 5 15 10 30 1
system 1 10 10 10 30 2
Here is my entire code(NOT using a "rank" analytical function)
with system_data as
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
select 'system 2' system, 4 err_code, 10 err_code_cnt from dual
),system_pivot AS (
SELECT
system,
MAX(DECODE(sd.err_code,2,sd.err_code_cnt) ) err_code_2_cnt,
MAX(DECODE(sd.err_code,3,sd.err_code_cnt) ) err_code_3_cnt,
MAX(DECODE(sd.err_code,4,sd.err_code_cnt) ) err_code_4_cnt,
SUM(sd.err_code_cnt) err_code_cnt_all
FROM
system_data sd
GROUP BY
system
),ranked_system_data AS (
SELECT
sp.*,
ROW_NUMBER() OVER(
ORDER BY
err_code_cnt_all DESC,
err_code_4_cnt DESC,
err_code_3_cnt DESC,
err_code_2_cnt DESC
) ranked_system
FROM
system_pivot sp
),ranked_systems_all AS (
SELECT
rs.ranked_system,
rs.err_code_cnt_all,
s.*
FROM
ranked_system_data rs,
system_data s
WHERE
rs.system = s.system
ORDER BY
rs.ranked_system
)
--select * from system_data
--select * from system_pivot
--select * from ranked_system_data
--select * from ranked_systems_all
SELECT
rsa.system,
--rsa.err_code_cnt_all,
rsa.err_code,
rsa.err_code_cnt
FROM
ranked_systems_all rsa
ORDER BY
ranked_system,
rsa.system,
rsa.err_code_cnt_all DESC,
rsa.err_code DESC,
rsa.err_code_cnt
I hope I captured most of what I'm trying to get. As usual, I'm not the best at defining my problem I'm trying to solve. Please ask me to clarify where applicable
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673015 is a reply to message #673014] |
Thu, 01 November 2018 18:47 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
1 with system_data as
2 (
3 select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
4 select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
5 select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
6 select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
7 select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
8 select 'system 2' system, 4 err_code, 10 err_code_cnt from dual
9 )
10 SELECT SYSTEM, ERR_CODE, ERR_CODE_CNT
11 FROM SYSTEM_DATA
12* ORDER BY SYSTEM DESC, ERR_CODE DESC
SQL> /
SYSTEM ERR_CODE ERR_CODE_CNT
-------- ---------- ------------
system 2 4 10
system 2 3 15
system 2 2 5
system 1 4 10
system 1 3 10
system 1 2 10
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673021 is a reply to message #673014] |
Fri, 02 November 2018 02:30 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Your example should contain more different cases in order to be complete, here it represents only ONE case: same count, same err_code, different err_code_cnt.
As BlackSwan showed, she get the result you want without following your rules.
So post a test case which invalidates her query and other possible solutions that can violate one of your rules.
In short, "system 3" with smaller count, "system 4" with bigger count, "system 5" with same count than another one but same and different err_code(s)...
All possible cases to show how all your rules work so we can work and find a solution that match all of them.
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673031 is a reply to message #673021] |
Fri, 02 November 2018 09:40 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
Quote:Your example should contain more different cases in order to be complete, here it represents only ONE case: same count, same err_code, different err_code_cnt.
Yes, fair enough. I added 4 more "systems". I also added a new requirement which should still be able to be determined with the "ranked_system" column. I only want the top 5 systems according to my earlier specs.
with system_data as
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all
select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all
select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
),system_pivot AS (
SELECT
system,
MAX(DECODE(sd.err_code,2,sd.err_code_cnt) ) err_code_2_cnt,
MAX(DECODE(sd.err_code,3,sd.err_code_cnt) ) err_code_3_cnt,
MAX(DECODE(sd.err_code,4,sd.err_code_cnt) ) err_code_4_cnt,
SUM(sd.err_code_cnt) err_code_cnt_all
FROM
system_data sd
GROUP BY
system
),ranked_system_data AS (
SELECT
sp.*,
ROW_NUMBER() OVER(
ORDER BY
err_code_cnt_all DESC,
err_code_4_cnt DESC,
err_code_3_cnt DESC,
err_code_2_cnt DESC
) ranked_system
FROM
system_pivot sp
),ranked_systems_all AS (
SELECT
rs.ranked_system,
rs.err_code_cnt_all,
s.*
FROM
ranked_system_data rs,
system_data s
WHERE
rs.system = s.system
ORDER BY
rs.ranked_system
)
--select * from system_data
--select * from system_pivot
--select * from ranked_system_data
--select * from ranked_systems_all
select
rsa.ranked_system,
rsa.system,
rsa.err_code_cnt_all,
rsa.err_code,
rsa.err_code_cnt
FROM
ranked_systems_all rsa
where
rsa.ranked_system <= 5
order by
rsa.ranked_system,
rsa.system,
rsa.err_code_cnt_all DESC,
rsa.err_code desc,
rsa.err_code_cnt
Results
--columns are not actual. I just abbr. them to fit my output
rank system cnt_all code code_cnt
1 system 6 30 4 11
1 system 6 30 3 14
1 system 6 30 2 5
2 system 2 30 4 10
2 system 2 30 3 15
2 system 2 30 2 5
3 system 1 30 4 10
3 system 1 30 3 10
3 system 1 30 2 10
4 system 5 25 4 11
4 system 5 25 3 10
4 system 5 25 2 4
5 system 4 25 4 9
5 system 4 25 3 10
5 system 4 25 2 6
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673036 is a reply to message #673031] |
Fri, 02 November 2018 13:30 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
Is this what you want?
with system_data (system, err_code, err_code_cnt) as
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all
select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all
select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
)
select
system,
sum(err_code_cnt) over (partition by system) as sum_cnt,
err_code,
err_code_cnt
from system_data
order by 2 desc, system, err_code_cnt desc, err_code;
SYSTEM SUM_CNT ERR_CODE ERR_CODE_CNT
-------- ---------- ---------- ------------
system 1 30 2 10
system 1 30 3 10
system 1 30 4 10
system 2 30 3 15
system 2 30 4 10
system 2 30 2 5
system 6 30 3 14
system 6 30 4 11
system 6 30 2 5
system 4 25 3 10
system 4 25 4 9
system 4 25 2 6
system 5 25 4 11
system 5 25 3 10
system 5 25 2 4
system 3 20 4 10
system 3 20 2 5
system 3 20 3 5
JP
[Updated on: Fri, 02 November 2018 13:31] Report message to a moderator
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673037 is a reply to message #673036] |
Fri, 02 November 2018 14:17 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
Quote:Is this what you want?
No
If you execute my code to the "pivot" part
with system_data as
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all
select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all
select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
),system_pivot AS (
SELECT
system,
MAX(DECODE(sd.err_code,2,sd.err_code_cnt) ) err_code_2_cnt,
MAX(DECODE(sd.err_code,3,sd.err_code_cnt) ) err_code_3_cnt,
MAX(DECODE(sd.err_code,4,sd.err_code_cnt) ) err_code_4_cnt,
SUM(sd.err_code_cnt) err_code_cnt_all
FROM
system_data sd
GROUP BY
system
),ranked_system_data AS (
SELECT
sp.*,
ROW_NUMBER() OVER(
ORDER BY
err_code_cnt_all DESC,
err_code_4_cnt DESC,
err_code_3_cnt DESC,
err_code_2_cnt DESC
) ranked_system
FROM
system_pivot sp
),ranked_systems_all AS (
SELECT
rs.ranked_system,
rs.err_code_cnt_all,
s.*
FROM
ranked_system_data rs,
system_data s
WHERE
rs.system = s.system
ORDER BY
rs.ranked_system
)
select rs.ranked_system, rs.err_code_cnt_all, rs.err_code_4_cnt, rs.err_code_3_cnt, rs.err_code_2_cnt, rs.system from ranked_system_data rs
you'll see
rank cnt_all code_4 code_3 code_2 System
1 30 11 14 5 system 6
2 30 10 15 5 system 2
3 30 10 10 10 system 1
4 25 11 10 4 system 5
5 25 9 10 6 system 4
6 20 10 5 5 system 3
System 6 is ranked first because although "system" 2 and 1 have the same "cnt_all" of 30 it has a "code_4" of 11 while system 2 & 3 have "code_4" of 10
rank cnt_all code_4 System
1 30 11 system 6
2 30 10 system 2
3 30 10 system 1
11 > 10 thus it is #1
System 2 is ranked second because although "system" 6 and 1 have the same "cnt_all" of 30 both "system" 2 + 1 have a "code_4" of 10 but "system" 2 has a "code_3" of 15 while system 1 has "code_3" of 10
rank cnt_all code_4 code_3 System
1 30 11 14 system 6
2 30 10 15 system 2
3 30 10 10 system 1
15 > 10 thus it is #2
and so on..
It's basically, ranking of "cnt_all" first then ranking of "cat"(4->3->2) second,third,fourth
[Updated on: Fri, 02 November 2018 14:28] Report message to a moderator
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673038 is a reply to message #673037] |
Fri, 02 November 2018 14:59 |
|
JPBoileau
Messages: 88 Registered: September 2017
|
Member |
|
|
I couldn't really do it in one single pass, so I did it with 2 passes. In the below, I created a view on the data to simplify things.
select s.system, err_code, err_code_cnt from (
select rownum as position, system from (
select
system,
sum(err_code_cnt) as sum_cnt,
sum(decode(err_code, 4, err_code_cnt, 0)) as sum_cnt_4,
sum(decode(err_code, 3, err_code_cnt, 0)) as sum_cnt_3,
sum(decode(err_code, 2, err_code_cnt, 0)) as sum_cnt_2
from system_data
group by system
order by 2 desc, 3 desc , 4 desc, 5 desc) ) o,
system_data s
where s.system = o.system
order by o.position, err_code desc;
SYSTEM ERR_CODE ERR_CODE_CNT
-------- ---------- ------------
system 6 4 11
system 6 3 14
system 6 2 5
system 2 4 10
system 2 3 15
system 2 2 5
system 1 4 10
system 1 3 10
system 1 2 10
system 5 4 11
system 5 3 10
system 5 2 4
system 4 4 9
system 4 3 10
system 4 2 6
system 3 4 10
system 3 3 5
system 3 2 5
JP
|
|
|
Re: Ranking data by a set of data maybe using analytical function [message #673039 is a reply to message #673038] |
Fri, 02 November 2018 15:24 |
|
lott42_gmail
Messages: 146 Registered: June 2010
|
Senior Member |
|
|
JP
Quote:I couldn't really do it in one single pass, so I did it with 2 passes. In the below, I created a view on the data to simplify things.
Yes, that is correct. Looks like a little simpler than my version. I'm including your version with the "with" statement to simulate the view so others can run it too.
Thank You!!
with system_data as
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all
select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all
select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
)
SELECT
s.system,
err_code,
err_code_cnt
FROM
(
SELECT
ROWNUM AS position,
system
FROM
(
SELECT
system,
SUM(err_code_cnt) AS sum_cnt,
SUM(DECODE(err_code,4,err_code_cnt,0) ) AS sum_cnt_4,
SUM(DECODE(err_code,3,err_code_cnt,0) ) AS sum_cnt_3,
SUM(DECODE(err_code,2,err_code_cnt,0) ) AS sum_cnt_2
FROM
system_data
GROUP BY
system
ORDER BY
2 DESC,
3 DESC,
4 DESC,
5 DESC
)
) o,
system_data s
WHERE
s.system = o.system
ORDER BY
o.position,
err_code DESC;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 18:11:59 CDT 2024
|