Home » SQL & PL/SQL » SQL & PL/SQL » Identifying Trends via a SELECT query
Identifying Trends via a SELECT query [message #186036] Sat, 05 August 2006 00:27 Go to next message
jameswise
Messages: 8
Registered: August 2006
Junior Member
I have some experience with SELECT queries in Oracle but I've run into a situation I don't know how to handle and would appreciate anyone's ideas on how to do this if possible.

I have an Oracle 9 table which includes the following fields

c_starttime
c_endtime
c_machine
c_count01
c_count02
c_count03

The worst "Position" numbers for a given machine (c_machine) and day (c_starttime) are stored in c_count01 - c_count03. I would like to be able to run a query to see the worst positions (worst being the positions with the highest number of records) by machine over a given period of time (c_starttime >= entered value and c_endtime <= entered value) including how many records included these as problems. A bad position could be in any of the count values.

Example:
c_starttime	c_machine	c_count01	c_count02	c_count03
1-Jul		001		43		98		12
2-Jul		001		18		6		54
3-Jul		001		19		43		54
4-Jul		001		54		3		17
1-Jul		002		9		14		12
2-Jul		002		14		2		7
3-Jul		002		6		12		7
4-Jul		002		6		18		2

Desired Results:
If a query was run where c_starttime >= 1-Jul and c_endtime <=4 Jul, 
I would want to see the following:

		Machine		Worst 1		2nd Worst	3rd Worst
		001		54 - 3 Times	43 - 2 Times	6 - 1 Time
		002		7 - 2 Times	12 - 2 Times	14 - 2 Times		

Is there any way a query could be written to provide these results?
Re: Identifying Trends via a SELECT query [message #186037 is a reply to message #186036] Sat, 05 August 2006 01:13 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Either I misunderstand you requirements, or your output is incorrect. The worst for machine 001 is 98, not 54.
Anyway here is my code:
column "Worst 1" format a20
column "2nd Worst" format a20
column "3rd Worst" format a20

drop table faq;
create table faq
( c_starttime date
, c_machine   varchar2(3)
, c_count01   number
, c_count02   number
, c_count03   number
)
/
insert into faq values(to_date('01-Jul-2006', 'dd-Mon-yyyy'), '001', 43, 98, 12);
insert into faq values(to_date('02-Jul-2006', 'dd-Mon-yyyy'), '001', 18, 6, 54);
insert into faq values(to_date('03-Jul-2006', 'dd-Mon-yyyy'), '001', 19, 43, 54);
insert into faq values(to_date('04-Jul-2006', 'dd-Mon-yyyy'), '001', 54, 3, 17);
insert into faq values(to_date('01-Jul-2006', 'dd-Mon-yyyy'), '002', 9, 14, 12);
insert into faq values(to_date('02-Jul-2006', 'dd-Mon-yyyy'), '002', 14, 2, 7);
insert into faq values(to_date('03-Jul-2006', 'dd-Mon-yyyy'), '002', 6, 12, 7);
insert into faq values(to_date('04-Jul-2006', 'dd-Mon-yyyy'), '002', 6, 18, 2);


select c_machine
,      max(decode(the_inverted_rank, 1, to_char(c_count)||' - '||to_char(the_count)||' Times')) "Worst 1"
,      max(decode(the_inverted_rank, 2, to_char(c_count)||' - '||to_char(the_count)||' Times')) "2nd Worst"
,      max(decode(the_inverted_rank, 3, to_char(c_count)||' - '||to_char(the_count)||' Times')) "3rd Worst"
from   (select c_machine
        ,      c_count
        ,      count(*) the_count
        ,      the_inverted_rank
        from   (select dense_rank() over (partition by c_machine order by c_count desc) the_inverted_rank
                ,      c_count
                ,      c_machine
                ,      c_starttime
                from   (select c_starttime
                        ,      c_machine
                        ,      c_count01 as c_count
                        from   faq
                        union  all
                        select c_starttime
                        ,      c_machine
                        ,      c_count02
                        from   faq
                        union  all
                        select c_starttime
                        ,      c_machine
                        ,      c_count03
                        from   faq
                       )
                )
        where   the_inverted_rank <= 3
        group   by c_machine
        ,       c_count
        ,       the_inverted_rank
       )
group  by c_machine
/

The idea I followed was (read from inner to outer query)
- first get the three c_count columns on the same 'level'
- rank these columns using dense_rank
- do a count per rank per machine for the 3 worst counts
- pivot the rows back to get one line per machine

And this is the output
SQL> 

Table dropped

Table created

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

1 row inserted

C_MACHINE Worst 1              2nd Worst            3rd Worst
--------- -------------------- -------------------- --------------------
002       18 - 1 Times         14 - 2 Times         12 - 2 Times
001       98 - 1 Times         54 - 3 Times         43 - 2 Times


I do have the feeling it could be done in less steps, so experts, please comment on it.
Re: Identifying Trends via a SELECT query [message #186055 is a reply to message #186036] Sat, 05 August 2006 06:42 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
A variant of Frank's solution with date filter, and counts per rank per machine calculated for each rank (which reduces an inner query).

SQL> select c_machine
  2  ,      max(decode(the_inverted_rank, 1, to_char(c_count)||' - '||to_char(the_count)||' Times')) "Worst 1"
  3  ,      max(decode(the_inverted_rank, 2, to_char(c_count)||' - '||to_char(the_count)||' Times')) "2nd Worst"
  4  ,      max(decode(the_inverted_rank, 3, to_char(c_count)||' - '||to_char(the_count)||' Times')) "3rd Worst"
  5  from   (select c_machine
  6          ,      c_count
  7          ,      dense_rank() over (partition by c_machine order by c_count desc) the_inverted_rank
  8          ,      count(c_count) over (partition by c_machine, c_count) the_count
  9          from   (select c_machine
 10                  ,      c_count01 as c_count
 11                  from   faq
 12                  where  c_starttime between to_date('01-JUL-06', 'DD-MON-YY') and to_date('04-JUL-06', 'DD-MON-YY')
 13                  union  all
 14                  select c_machine
 15                  ,      c_count02
 16                  from   faq
 17                  where  c_starttime between to_date('01-JUL-06', 'DD-MON-YY') and to_date('04-JUL-06', 'DD-MON-YY')
 18                  union  all
 19                  select c_machine
 20                  ,      c_count03
 21                  from   faq
 22                  where  c_starttime between to_date('01-JUL-06', 'DD-MON-YY') and to_date('04-JUL-06', 'DD-MON-YY')
 23                  )
 24         )
 25  where   the_inverted_rank <= 3
 26  group  by c_machine
 27  /

C_M Worst 1              2nd Worst            3rd Worst
--- -------------------- -------------------- --------------------
001 98 - 1 Times         54 - 3 Times         43 - 2 Times
002 18 - 1 Times         14 - 2 Times         12 - 2 Times
Re: Identifying Trends via a SELECT query [message #186079 is a reply to message #186055] Sat, 05 August 2006 10:17 Go to previous messageGo to next message
jameswise
Messages: 8
Registered: August 2006
Junior Member
I appreciate everyone's help. It looks like I didn't explain this quite right though. The "Worst 1" field should be the "position number" from any of the count 1-3 fields with the highest number of entries for that machine over the given time period (records). So, for machine 001, position 54 is the worst because there are three occurrences of it for that machine (2 occurrences in count_03 and 1 occurrence in count_01). Similarly, for machine 002, the three worst positions each had two occurrences. Positions 2, 6, 7, 12, 14. I see I made a mistake in the results I listed for this machine. I really should have listed worst 1 as 2, 2nd worst as 6, and 3rd worst as 7. This way I'm consistent and taking the position numbers with the most occurrences but in the case of several with the same number, I use the lowest position numbers for the top three.

I hope this helps to clear up what I'm trying to do and I really appreciate everyone's help. I'll work with the queries that have already been suggested though to see if I can use them to solve my problem but I thought the new information above might better describe what I was trying to accomplish.
Re: Identifying Trends via a SELECT query [message #186087 is a reply to message #186079] Sat, 05 August 2006 12:28 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
I'm not sure I fully understand the requirement, but not that you can transform the data into a possibly more convenient forrmat like this:

SELECT c_machine
     , c_starttime, c_endtime
     , column_value AS c_count
FROM   ( SELECT c_starttime
              , LEAD(c_starttime) OVER(ORDER BY c_starttime) - 1/86400 AS c_endtime
              , c_machine
              , c_count01
              , c_count02
              , c_count03
         FROM   faq ) t
     , TABLE(integer_tt(t.c_count01, t.c_count02, t.c_count03));


C_MACHINE  C_STARTTIME  C_ENDTIME   C_COUNT
---------- ------------ ----------- -------
001        01-JUL-06    30-JUN-06        43
001        01-JUL-06    30-JUN-06        98
001        01-JUL-06    30-JUN-06        12
002        01-JUL-06    01-JUL-06         9
002        01-JUL-06    01-JUL-06        14
002        01-JUL-06    01-JUL-06        12
001        02-JUL-06    01-JUL-06        18
001        02-JUL-06    01-JUL-06         7
001        02-JUL-06    01-JUL-06        54
002        02-JUL-06    02-JUL-06        14
002        02-JUL-06    02-JUL-06         2
002        02-JUL-06    02-JUL-06         7
001        03-JUL-06    02-JUL-06        19
001        03-JUL-06    02-JUL-06        43
001        03-JUL-06    02-JUL-06        54
002        03-JUL-06    03-JUL-06         6
002        03-JUL-06    03-JUL-06        12
002        03-JUL-06    03-JUL-06         7
001        04-JUL-06    03-JUL-06        54
001        04-JUL-06    03-JUL-06         3
001        04-JUL-06    03-JUL-06        17
002        04-JUL-06    -                 6
002        04-JUL-06    -                18
002        04-JUL-06    -                 2

For INTEGER_TT substitute your own friendly neighbourhood integer collection type.

[Updated on: Sat, 05 August 2006 12:30]

Report message to a moderator

Re: Identifying Trends via a SELECT query [message #186110 is a reply to message #186087] Sat, 05 August 2006 23:24 Go to previous message
jameswise
Messages: 8
Registered: August 2006
Junior Member
Once again, I appreciate everyone’s help. This issue is solved now as I was able to use a slight variation on the query posted by hobbes to get what I wanted. I just had to replace the lines below as follows:
  7          ,      row_number() over (partition by c_machine order by count(c_count) desc) the_inverted_rank
  8          ,      sum(count(c_count)) over (partition by c_machine, c_count) the_count
 24  group by c_machine, c_count


I used "row_number" instead of "dense_rack" to get the behavior I wanted if there were ties.
Previous Topic: Using %ROWTYPE in a Function with a cursor
Next Topic: about pro c/c++
Goto Forum:
  


Current Time: Fri Dec 09 21:25:51 CST 2016

Total time taken to generate the page: 0.10753 seconds