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 Go to next message
lott42_gmail
Messages: 139
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 Go to previous messageGo to next message
BlackSwan
Messages: 26566
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 Go to previous messageGo to next message
Michel Cadot
Messages: 66475
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 Go to previous messageGo to next message
lott42_gmail
Messages: 139
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 Go to previous messageGo to next message
JPBoileau
Messages: 59
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 Go to previous messageGo to next message
lott42_gmail
Messages: 139
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 Go to previous messageGo to next message
JPBoileau
Messages: 59
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 Go to previous message
lott42_gmail
Messages: 139
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;
Previous Topic: SQL query
Next Topic: PL/SQL error Ora-06502
Goto Forum:
  


Current Time: Sun Jul 21 16:25:34 CDT 2019