计算每日最低余额

Question

假设我有一个名为 transaction 的表，其中包含 transaction_date、存款、取款字段。一天可能有也可能没有交易，但每天可以有多个交易。所以，我需要做的是给定一个日期范围，比如2010年12月1日到2010年12月31日，我需要计算出每天的最低余额。我们假设 2010 年 12 月 1 日之前也有交易。有人可以给我一个关于这个的想法吗？

谢谢。

更新 示例

 tran_date   withdraw    deposit
2010-11-23       0.00      50.00
2010-12-10       0.00      50.00
2010-12-10       0.00     200.00
2010-12-12     100.00       0.00
2010-12-20       0.00      50.00
2010-12-20      70.00       0.00
2010-12-20       0.00      50.00
2010-12-20       0.00      50.00
2010-12-24     150.00       0.00

在上面的示例中，从12 月 1 日到12 月 10 日的最低每日余额为 50。 12 月 10 日有两笔存款总计 70，但当天的最低余额为 50（从前一天结转）。

现在让我们看看多笔交易。

12 月 20 日结转为 200。第一笔存款为250，第二笔存款为180，第三笔存款为230，最后一笔交易为 280。因此，在当天的第二笔交易中提取 70 后，当天的最低余额将为 180。是否可以使用 PostgreSQL 8.4 上的查询生成此内容，还是应该使用其他方法？

Answer 1

编辑2
这是一个完整的示例，包括前一天的（最小）余额（据我所知，用这么小的一组数据）。它应该在 8.4 上运行。

我重构了派生表以使用 CTE（通用表表达式）以使其（希望）更具可读性：

