需要用以前的值替换空值

Question

有必要连接表，但这样就不是空值，而是最后一个已知值​​​被放下并相乘

我有两个桌子“oper”tender“curs”

1. 操作-有2列日期（日期），和(int)
2. curs-有2列“date”（date），“cur”(int)[1]:https://i.stack.imgur.com/og8qy.png

我运行代码:

    SELECT  oper.date AS "Date", 
        oper.sum AS "SUM, USD",
        curs.cur AS "USD/RUB", 
        round(oper.sum * curs.cur,2) AS "SUM, RUB" 
FROM operations
LEFT JOIN currency ON oper.date = cur.date

我得到:

date       | sum.USD | USD/RUB | SUM.RUB
01.01.2020 | 3464    | 71,21   | 246671,44
02.01.2020 | 1091    | 67,99   | 74177,09
03.01.2020 | 2991    | 69,81   | 208801,71
04.01.2020 | 1919    | NULL    | NULL
05.01.2020 | 1560    | NULL    | NULL
06.01.2020 | 2479    | 70,5    | 174769,5
07.01.2020 | 2521    | NULL    | NULL
08.01.2020 | 3382    | NULL    | NULL
09.01.2020 | 3112    | 70,21   | 218493,52
10.01.2020 | 1632    | 69,9    | 114076,8

我想获取（最好是在一个使用COALESCE的请求中）:

date       | sum.USD | USD/RUB | SUM.RUB
01.01.2020 | 3464    | 71,21   | 246671,44
02.01.2020 | 1091    | 67,99   | 74177,09
03.01.2020 | 2991    | 69,81   | 208801,71
04.01.2020 | 1919    | 69,81   | 133965,39
05.01.2020 | 1560    | 69,81   | 108903,6
06.01.2020 | 2479    | 70,5    | 174769,5
07.01.2020 | 2521    | 70,5    | 177730,5
08.01.2020 | 3382    | 70,5    | 238431
09.01.2020 | 3112    | 70,21   | 218493,52
10.01.2020 | 1632    | 69,9    | 114076,8

Answer 1

在MySQL8+上，我们可以在这里使用last_value分析函数:

SELECT
    oper.date AS "Date", 
    oper.sum AS "SUM, USD",
    LAST_VALUE(curs.cur IGNORE NULLS) OVER (ORDER BY oper.date) AS "USD/RUB", 
    ROUND(oper.sum *
          LAST_VALUE(curs.cur IGNORE NULLS) OVER (ORDER BY oper.date), 2) AS "SUM, RUB" 
FROM operations oper
LEFT JOIN currency curs
    ON oper.rep_date = curs.date;

在MySQL的早期版本上，我们可以尝试使用相关子查询来填补空白:

SELECT
    oper.date AS "Date", 
    oper.sum AS "SUM, USD",
    (SELECT curs.cur FROM currency curs
     WHERE curs.date <= oper.date AND curs.cur IS NOT NULL
     ORDER BY curs.date DESC LIMIT 1) "USD/RUB", 
    ROUND(oper.sum *
          (SELECT curs.cur FROM currency curs
           WHERE curs.date <= oper.date AND curs.cur IS NOT NULL
           ORDER BY curs.date DESC LIMIT 1), 2) AS "SUM, RUB" 
FROM operations oper
ORDER BY date;