首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >按月汇总收入和费用信息

问按月汇总收入和费用信息
EN

Code Review用户
提问于 2015-08-07 21:16:25
回答 1查看 1.3K关注 0票数 1

我给自己做了一个简单的预算应用程序。每个事务都存储在transactions表中:

代码语言:javascript
复制
CREATE TABLE transactions (
    -- Foreign key into the "categories" table, not shown here. (In reality this
    -- has a NOT NULL constraint instead of a default, but this makes the
    -- example data easier to enter.)
    category INTEGER DEFAULT 1,
    -- Number of cents. Positive values represent income and negative values are
    -- expenses.
    amount INTEGER NOT NULL,
    -- The date this transaction occurred.
    transaction_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

(还有其他几个专栏,但它们与我的问题无关,因为我的问题涉及一些摘要信息的视图。)在每个月开始时,我的应用程序为我的每个支出类别添加了一项收入交易;这些项目使用的是正数。每个月我每次花钱,我都会加一个负数的交易。如果在月底把钱留在一个类别中,我可以在下个月使用它,所以我只需将所有的交易按类别分组并将金额相加,就可以检查我的类别的余额。

我写了下面的观点,试图总结我每个月的消费习惯(这一次忽略了我的支出类别,把我所有的交易合并在一起)。它生成的列是

  • month:ISO 8601格式的月份,例如“2015-08”。
  • carryover:月开始前可用的金额(在月初进行收入交易之前,实际上是在月初20毫秒左右)。
  • filled:当月发生的所有收入交易的总额。
  • spent:当月发生的所有费用交易的总额,现在表示为正数。
  • pct_of_fill:spent / filled.
  • pct_of_available:spent / (filled + carryover)。

我写的观点似乎是正确的,但很奇怪。WITH子句看起来是多余的,主查询的SELECT部分有很多重复。我怎样才能用一种更地道的方式重写这个视图呢?

Months视图

代码语言:javascript
复制
CREATE VIEW months AS
    WITH filled AS (
            SELECT date_trunc('month', transaction_date) AS month,
                sum(amount) AS filled
            FROM transactions
            WHERE amount > 0
            GROUP BY month),
        spent AS (
            SELECT date_trunc('month', transaction_date) AS month,
                sum(amount) AS spent
            FROM transactions
            WHERE amount < 0
            GROUP BY month),
        months AS (
            SELECT DISTINCT date_trunc('month', transaction_date) AS month
            FROM transactions),
        cumulative AS (
            SELECT month,
                sum(amount) AS s
            FROM transactions
            LEFT JOIN months ON transaction_date < months.month
            GROUP BY month)
    SELECT to_char(filled.month, 'YYYY-MM') AS month,
        coalesce(sum(cumulative.s), 0) AS carryover,
        sum(filled.filled) AS filled,
        abs(sum(spent.spent)) AS spent,
        round(100 * abs(sum(spent.spent))/sum(filled.filled), 1) AS pct_of_fill,
        round(100 * abs(sum(spent.spent))/(coalesce(sum(cumulative.s), 0)+sum(filled.filled)), 1) AS pct_of_available
    FROM filled
    LEFT JOIN spent ON filled.month = spent.month
    LEFT JOIN cumulative ON filled.month = cumulative.month
    GROUP BY filled.month
    ORDER BY filled.month;

示例

插入下列数据

代码语言:javascript
复制
INSERT INTO transactions(amount, transaction_date) VALUES
    (10000, '2015-06-01T00:00:00.02'),
    (-3000, '2015-06-05'),
    (-1000, '2015-06-06'),
    (-4000, '2015-06-21'),
    (10000, '2015-07-01T00:00:00.02'),
    (-2500, '2015-07-03'),
    (-1000, '2015-07-05'),
    (-7000, '2015-07-11'),
    (10000, '2015-08-01T00:00:00.02'),
    (-1500, '2015-08-03');

为months提供以下输出:

代码语言:javascript
复制
  month  | carryover | filled | spent | pct_of_fill | pct_of_available 
---------+-----------+--------+-------+-------------+------------------
 2015-06 |         0 |  10000 |  8000 |        80.0 |             80.0
 2015-07 |      2000 |  10000 | 10500 |       105.0 |             87.5
 2015-08 |      1500 |  10000 |  1500 |        15.0 |             13.0
EN

回答 1

Code Review用户

发布于 2015-08-08 06:46:05

Postgresql有一个money数据类型专门用于存储货币。我知道您已经通过存储美分来避免浮点错误,但至少在显示方面使用money可能会更好(如果您获得或损失超过2100万美元)。

票数 2
EN
页面原文内容由Code Review提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://codereview.stackexchange.com/questions/99304

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档