Oracle 用窗口函数问题获取产品在日期的代码化

你好，
我正在尝试在给定日期将产品进行编码。
我做了一张表:

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;

如果我用一个子查询解决它没有问题，就像这样:

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
;

来自RNK的名称验证
-
黄金17-10-03 00:00:00 1
银17-01-21 00:00:00 1

但是我想封装，因为我必须在一个更大的查询中使用它，在一个包含数千个结果的列表中。

我试过CTE和窗口功能，像这样:

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;

没有结果。

它看起来像它在应用日期过滤器之前计算ROW_NUMBER()。

我已经尝试了其他方法的窗口函数，也使用了一个视图，这将是一个更干净的方法，具有相同的结果。

还有其他方法可以用封装解决这个问题，最好是用视图？

这里有一个视图方法:
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
;

你是对的，行号是在应用日期过滤器之前计算的。

要获得正确的结果，您需要使用where子句在同一子查询中进行过滤。例如:

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 

没有适用于视图的通用案例解决方案。

从19c可以使用SQL宏，这些允许您创建可参数化的 “视图”，如下所示:

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 

在以下位置了解更多关于这些的信息https://blogs.oracle.com/datawarehousing/sql-macros-have-arrived-in-autonomous-database