我使用的是Oracle 12c数据库。我有下表:
accountId | totalBalance | balanceDate |
1 | 10 | 01/01/2010 |
1 | 20 | 01/02/2010 |
1 | 30 | 01/03/2010 |
2 | 11 | 10/01/2010 |
2 | 21 | 10/02/2010 |
2 | 31 | 10/03/2010 |
该表将存储每个账户最近3个月的最后余额记录,这意味着每个账户在表中都有3条记录。周期的数量是已知的,在这种情况下是3。当然,余额的值会随着月份的进展而变化,因为该表总是存储最后三个月的余额。当然,不知道的是账户的数量。
我想创建一个视图,显示上述数据如下:
accountId | firstMonth | secondMonth | thirdMonth |
1 | 10 | 20 | 30 |
2 | 11 | 21 | 31 |
我不在乎日期。
我看到了很多类似的问题,但没有一个完全相同,我不知道如何实现这一点。有人有什么建议吗?
嗯。你想要一个相对的月份。假设月份日期一致,则可以使用months_between()
:
select accountId,
max(case when months_between(last_bd, balance_date) = 2 then totalBalance end) as firstMonth,
max(case when months_between(last_bd, balance_date) = 1 then totalBalance end) as secondMonth,
max(case when months_between(last_bd, balance_date) = 0 then totalBalance end) as thirdMonth
from (select max(balanceDate) over (partition by accountId) as last_bd, b.*
from balances b
) b
group by accountId