语句来查看前面的所有行。

Question

我有下表

create table interest_earning_assets (
  key integer
  month_of varchar(7),
  principal_distribution numeric(38,2),
  closed_loan_amount numeric(38,2)
);

数据如下所示

key month_of    principal_distribution  closed_loan_amount
24  2017-01     4133500.00              5984695.00
23  2016-12     12018303.93             26941275.40
22  2016-11     6043945.46              21239620.20
21  2016-10     2864195.39              20368518.20

我有两个要求。

1. 总结closed_amount_values

对于每个月(目前为24个月，下个月为25个月，然后为26个月等)，我需要用前几个月的值对closed_amount_values进行汇总，即

2017-01 sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12 + 2017-01)
2016-12 sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12)
2016-11 sum(closed_loan_amount for 2016-10 + 2016-11) 
2016-10 sum(closed_loan_amount for 2016-10 )  

1. 将closed_loan_amount之和减为principal_distribution

一旦我得到了求和值，我需要将每个月的closed_loan_amount和减为principal_distribution。

2017-01 principal_distribution for 2017-01 - sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12 + 2017-01)
2016-12 principal_distribution for 2016-12 - sum(closed_loan_amount for 2016-10 + 2016-11 + 2016-12)
2016-11 principal_distribution for 2016-11 - sum(closed_loan_amount for 2016-10 + 2016-11) 
2016-10 principal_distribution for 2016-10 - sum(closed_loan_amount for 2016-10 )  

Redshift不支持存储过程，我也不精通Python。所以我试着用滞后

select month_of, closed_loan_amount, 
       lag(closed_loan_amount,1) over (order by month_of desc) as previous_month 
from public.interest_earning_assets

它工作，但只给我前一个月的价值。我也在研究使用CTE，但我今天才得到这个任务。我如何在SQL中做到这一点？

Answer 1

使用带有窗口规范的sum窗口函数来查看前面的所有行，以获得closed_loan_amount的和，并从principal_distribution中减去它。

select month_of, closed_loan_amount, 
 principal_distribution
-sum(closed_loan_amount) over (order by month_of desc rows between current row and unbounded following) as some_value
from public.interest_earning_assets

Answer 2

试试这个：

SELECT [key], month_of,
       SUM(closed_loan_amount) OVER(ORDER BY month_of),
       principal_distribution + SUM(closed_loan_amount) OVER(ORDER BY month_of)
FROM interest_earning_assets

带有SUM子句的窗口版本的ORDER BY子句根据出现在ORDER BY子句中的第二个字段定义的顺序计算字段的运行总数。