Getting the last value per calendar month in Postgres-CodePudding

I have a daily loan schedule in a Postgresql database that looks as follows:

date      | interest | closing_balance
1 Jan 21  | 100      | 30000
2 Jan 21  | 99       | 29910
....  
31 Jan 21 | 98       | 28000
1 Feb 21
2 Feb 21
...
28 Feb 21 | 90       | 27000

I want to sum the interest column per month and then get the last value for each month for the closing_balance column.

The following seems to work to get the summed up value of the interest column per month:

SELECT date_trunc('month', "my_table"."date") AS my_month,
SUM("my_table"."interest") AS "interest_sum"
FROM "my_table"
GROUP BY my_month
ORDER BY my_month

I'm struggling to get the closing balance for each month. The above example should return 2 rows for Jan and Feb with 28000 and 27000 respectively. How should I update the query to calculate this?

CodePudding user response：

You need a window function which will select the last row inside the window associated to each row resulting from GROUP BY my_month once the rows have been ordered by date inside the window. See the manual for more explaination : 3.5. Window Functions, 4.2.8. Window Function Calls, 9.22. Window Functions Try this :

SELECT date_trunc('month', "my_table"."date") AS my_month
     , SUM("my_table"."interest") AS "interest_sum"
     , last_value("my_table"."closing_balance") OVER (ORDER BY date) AS last_closing_balance
  FROM "my_table"
 GROUP BY my_month
 ORDER BY my_month