Postgres如何获取每个自然月最后一条closing_balance及月度利息总和

解决方案 #

你可以用以下两种方式修改查询，都可以同时拿到月利息总和和当月最后一天的余额：

方案1：简洁写法（适合中小数据量）

利用PostgreSQL的数组聚合特性，直接在分组查询中排序取当月最后一天的余额：

SELECT 
    date_trunc('month', "date") AS my_month,
    SUM(interest) AS interest_sum,
    (array_agg(closing_balance ORDER BY "date" DESC))[1] AS month_end_closing_balance
FROM my_table
GROUP BY my_month
ORDER BY my_month;

逻辑说明：array_agg会将当月所有closing_balance按日期倒序排列后拼接为数组，取数组的第一个元素就是当月最后一条记录的余额值，和你给出的示例数据匹配的话，1月会返回28000、2月返回27000。

方案2：高性能写法（适合大数据量、date字段有索引的场景）

先计算每个月的最大日期，再关联原表取对应余额，逻辑更直观性能更好：

WITH monthly_base AS (
    SELECT 
        date_trunc('month', "date") AS my_month,
        SUM(interest) AS interest_sum,
        MAX("date") AS month_last_date
    FROM my_table
    GROUP BY my_month
)
SELECT 
    mb.my_month,
    mb.interest_sum,
    t.closing_balance AS month_end_closing_balance
FROM monthly_base mb
JOIN my_table t ON mb.month_last_date = t."date"
ORDER BY mb.my_month;

内容的提问来源于stack exchange，提问作者Kritz