时间戳

首页 > 解决方案 > 获取之前的非空值

sql - 获取之前的非空值

问题描述

我有一张 t0 费率表

例子 ：

id   code   dev   period   rate    
4937   TRY   EUR   201812   0.1649    
4940   AED   EUR   201812   0.2392    
4857   USD   EUR   201812   1.4259    
4845   CNY   EUR   201811   0.1678
4851   AED   EUR   201811   0.3949    
4960   AED   EUR   201810   1.1949
4886   TRY   EUR   201809   1.128    
4986   RSD   EUR   201808   1.006

我想从表 t1 中获取每个日期的 t0 速率

我尝试了 last_value 函数，但它不起作用。你有另一个想法吗？

select t3.id
   ,t0.period
   ,t1.date 
   ,t0.code
   ,(CASE WHEN t0.period is null THEN last_value(t0.rate ignore nulls) OVER (order by t1.date ROWS BETWEEN  UNBOUNDED PRECEDING AND 1 PRECEDING) ELSE t0.rate END) as rate 
from t1
 INNER JOIN t2 ON t2.id = t1.id
 LEFT OUTER JOIN t3 ON t3.id  = t1.id3
 LEFT OUTER JOIN t0 ON t0.code = t1.code
                        AND  t0.period < to_char(t1.date, 'YYYYMM')

期望的结果：

案例 0：

如果 t0.period = to_char(t1.date, 'YYYYMM') ，所以我想要一行

15|201812|201812|TRY|0.1649    
21|201808|201808|RSD|1.006

情况1 ：

如果 t0.period 为空，那么我搜索最后一个不为空的利率：

2|201812|201911|AED|0.2392    
45|201812|201910|USD|1.4259

标签： sqloracle11g

解决方案

您还可以使用内部连接和分析功能来实现这一点 - 例如：

WITH t0 AS (SELECT 4937 ID, 'TRY' CODE, 'EUR' dev, 201812 period, 0.1649 rate FROM dual UNION ALL
            SELECT 4940 ID, 'AED' CODE, 'EUR' dev, 201812 period, 0.2392 rate FROM dual UNION ALL
            SELECT 4857 ID, 'USD' CODE, 'EUR' dev, 201812 period, 1.4259 rate FROM dual UNION ALL
            SELECT 4845 ID, 'CNY' CODE, 'EUR' dev, 201811 period, 0.1678 rate FROM dual UNION ALL
            SELECT 4851 ID, 'AED' CODE, 'EUR' dev, 201811 period, 0.3949 rate FROM dual UNION ALL
            SELECT 4960 ID, 'AED' CODE, 'EUR' dev, 201810 period, 1.1949 rate FROM dual UNION ALL
            SELECT 4886 ID, 'TRY' CODE, 'EUR' dev, 201809 period, 1.128 rate FROM dual UNION ALL
            SELECT 4986 ID, 'RSD' CODE, 'EUR' dev, 201808 period, 1.006 rate FROM dual),
     t1 AS (SELECT 15 ID, 201812 dt, 'TRY' CODE FROM dual UNION ALL
            SELECT 21 ID, 201808 dt, 'RSD' CODE FROM dual UNION ALL
            SELECT 2 ID, 201911 dt, 'AED' CODE FROM dual UNION ALL
            SELECT 45 ID, 201910 dt, 'USD' CODE FROM dual)
SELECT ID,
       period,
       dt,
       CODE,
       rate
FROM   (
SELECT t1.id,
     t0.period,
     t1.dt,
     t1.code,
     t0.rate,
       MAX(t0.period) OVER (PARTITION BY t1.id) max_period-- assuming t1.id is the primary or unique key for t1
FROM   t0
       INNER JOIN t1 ON t0.period <= t1.dt AND t0.code = t1.code)
WHERE  period = max_period;

        ID     PERIOD         DT CODE       RATE
---------- ---------- ---------- ---- ----------
         2     201812     201911 AED      0.2392
        15     201812     201812 TRY      0.1649
        21     201808     201808 RSD       1.006
        45     201812     201910 USD      1.4259

如果我是你，我会检查哪个答案对你的数据和结构等最有效。

推荐阅读