简单私人现金流数据库统计查询：百分比计算与单查询实现

背景与表结构 #

我搭建了一个简单的私人现金流数据库，用finance_flow表记录所有收支规则如下：

收入 -> amount > 0
支出 -> amount < 0

表结构与示例数据 #

finance_flow表

category表

表间已正确配置外键约束。

需求 #

需要获取三类统计数据：

• 当前资金总余额
• 各分类的总支出金额
• 各分类支出占当月总收入的百分比

已掌握的基础查询：

• 获取当前资金总余额：

SELECT sum(amount) as total FROM finance_flow

• 获取各分类总支出（WHERE子句可选，用于月度统计）：

SELECT abs(sum(amount)) as total_per_cat, category.name 
FROM finance_flow 
LEFT JOIN category ON finance_flow.category_id = category.id 
WHERE date >= '2023-02-01' AND date < '2023-03-01' -- 替换为目标月份范围
GROUP BY category_id, category.name

（注：修正了原查询中financeflow.cat_id的笔误，同时用日期范围替代模糊匹配，避免timestamp类型的时间部分导致数据遗漏）

问题 #

如何计算各分类支出占比？能否通过单个查询同时获取所有上述统计数据？

占比计算公式：
procentual_per_cat = total_per_cat / total_income(当月) * 100
其中：

• total_per_cat = 某分类支出的abs(sum(amount))
• total_income(当月) = 示例中2月总收入60

预期结果表：

解决方案 #

单个查询获取所有统计数据 #

提供两种实现方案，均能一次返回所有需要的统计结果：

方案1：子查询实现

SELECT 
    c.name,
    ABS(ff.total_per_cat) AS total_per_cat,
    ROUND((ABS(ff.total_per_cat) / ti.total_income) * 100, 2) AS procentual_per_cat,
    ti.total_income AS monthly_total_income,
    (SELECT SUM(amount) FROM finance_flow) AS overall_total_balance
FROM (
    SELECT 
        category_id,
        SUM(amount) AS total_per_cat
    FROM finance_flow
    WHERE date >= '2023-02-01' AND date < '2023-03-01'
    GROUP BY category_id
) ff
LEFT JOIN category c ON ff.category_id = c.id
CROSS JOIN (
    SELECT SUM(amount) AS total_income
    FROM finance_flow
    WHERE date >= '2023-02-01' AND date < '2023-03-01' AND amount > 0
) ti
WHERE ff.total_per_cat < 0 -- 仅筛选支出分类
ORDER BY procentual_per_cat DESC;

方案2：窗口函数简化实现

SELECT 
    c.name,
    ABS(SUM(ff.amount)) AS total_per_cat,
    ROUND((ABS(SUM(ff.amount)) / SUM(SUM(ff.amount)) FILTER (WHERE ff.amount > 0) OVER ()) * 100, 2) AS procentual_per_cat,
    SUM(SUM(ff.amount)) FILTER (WHERE ff.amount > 0) OVER () AS monthly_total_income,
    (SELECT SUM(amount) FROM finance_flow) AS overall_total_balance
FROM finance_flow ff
LEFT JOIN category c ON ff.category_id = c.id
WHERE ff.date >= '2023-02-01' AND ff.date < '2023-03-01'
GROUP BY ff.category_id, c.name
HAVING SUM(ff.amount) < 0 -- 仅保留支出分类
ORDER BY procentual_per_cat DESC;

关键说明 #

1. 日期过滤：采用date >= 当月第一天 AND date < 下月第一天的方式，确保不会遗漏包含具体时间的timestamp数据。
2. 占比计算：通过子查询或窗口函数提前计算当月总收入，再用各分类支出绝对值除以总收入得到占比，ROUND(x,2)用于保留两位小数。
3. 总余额获取：通过独立子查询直接获取所有收支的累计总余额。
4. 支出筛选：用WHERE/HAVING条件仅展示支出分类，与预期结果一致。

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