WITH days AS (
   -- generate a liste of possible dates spanning 
   -- the whole interval of the transactions
   SELECT min(tran_date) + generate_series(0, max(tran_date) - min(tran_date)) AS some_date
   FROM transaction
),
total_balance AS (
  -- Calculate the running totals for all transactions
  SELECT tran_id,
         days.some_date as tran_date, 
         deposit, 
         withdrawal,
         sum(deposit - withdrawal) 
             OVER (ORDER BY some_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as balance
  FROM days
   LEFT JOIN transaction t ON t.tran_date = days.some_date 
),
min_balance AS (
  -- calculate the minimum balance for each day 
  -- (the smalles balance will have a '1' in the column balance_rank)
  SELECT tran_id, 
         tran_date,
         rank() OVER (PARTITION BY tran_date ORDER BY balance) as balance_rank,
         balance
  FROM total_balance
)
-- Now get everything, including the balance for the previous day
SELECT tran_id,
       tran_date,
       balance,
       lag(balance) over (order by tran_date) as previous_balance
FROM min_balance
WHERE balance_rank = 1;

Answer 2

忽略其他答案中的所有内容。马伏里奥那个家伙是个吹牛者和白痴。试试这个：

SELECT MIN(balance), transaction_date FROM
( SELECT a.transaction_date, IFNULL(sum(b.deposit) - sum(b.withdrawal), 0) balance FROM transaction a
        LEFT JOIN transaction b ON a.seqno > b.seqno GROUP ON a.seqno
   UNION
  SELECT a.transaction_date, IFNULL(sum(b.deposit) - sum(b.withdrawal), 0) balance FROM transaction a
        LEFT JOIN transaction b ON a.seqno >= b.seqno GROUP ON a.seqno  ) x
GROUP BY transaction_date;

当我想到这个时，我正要入睡。 IFNULL 可能是 MySQL 特有的东西，但你可以找到 Postgres 的等效项。

Answer 3

我假设您所说的最低余额是一天开始时或一天结束时的余额较少？

我想你每天都会做这样的事情：

前一天的余额：（

SELECT (SUM(deposit) - SUM(withdrawal)) WHERE date < [date you're after]

不确定如何在 PostgreSQL 中进行日期比较

然后：

SELECT (SUM(deposit) - SUM(withdrawal)) WHERE date = [date you're after]

然后以较大者为准。

如果这不是你的意思，我们需要更多信息。

Answer 4

首先，我假设交易是按顺序编号的。根据定义，交易必须正确排序（因为在同一天存款 50 美元，然后取款 50 美元，按照不同的顺序执行相同的步骤会产生截然不同的结果），并且按顺序对它们进行编号可以使其他事情变得更加容易。然后我们必须做一些程序上的挥手：（

CREATE TABLE running_total (seqno INT, transaction_date DATE, before NUMBER(10,20), after NUMBER(10,20);
SET tot=0;
FOR transaction IN SELECT * FROM transaction ORDER BY seqno ASC LOOP
    SET oldtot = tot;
    SET tot = tot = transaction.deposit - transaction.withdrawal;
    EXECUTE 'INSERT INTO running_total (seqno, transaction_date, before, after) VALUES (' ||
    transaction.seqno || ', ' || transaction.transaction_date || ',' || oldtot || ',' || tot || ')';
END LOOP;

请原谅任何拼写错误——我手头没有 PostGres）。现在我们有了一个包含所有余额的表，我们只需将其挖出来即可。

SELECT MIN(balance), transaction_date FROM
( SELECT before as balance, transaction_date FROM running_total
   UNION
 SELECT after as balance, transaction_date FROM running_total) x 
GROUP BY transaction_date;

我无法在这里测试它，但它应该可以工作。

Answer 5

假设您对一天内的交易进行编号，我采用以下模式：

CREATE TABLE transaction (
    tran_date date,
    num       int,
    withdraw  numeric,
    deposit   numeric
);

INSERT INTO transaction VALUES
    ('2010-11-23', 1,      0.00,      50.00),
    ('2010-12-10', 1,      0.00,      50.00),
    ('2010-12-10', 2,      0.00,     200.00),
    ('2010-12-12', 1,    100.00,       0.00),
    ('2010-12-20', 1,      0.00,      50.00),
    ('2010-12-20', 2,     70.00,       0.00),
    ('2010-12-20', 3,      0.00,      50.00),
    ('2010-12-20', 4,      0.00,      50.00),
    ('2010-12-24', 1,    150.00,       0.00);

然后，以下查询将为您提供答案：

WITH dates (tran_date) AS (SELECT date '2010-12-01' + generate_series(0, 30)),    
     transactions AS (SELECT tran_date, num,
                             coalesce(withdraw, 0) AS withdraw, 
                             coalesce(deposit, 0) AS deposit
                      FROM dates FULL OUTER JOIN transaction USING (tran_date)),
     running_totals AS (SELECT tran_date,     
                               sum(deposit - withdraw) OVER (ORDER BY tran_date, num ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS lagging_total,
                               sum(deposit - withdraw) OVER (ORDER BY tran_date, num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_total
                        FROM transactions)
SELECT tran_date, min(least(lagging_total, current_total))
FROM running_totals
GROUP BY tran_date
HAVING tran_date IN (SELECT tran_date FROM dates)
ORDER BY tran_date;    

但请注意，您需要 PostgreSQL 9.0，因为 1 PRECEDING 子句早期版本不支持。如果您无法升级，您可能需要某种程序解决方案，就像其他答案所建议的那样。

无论如何，我建议为此编写单元测试。 ;-)

Answer 6

为什么不在数据库中添加一列来跟踪当前余额（在每次存款/取款时计算）。这样，只需返回您感兴趣的日期范围内该列的最小值即可。

Answer 7

谢谢大家的帮助。我使用以下方法来解决这个问题。我不知道代码的效率如何。

select dt::date, 
coalesce(case when balance<=coAmt then balance else coAmt end, 
(select sum(coalesce(deposit, 0.00))-sum(coalesce(withdraw, 0.00)) 
from  where tran_date<=dt::date and acc_no='3'), 0.00) amt
from (
select tran_date, min(balance) balance, 
coalesce((select sum(coalesce(deposit, 0.00) - coalesce(withdraw, 0.00)) 
from transaction where tran_date<t.tran_date and acc_no=t.acc_no), 0.00) coAmt
from (
select tran_id, acc_no, tran_date, deposit, withdraw,
sum(deposite - withdraw) over (order by tran_id) balance 
from transaction sv group by tran_id, acc_no, tran_date, deposite, withdraw) t 
where acc_no='3' group by tran_date, acc_no order by tran_date ) t1 
right join 
generate_series('2010-12-01', '2010-12-31', interval '1 day') as dt on dt=tran_date 
group by dt, tran_date, balance, coAmt order by dt

再次感谢您的所有帮助。