如果当前为空，则插入上一个日期的信息 Oracle

Question

>我有一个包含以下信息的表格

Table1 是一个子查询，为了简化起见，我将只使用结果表：

| Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+---------+---------+--------+-------------------+
| 1          | 1       | USD     | 50     | 03/01/2017        |
| 1          | 2       | EUR     | 25     | 01/01/2017        |
| 1          | 3       | USD     | 51     | 01/01/2017        |
| 1          | 1       | GBP     | 45     | 01/01/2017        |
| 1          | 2       | USD     | 65     | 02/01/2017        |

金额是该日期某个部门指定货币的账户日结束时的金额。更重要的是，同一帐户可能会因货币和Dept_ID(相同的Account_no但不同的货币和/或部门 ID(而有所不同，我的意思是 PK 是Account_no、Dept_ID和货币的组合。

我正在尝试将该表附加到日期表，该表每天都具有某个指定范围：

日期表：

|   Date_1   |
+------------+
| 01/01/2017 |
| 02/01/2017 |
| 03/01/2017 |
| 04/01/2017 |
| 05/01/2017 |
...

预期结果是：

|   Date_1   | Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+------------+---------+---------+--------+-------------------+
| 01/01/2017 | 1          | 1       | USD     | 0      |                   |
| 01/01/2017 | 1          | 2       | USD     | 0      |                   |
| 01/01/2017 | 1          | 2       | EUR     | 25     | 01/01/2017        |
| 01/01/2017 | 1          | 3       | USD     | 51     | 01/01/2017        |
| 01/01/2017 | 1          | 1       | GBP     | 45     | 01/01/2017        |
| 02/01/2017 | 1          | 2       | USD     | 65     | 02/01/2017        |
| 02/01/2017 | 1          | 2       | EUR     | 25     |                   |
| 02/01/2017 | 1          | 3       | USD     | 51     |                   |
| 02/01/2017 | 1          | 1       | GBP     | 45     |                   |
| 02/01/2017 | 1          | 1       | USD     | 0      |                   |
| 03/01/2017 | 1          | 1       | USD     | 50     | 03/01/2017        |
| 03/01/2017 | 1          | 1       | GBP     | 45     |                   |
| 03/01/2017 | 1          | 3       | USD     | 51     |                   |
| 03/01/2017 | 1          | 2       | EUR     | 25     |                   |
| 03/01/2017 | 1          | 2       | USD     | 65     |                   |

因此，对于日期表中的每个日期，我将从表1中获得信息，如果缺少该信息，则应选择前几天的信息。我已经完成了左连接的查询，但不知道如何将前一天的数据填充到缺失的字段中

SELECT * FROM DATES A LEFT JOIN TABLE1 B ON A.DATE_1 = B.DATE_2;

我得到

|   Date_1   | Account_No | Dept_ID | Currency| Amount | Date_2(dd/mm/yyyy)|
+------------+------------+---------+---------+--------+-------------------+
| 01/01/2017 | 1          | 2       | EUR     | 25     | 01/01/2017        |
| 01/01/2017 | 1          | 3       | USD     | 51     | 01/01/2017        |
| 01/01/2017 | 1          | 1       | GBP     | 45     | 01/01/2017        |
| 02/01/2017 | 1          | 2       | USD     | 65     | 02/01/2017        |
| 03/01/2017 | 1          | 1       | USD     | 50     | 03/01/2017        |
| 04/01/2017 |            |         |         |        |                   |
...

有关如何进行的建议不胜感激

Answer 1

您还可以使用分区外部连接和 case 语句来执行此操作，该语句决定在没有当前金额的情况下是放置当前金额还是以前的可用金额，如下所示：

WITH table1 AS (SELECT 1 account_no, 1 dept_id, 'USD' currency, 50 amount, to_date('03/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
SELECT 1 account_no, 2 dept_id, 'EUR' currency, 25 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
SELECT 1 account_no, 3 dept_id, 'USD' currency, 51 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
SELECT 1 account_no, 1 dept_id, 'GBP' currency, 45 amount, to_date('01/01/2017', 'dd/mm/yyyy') date_2 FROM dual UNION ALL
SELECT 1 account_no, 2 dept_id, 'USD' currency, 65 amount, to_date('02/01/2017', 'dd/mm/yyyy') date_2 FROM dual),
dates AS (SELECT to_date('01/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
SELECT to_date('02/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
SELECT to_date('03/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
SELECT to_date('04/01/2017', 'dd/mm/yyyy') date_1 FROM dual UNION ALL
SELECT to_date('05/01/2017', 'dd/mm/yyyy') date_1 FROM dual)
SELECT d.date_1,
t1.account_no,
t1.dept_id,
t1.currency,
CASE WHEN t1.amount is NULL THEN
LAG(t1.amount, 1, 0) IGNORE NULLS OVER (PARTITION BY t1.account_no, t1.dept_id, t1.currency ORDER BY d.date_1)
ELSE t1.amount
END amount,
t1.date_2
FROM   dates d
LEFT OUTER JOIN table1 t1 PARTITION BY (t1.account_no, t1.dept_id, t1.currency)
ON d.date_1 = t1.date_2
ORDER BY d.date_1,
t1.account_no,
t1.dept_id,
t1.currency;
DATE_1      ACCOUNT_NO    DEPT_ID CURRENCY     AMOUNT DATE_2
----------- ---------- ---------- -------- ---------- -----------
01/01/2017           1          1 GBP              45 01/01/2017
01/01/2017           1          1 USD               0 
01/01/2017           1          2 EUR              25 01/01/2017
01/01/2017           1          2 USD               0 
01/01/2017           1          3 USD              51 01/01/2017
02/01/2017           1          1 GBP              45 
02/01/2017           1          1 USD               0 
02/01/2017           1          2 EUR              25 
02/01/2017           1          2 USD              65 02/01/2017
02/01/2017           1          3 USD              51 
03/01/2017           1          1 GBP              45 
03/01/2017           1          1 USD              50 03/01/2017
03/01/2017           1          2 EUR              25 
03/01/2017           1          2 USD              65 
03/01/2017           1          3 USD              51 
04/01/2017           1          1 GBP              45 
04/01/2017           1          1 USD              50 
04/01/2017           1          2 EUR              25 
04/01/2017           1          2 USD              65 
04/01/2017           1          3 USD              51 
05/01/2017           1          1 GBP              45 
05/01/2017           1          1 USD              50 
05/01/2017           1          2 EUR              25 
05/01/2017           1          2 USD              65 
05/01/2017           1          3 USD              51 

注：注：如果您使用的是 11.2 之前的 Oracle 版本，则 lag 不会知道忽略空值。您可以改用以下内容来模拟相同的效果：

nvl(last_value(t1.amount) IGNORE NULLS OVER (PARTITION BY t1.account_no, t1.dept_id, t1.currency ORDER BY d.date_1), 0)

Answer 2

您可以使用带有ignore nulls选项的lag()来执行此操作。 我认为这就是你想要的：

select d.date_1, a.account_no,
coalesce(dept_id,
lag(dept_id ignore nulls) over (partition by t1.account_no order by d.date_1)
) as dept_id,
coalesce(currency,
lag(currency ignore nulls) over (partition by t1.account_no order by d.date_1)
) as currency,
coalesce(amount,
lag(amount ignore nulls) over (partition by t1.account_no order by d.date_1)
) as amount
from dates d CROSS JOIN
(select distinct account_no from table1) a left join
table1 t1
on d.DATE_1 = t1.DATE_2 and a.account_no = t1.account_no;

Answer 3

WITH table1 AS
(
SELECT 1 account_no,
1 dept_id,
'USD' currency,
50 amount,
to_date('03/01/2017', 'dd/mm/yyyy') date_2
FROM dual
UNION ALL
SELECT 1 account_no,
2 dept_id,
'EUR' currency,
25 amount,
to_date('01/01/2017', 'dd/mm/yyyy') date_2
FROM dual
UNION ALL
SELECT 1 account_no,
3 dept_id,
'USD' currency,
51 amount,
to_date('01/01/2017', 'dd/mm/yyyy') date_2
FROM dual
UNION ALL
SELECT 1 account_no,
1 dept_id,
'GBP' currency,
45 amount,
to_date('01/01/2017', 'dd/mm/yyyy') date_2
FROM dual
UNION ALL
SELECT 1 account_no,
2 dept_id,
'USD' currency,
65 amount,
to_date('02/01/2017', 'dd/mm/yyyy') date_2
FROM dual
)
,
dates AS
(
SELECT to_date('01/01/2017', 'dd/mm/yyyy') date_1 FROM dual
UNION ALL
SELECT to_date('02/01/2017', 'dd/mm/yyyy') date_1 FROM dual
UNION ALL
SELECT to_date('03/01/2017', 'dd/mm/yyyy') date_1 FROM dual
UNION ALL
SELECT to_date('04/01/2017', 'dd/mm/yyyy') date_1 FROM dual
UNION ALL
SELECT to_date('05/01/2017', 'dd/mm/yyyy') date_1 FROM dual
)
SELECT d.date_1,
t1.account_no,
t1.dept_id,
t1.currency,
t1.amount,
CASE (t1.date_2)
WHEN d.date_1
THEN t1.date_2
ELSE NULL
END
FROM table1 t1,
dates d
ORDER BY d.date_1,
t1.account_no,
t1.dept_id,
t1.currency;