db2 SQL计算每个元素的值

webghufk 于 2022-11-07 发布在 DB2

关注(0)|答案(1)|浏览(162)

我在db2中有两个表，我想计算每个项目的金额。在第一个表中，我有：

SELECT sum(AMOUNT_CUR) AS amount, ID_1
FROM TAB_SCHEMA.TAB1 
WHERE ID_1 = '0000710195|000100179390'
GROUP BY ID_1

第二张table上有：

SELECT 
DATE_1, AMOUNT_PLN1 
FROM TAB_SCHEMA.TAB2 
WHERE ID_1 = '0000710195|000100179390'
ORDER BY DATE_1 ASC

我想得到这样的东西。对于TAB2中的第一个项目，计算差异，即。

DATE_1    amount_2
20191231  9613170,76-3902609,95 = 5710560,81 (amount-AMOUNT_PLN1)
20200229  5710560,81-557515,71 = 5153045,1 
20200331  5153045,1-2779121,54 ...

有人知道如何轻松地做到这一点吗？也许是一些功能？

db2

来源：https://stackoverflow.com/questions/70802288/sql-counting-the-value-for-each-element

1条答案

按热度按时间

34gzjxbg1#

您需要OLAP规范，特别是SUM() OVER()

with
tab1 (amount, id_1) as (values (10, 1)),
tab2 (id_1, date_1, amount_pln1) as (
    values
    (1, date '2019-12-31', 1),
    (1, date '2020-02-29', 2),
    (1, date '2020-03-31', 3)
)
select
    tab2.date_1,
    tab1.amount - coalesce(sum(tab2.amount_pln1) over(partition by id_1 order by date_1 rows between unbounded preceding and 1 preceding), 0) previous_balance,
    tab2.amount_pln1,
    tab1.amount - sum(tab2.amount_pln1) over(partition by id_1 order by date_1) balance
from
    tab1 inner join tab2 using(id_1)

赞(0）回复(0）举报 2022-11-07

我来回答

db2 SQL计算每个元素的值

1条答案

相关问题

热门标签

最新问答