sql - 列和行之间的减法和加法
问题描述
我有一些数据看起来像这样
id date total amount adj amount
1 2017-01-02 100 50
1 2017-01-02 50 0
2 2017-01-15 100 35
2 2017-01-15 35 0
3 2017-01-30 120 50
3 2017-01-30 -120 -50
3 2017-01-30 100 50
3 2017-01-30 50 0
3 2017-01-30 60 40
输出应该看起来像,我不知道如何在行和列之间进行减法。
id date due amount
1 2017-01-02 0
2 2017-01-15 0
3 2017-01-30 40
这是我当前的代码,但它仅适用于 1 和 2,但绝对不适用于 3。
这部分的逻辑是在每个 id 的每个条目之间找到应有的金额。比如id 1有两个入口,总金额100,然后他支付50,所以adj金额是50,第二个入口,总金额是50,他支付50,te adj金额是0。所以id 1到期最终金额为0。
id 3 有 5 个条目,第一个条目显示 ID 3 的总金额是 120,他支付了 70,所以调整金额是 50,但第一个条目是错误的,所以所有金额都修改了。那么第三个条目显示总金额为100,ID 3支付50,因此调整金额为50。然后第四个条目显示总金额为50,ID 3也支付50,因此调整金额为0。第五个条目显示总金额为60,ID 3支付了20,因此adj金额为40。所以最终ID 3到期金额为40;
select distinct a.id,
a.date,
case when a.date=b.date and a.total_amount = b.adj_amount then a.adj_amount
when a.date=b.date and a.total_amount <> b.adj_amount then ABS(a.adj_amount + b.adj_amount)
else a.adj_amount
end as due_amount
from table a,
table b
where a.id=b.id;
我只是想知道是否有任何函数可以在行和列之间进行这种计算。
解决方案
如果可以订购交易,这样的事情可能会奏效。请注意,我已经重命名了一些列以帮助阐明它们的含义。我还添加了一个 trans_seq_num 列来指示客户在特定日期的交易顺序。我认为您正在寻找客户在上次付款时仍欠的金额。
WITH sample (id, trans_seq_num, some_date, starting_balance, ending_balance) AS
(
SELECT '1',1,'2017-01-02','100','50' FROM dual UNION ALL
SELECT '1',2,'2017-01-02','50','0' FROM dual UNION ALL
SELECT '2',1,'2017-01-15','35','0' FROM dual UNION ALL
SELECT '2',2,'2017-01-15','100','35' FROM dual UNION ALL
SELECT '3',1,'2017-01-30','120','50' FROM dual UNION ALL
SELECT '3',2,'2017-01-30','-120','-50' FROM dual UNION ALL
SELECT '3',3,'2017-01-30','100','50' FROM dual UNION ALL
SELECT '3',4,'2017-01-30','50','0' FROM dual UNION ALL
SELECT '3',5,'2017-01-30','60','40' FROM dual
)
SELECT DISTINCT id,
some_date,
LAST_VALUE(ending_balance) OVER (PARTITION BY id ORDER BY trans_seq_num RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) day_balance
FROM sample
ORDER BY 1,2,3;
ID SOME_DATE AMOUNT_DUE
----- --------------- ---------------
1 2017-01-02 0
2 2017-01-15 35
3 2017-01-30 40
推荐阅读
- sql-server - 在 SQL 中加入视图内部视图时,获取记录的速度非常慢
- android - 反应本机代码推送无法解析':app@releaseStaging/compileClasspath'的依赖关系:
- python - python pandas:比较两列是否相等并产生第三个数据框
- jenkins - 将非 Maven 项目上传到 nexus
- javascript - 在 ReactJS 中将 props 从一个组件传递到另一个组件返回 undefined
- javascript - 将数据表导出到 excel 时提示用户输入文件名
- python - Numba 中的负动态索引
- solr - 如何提取
- javascript - Lightwidget 在我的 HTTPS 网站中不起作用
- php - PHP - 使用 ksort() 对关联数组进行排序