运行总......有点扭曲 [英] Running total... with a twist

问题描述

我正在尝试找出 SQL 来计算每日配额系统的运行总数.系统是这样工作的...

I am trying to figure out the SQL to do a running total for a daily quota system. The system works like this...

用户每天获得 2 个消耗品"的配额.如果他们全部用完，第二天他们会得到另外 2 个.如果他们以某种方式过度使用它们(使用超过 2 个)，第二天他们仍然得到 2 个(他们不能有负余额).如果他们不全部使用它们，剩余的会带到第二天(可以带到下一天，等等......).

Each day a user gets a quota of 2 "consumable things". If they use them all up, the next day they get another 2. If they somehow over use them (use more than 2), the next day they still get 2 (they can't have a negative balance). If they don't use them all, the remainder carries to the next day (which can carry to the next, etc...).

这是用作验证的数据图表.它被列为当天的配额、当天使用的金额、当天结束时剩余的金额:

Here is a chart of data to use as validation. It's laid out as quota for the day, amount used that day, amount left at the end of the day:

2 - 2 - 0
2 - 0 - 2
4 - 3 - 1
3 - 0 - 3
5 - 7 - 0
2 - 1 - 1
3 - 0 - 3
5 - 2 - 3
5 - 1 - 4
6 - 9 - 0

开始的 SQL 将是:

The SQL to start of with would be:

WITH t(x, y) AS (
  VALUES (2, '2013-09-16'),
              (0, '2013-09-17'),
              (3, '2013-09-18'),
              (0, '2013-09-19'),
              (7, '2013-09-20'),
              (1, '2013-09-21'),
              (0, '2013-09-22'),
              (2, '2013-09-23'),
              (1, '2013-09-24'),
              (9, '2013-09-25')
)

在我的一生中，尝试使用语句和窗口聚合进行递归，我无法弄清楚如何使其工作(但我当然可以看到模式).

For the life of me, trying recursive with statements and window aggregates, I cannot figure out how to make it work (but I can certainly see the pattern).

它应该类似于 2 - x + SUM(上一行)，但我不知道如何将其放入 SQL.

It should be something like 2 - x + SUM(previous row), but I don't know how to put that in to SQL.

推荐答案

尝试创建自定义聚合函数，例如:

Try creating custom aggregate function like:

CREATE FUNCTION quota_calc_func(numeric, numeric, numeric) -- carry over, daily usage and daily quota
RETURNS numeric AS 
$$
  SELECT GREATEST(0, $1 + $3 - $2);
$$
LANGUAGE SQL STRICT IMMUTABLE;

CREATE AGGREGATE quota_calc( numeric, numeric ) -- daily usage and daily quota
(
    SFUNC = quota_calc_func,
    STYPE = numeric,
    INITCOND = '0'
);

WITH t(x, y) AS (
  VALUES (2, '2013-09-16'),
              (0, '2013-09-17'),
              (3, '2013-09-18'),
              (0, '2013-09-19'),
              (7, '2013-09-20'),
              (1, '2013-09-21'),
              (0, '2013-09-22'),
              (2, '2013-09-23'),
              (1, '2013-09-24'),
              (9, '2013-09-25')
)
SELECT x, y, quota_calc(x, 2) over (order by y)
FROM t;

可能包含错误，尚未测试.

May contain bugs, haven't tested it.

这篇关于运行总......有点扭曲的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！