Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Analytic Function question

    So I want to look at the value of a column on a current row, and the value of the same column on the most recently entered row, prior to the current. The problem I'm encountering is that for the current row, I'm only interested in those added last month. So, if the record that I'm seeking in the LAG function is prior to last month, it gets rejected from the resultset.

    Code:
    create table check_lag
    (filenr number, code varchar2(2), create_date date);
    
    insert into check_lag values (1,'02',to_date('9/5/2008','MM/DD/YYYY')); -- current
    insert into check_lag values (1,'01',to_date('9/1/2008','MM/DD/YYYY')); --lag record, same month
    insert into check_lag values (2,'02',to_date('9/10/2008','MM/DD/YYYY'));-- current
    insert into check_lag values (2,'01',to_date('8/10/2008','MM/DD/YYYY'));-- lag record prior month
    So this query's results made sense
    Code:
    SELECT FILENR, CODE, 
           LAG( CODE ) OVER( PARTITION BY FILENR ORDER BY FILENR, CREATE_DATE ) AS PRIOR_CODE, 
           CREATE_DATE
    FROM   CHECK_LAG;
    
    FILENR CODE PRIOR_CODE CREATE_DATE
    1      01              9/1/2008
    1      02   01         9/5/2008
    2      01              8/10/2008
    2      02   01         9/10/2008
    But as soon as I add a WHERE clause which set's a boundary around last month, I exclude a LAG record

    Code:
    SELECT FILENR, CODE, 
           LAG( CODE ) OVER( PARTITION BY FILENR ORDER BY FILENR, CREATE_DATE ) AS PRIOR_CODE, 
           CREATE_DATE
    FROM   CHECK_LAG;
    
    FILENR CODE PRIOR_CODE CREATE_DATE
    1      01              9/1/2008
    1      02   01         9/5/2008
    2      02              9/10/2008
    I know that I could push this into an inline view, and provide the WHERE clause with the date range after the inline view is processed, but this is a huge table with an index on the CREATE_DATE, and so the following forces a table scan

    Code:
    SELECT *
    FROM   ( SELECT FILENR, CODE, 
                    LAG( CODE ) OVER( PARTITION BY FILENR ORDER BY FILENR, CREATE_DATE ) AS PRIOR_CODE,
                    CREATE_DATE
            FROM   CHECK_LAG )
    WHERE  CREATE_DATE BETWEEN TO_DATE( '09/01/2008', 'MM/DD/YYYY' )
                           AND TO_DATE( '09/30/2008 23:59:59', 'MM/DD/YYYY HH24:MI:SS' )
    AND    PRIOR_CODE IS NOT NULL;
    
    FILENR CODE PRIOR_CODE CREATE_DATE
    1      02   01         9/5/2008
    2      02   01         9/10/2008
    Is that just the way things are, or am I missing out on another approach?

    Thanks,
    Chuck

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I would say you need the full table scan (to perform the analytic query) because you don't know the dates of the "prior" records. Unless of course you could be sure that the prior record will be no more than (say) 1 month earlier, in which case you can restrict the inner query by that range.

    Alternatively you could rewrite using a scalar subquery to obtain the prior code.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    I should have clarified, both approaches perform a TABLE SCAN. However, with the WHERE clause within the same SELECT as the LAG (and the incorrect results) the query returns in about 2 seconds. With the WHERE clause being performed after the inline view, 45 seconds.

    With the scalar subquery, about 6 seconds.
    --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •