Skip to Main Content
  • Questions
  • Get Cotization of Product at Date - CTE with Window Functions Issue

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Juan Fran.

Asked: March 31, 2021 - 10:55 am UTC

Last updated: March 31, 2021 - 1:34 pm UTC

Version: 11g, 11.2.0.1.0

Viewed 100+ times

You Asked

Hello,
I am trying to get the cotization of a product at a given date.
I have made a table:
CREATE TABLE PRICES ( NAME, VALIDFROM, PRICE ) AS
  SELECT 'GOLD', DATE '2017-01-01', 36000 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-09-25', 42000 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-10-03', 40800 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2017-12-12', 39500 FROM DUAL UNION ALL
  SELECT 'GOLD', DATE '2019-09-03', 49700 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2017-01-21', 28000 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2018-01-23', 23000 FROM DUAL UNION ALL
  SELECT 'SILVER', DATE '2018-02-26', 17000 FROM DUAL;


There is no problem If i solve it with a subquery, like this:

SQL>
SELECT * FROM
    (
        SELECT
            NAME
            ,VALIDFROM
            ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
        FROM PRICES
        WHERE VALIDFROM <=DATE '2017-10-25'
    )        
WHERE RNK=1
;


NAME VALIDFROM RNK
----- ----------------- ---
GOLD 17-10-03 00:00:00 1
SILVER 17-01-21 00:00:00 1

But I would like to encapsulate, as I have to use it in a bigger query, in a list with thousands of results.

I have tried CTE and window function, like this:

SQL>
WITH CTE_RNK (NAME, VALIDFROM, RNK) AS (
    SELECT
        NAME
        ,VALIDFROM
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
    FROM PRICES
)
SELECT 
    *
FROM CTE_RNK
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=1;


There is no results.

It looks like it calculates the ROW_NUMBER() before applying the date filter.

I have tried other approaches with window functions and also using a view, that would be a cleaner approach, with the same result.

There is any other approach to solve this with encapsulation, preferably with a view?

Here a view approach:
SQL>
CREATE VIEW PRICES_AT_GIVEN_DATE (NAME, VALIDFROM, RNK) AS 
    (SELECT
        NAME
        ,VALIDFROM
        ,ROW_NUMBER() OVER (PARTITION BY NAME ORDER BY (VALIDFROM) DESC) AS RNK
    FROM PRICES)
    ;
SELECT 
    *
FROM PRICES_AT_GIVEN_DATE
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=1
;    
SELECT 
    *
FROM PRICES_AT_GIVEN_DATE
WHERE 
    VALIDFROM <=DATE '2017-10-25'
    AND RNK=3
;


with LiveSQL Test Case:

and we said...

You're right, the row number is calculated before applying the date filter.

To get the correct result, you need to filter in the same subquery with the where clause. e.g.:

with cte_rnk (name, validfrom, rnk) as ( 
  select 
      name 
      ,validfrom 
      ,row_number() over (partition by name order by (validfrom) desc) as rnk 
  from prices 
  where validfrom <= date '2017-10-25' 
) 
select  
    * 
from   cte_rnk 
where  rnk=1;

NAME      VALIDFROM              RNK   
GOLD      03-OCT-2017 00:00:00     1 
SILVER    21-JAN-2017 00:00:00     1 


There is no general case solution that works with views.

From 19c you can use SQL macros, these allow you to create parameterizable "views" like this:

create or replace function filter_prices ( 
  max_valid_date date 
) 
  return clob sql_macro as
  stmt clob;
begin

  stmt := q'!select 
      name 
      ,validfrom 
      ,row_number() over (partition by name order by (validfrom) desc) as rnk 
  from prices 
  where validfrom <= max_valid_date!';

  return stmt;
end filter_prices;
/

select * 
from   filter_prices ( date '2017-10-25' )
where  rnk = 1;

NAME      VALIDFROM              RNK   
GOLD      03-OCT-2017 00:00:00     1 
SILVER    21-JAN-2017 00:00:00     1 


Find out more about these at https://blogs.oracle.com/datawarehousing/sql-macros-have-arrived-in-autonomous-database

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.