Oracle-L: RE: Tricky query question

From: Larry Elkins <elkinsl_at_flash.net>
Date: Fri, 24 Jan 2003 02:49:03 -0800
Message-ID: <F001.0053967E.20030124024903@fatcity.com>

Mark,

You're on the right track thinking of the analytic functions (8.1.6 and above), which among many things provide the ability to compare between rows without having to do a self-join or multiple passes. Take a look at the lag and lead functions.

Here's and example, making a single pass (plus the cost of the sort), showing the use of the LAG function to get the status from the prior row:

SQL> l
1 select primarykey,

  2         status,
  3         dateinserted,
  4         LAG(status,1) OVER (PARTITION BY primarykey
  5                             ORDER BY dateinserted) prior_status

6* from tricky
SQL> / PRIMARYKEY S DATEINSER P

---------- - --------- -
         1 A 01-JAN-03
         1 A 02-JAN-03 A
         1 C 03-JAN-03 A
         1 B 04-JAN-03 C
         1 B 05-JAN-03 B
         1 B 06-JAN-03 B
         1 D 07-JAN-03 B
         1 D 08-JAN-03 D
         1 E 09-JAN-03 D
         1 E 10-JAN-03 E
         1 E 11-JAN-03 E
         2 A 01-JAN-03
         2 A 02-JAN-03 A
         2 B 03-JAN-03 A
         2 B 04-JAN-03 B
         2 C 05-JAN-03 B

And then it's a simple case of wrapping it in an in-line view and doing a comparison.

SQL> Select *
2 From (select primarykey,

  3               status,
  4               dateinserted,
  5               LAG(status,1) OVER (PARTITION BY primarykey
  6                                   ORDER BY dateinserted) prior_status
  7        from   tricky) A

8 where status <> prior_status
9 /

PRIMARYKEY S DATEINSER P

---------- - --------- -
         1 C 03-JAN-03 A
         1 B 04-JAN-03 C
         1 D 07-JAN-03 B
         1 E 09-JAN-03 D
         2 B 03-JAN-03 A
         2 C 05-JAN-03 B

Note that I used the PARTITION BY since I was processing multiple keys (1 and 2). Also, if your requirement is such that the first occurrence of a key should be reported, include "or prior_status is null" or use an NVL function.

The elimination of the self join or multiple passes, or the elimination of having to drop to cursor row at a time based processing to do the comparison between rows, can result in a huge performance boost. For example, take another common type of query when dealing with historical data:

  1 select *
  2 from tricky a
  3 where dateinserted = (select max(dateinserted)

  4                        from   tricky b
  5*                       where  a.primarykey = b.primarykey)

SQL> / PRIMARYKEY S DATEINSER

---------- - ---------
         1 E 11-JAN-03
         2 C 05-JAN-03

With the use of analytic functions, ROW_NUMBER in this case, you can do something like this:

1 Select a.primarykey, a.status, a.dateinserted 2 From (Select tricky.*, ROW_NUMBER () OVER (PARTITION BY primarykey

  3                                             ORDER BY dateinserted desc)
rn
  4        from   tricky) a

5* Where rn=1
SQL> / PRIMARYKEY S DATEINSER

---------- - ---------
         1 E 11-JAN-03
         2 C 05-JAN-03

And you have now eliminated the need for the sub-query and extra accesses on the table. Anyway, sorry for the length, but sometimes examples help make things clearer.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Mark
> Richard
> Sent: Thursday, January 23, 2003 9:34 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Tricky query question
>
>
> Hi All,
>
> This is a question for those who like writing tricky queries as a single
> SQL...
>
> Assume I have a table with the following structure (imaginary
> table to keep
> example simple):
>
> primarykey number(8)
> status char(1)
> dateinserted date
> <many other fields which are insignificant>
>
> A row is inserted into this table any time one, or more, fields change for
> the record. What I'm trying to determine is the "dateinserted" for each
> time the value of "status" changed - remembering that there might be 50
> records for the primary key and five of those include a change in status -
> I want the dates of those five records (plus the value of status at each
> date).
>
> Is there any way to achieve this within a single SQL statement? I realise
> that I could cursor through each record in date order and use a
> variable to
> store the previous value of status - but I'd love to compress this to a
> single statement. I've seen the syntax "OVER" used in some queries and
> suspect this may be useful but I really don't understand it
> enough to know.
>
> Thanks in advance if you can help me on this one,
>
> Mark.
>
> PS: Sorry if this appears more than once - I've been getting mail server
> failures all day

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Fri Jan 24 2003 - 04:49:03 CST