我给自己做了一个简单的预算应用程序。每个事务都存储在transactions表中:
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视图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;插入下列数据
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提供以下输出:
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发布于 2015-08-08 06:46:05
Postgresql有一个money数据类型专门用于存储货币。我知道您已经通过存储美分来避免浮点错误,但至少在显示方面使用money可能会更好(如果您获得或损失超过2100万美元)。
https://codereview.stackexchange.com/questions/99304
复制相似问题