Home » SQL & PL/SQL » SQL & PL/SQL » Help needed on an Analytical Query
Help needed on an Analytical Query [message #312836] Wed, 09 April 2008 21:02 Go to next message
rasa
Messages: 45
Registered: February 2006
Member
This is what my requirement is.

If my data is this:
P_ID	P_DTM	               P_STATUS
1	3/10/2008 1:15:00 AM	0
1	3/10/2008 1:30:00 AM	1
1	3/10/2008 1:45:00 AM	2
1	3/10/2008 2:00:00 AM	4


I need my P_STATUS to be the MINIMUM of the non-zero original P_STATUS column for each HOUR as shown below.
P_ID	P_DTM	               P_STATUS
1	3/10/2008 1:15:00 AM	1
1	3/10/2008 1:30:00 AM	1
1	3/10/2008 1:45:00 AM	1
1	3/10/2008 2:00:00 AM	1


But, if I do a query like this, I will get 0, because 0 is the least. Somehow, I need to exclude 0 if it is present and just include the non-zero values for the MIN function for that "window."

How do I do it? My current query doesn't work. Here it is:

SELECT P_ID, P_DTM, MIN(P_STATUS) OVER (PARTITION BY P_ID ORDER BY P_DTM) "P_STATUS"
FROM TEST_THROWAWAY;


Here are the Test-Scripts that are needed:

CREATE TABLE TEST_THROWAWAY(P_ID NUMBER, P_DTM DATE, P_STATUS NUMBER);
INSERT INTO TEST_THROWAWAY VALUES (1, TIMESTAMP '2008-03-10 01:15:00',  0);
INSERT INTO TEST_THROWAWAY VALUES (1, TIMESTAMP '2008-03-10 01:30:00',  1);
INSERT INTO TEST_THROWAWAY VALUES (1, TIMESTAMP '2008-03-10 01:45:00',  2);
INSERT INTO TEST_THROWAWAY VALUES (1, TIMESTAMP '2008-03-10 02:00:00',  4);
COMMIT;


Thanks.
Re: Help needed on an Analytical Query [message #312839 is a reply to message #312836] Wed, 09 April 2008 21:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
It may well be possible to do this with analytic functions, although it doesn't seem likely. If it is possible, it will probably result in some difficult-to-read, non-intuitive code.

Since Analytic Functions are frequently no faster than self-joins (nay, they are often slower), why don't you join the table to a GROUP BY inline view that filters the zeros.

That would make for a reasonably performant, easy-to-read solution.

Ross Leishman
Re: Help needed on an Analytical Query [message #312845 is a reply to message #312839] Wed, 09 April 2008 22:16 Go to previous messageGo to next message
rasa
Messages: 45
Registered: February 2006
Member
Thanks rleishman.

I hve resolved this conundrum by reasoning as follows:

MIN(P_STATUS) OVER (PARTITION BY....ORDER BY...) "P_STATUS"


This fails because, 0 will always be the minimum and it should be excluded. Right? Well, that's because the "MIN" function is Oracle-written function. What if I can write my own "MIN" function that would take a bunch of values as offered by the WINDOW of the analytical query? Meaning, for every 4 values passed, if I can "trap" those numbers and exclude the 0 and then do a MIN on the other numbers to return it, that would resolve the problem.

So, I went ahead and implemented a solution of "User-Defined/Custom Aggregation Function" as indicated here:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1005029



Re: Help needed on an Analytical Query [message #312882 is a reply to message #312836] Thu, 10 April 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select p_id, p_dtm, 
  2         min(case when p_status > 0 then p_status end)
  3           over (partition by p_id) p_status
  4  from TEST_THROWAWAY
  5  /
      P_ID P_DTM                 P_STATUS
---------- ------------------- ----------
         1 10/03/2008 01:15:00          1
         1 10/03/2008 02:00:00          1
         1 10/03/2008 01:45:00          1
         1 10/03/2008 01:30:00          1

4 rows selected.

Regards
Michel
Re: Help needed on an Analytical Query [message #312937 is a reply to message #312882] Thu, 10 April 2008 03:04 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Well, there you go. Possible and not so difficult after all.

Although I would go for NULLIF() instead of CASE
SQL> select p_id, p_dtm, 
  2         min(nullif(p_status,0)))
  3           over (partition by p_id) p_status
  4  from TEST_THROWAWAY


Ross Leishman
Re: Help needed on an Analytical Query [message #312959 is a reply to message #312937] Thu, 10 April 2008 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, good idea, I always forgot these useful NULL functions.

Regards
Michel
Re: Help needed on an Analytical Query [message #312977 is a reply to message #312836] Thu, 10 April 2008 04:36 Go to previous messageGo to next message
mohdbfaq
Messages: 5
Registered: April 2008
Junior Member
SELECT P_ID, P_DTM, MIN(P_STATUS) OVER (PARTITION BY P_ID ORDER BY decode(nvl(P_DTM,0),0,99999,P_DTM)) "P_STATUS" FROM TEST_THROWAWAY;


Mohan

[Updated on: Thu, 10 April 2008 05:23] by Moderator

Report message to a moderator

Re: Help needed on an Analytical Query [message #312993 is a reply to message #312977] Thu, 10 April 2008 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read OraFAQ Forum Guide.

In addition "order by" is useless with min. Min value does not change with the order in which you look at the rows.

Regards
Michel
Re: Help needed on an Analytical Query [message #312999 is a reply to message #312993] Thu, 10 April 2008 05:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The order by does make a difference though.
The MIN value is computed over the window for the analytic function. If you don't specify one, the default window is 'UNBOUNDED PRECEDING TO CURRENT ROW'
So, by putting the lowest value at the end, the Min function will not see it until the very last row of the query.

Once you've corrected the script and changed p_dtm into p_status, you get this:
SQL> SELECT P_ID, P_DTM
  2       , MIN(P_STATUS) OVER (PARTITION BY P_ID ORDER BY decode(nvl(P_STATUS,0),0,99999,P_STATUS))
 P_STATUS 
  3  FROM TEST_THROWAWAY;

      P_ID P_DTM       P_STATUS
---------- --------- ----------
         1 10-MAR-08          1
         1 10-MAR-08          1
         1 10-MAR-08          1
         1 10-MAR-08          0


Re: Help needed on an Analytical Query [message #313009 is a reply to message #312999] Thu, 10 April 2008 06:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The MIN value is computed over the window for the analytic function. If you don't specify one, the default window is 'UNBOUNDED PRECEDING TO CURRENT ROW'

Ooops! Embarassed Writing too fast, brain dumb, need amphet...

Regards
Michel

Re: Help needed on an Analytical Query [message #313026 is a reply to message #313009] Thu, 10 April 2008 07:21 Go to previous message
rasa
Messages: 45
Registered: February 2006
Member
Thanks a lot, folks.

However, I still need a 0 if all the sub-intervals were only 0. So, I modified the solution to this and it works.

SELECT P_ID, P_DTM, nvl(MIN(NULLIF(P_STATUS, 0)) OVER(PARTITION BY P_ID),0) P_STATUS
FROM   TEST_THROWAWAY


I am feeling humbled for the straight-forward approach. I had a mental-block last night as I was working this through as although I went the path of MIN(CASE...) statements, I failed on two counts:

(1) I should have given the CASE without an ELSE as one of the solutions pointed out.

(2) I had never used NULLIF (Duh!) and that function could have made life lot easier.

I learnt a lot. Thanks, once again to everybody.
Previous Topic: date format not working
Next Topic: FROM TABLE, SELECT
Goto Forum:
  


Current Time: Wed Dec 07 04:36:50 CST 2016

Total time taken to generate the page: 4.13461 seconds