sql - Oracle 是否允许对分区进行求和，但仅当它遵守某些条件时才允许，否则使用滞后？

Question

所以我的公司有一个应用程序，它有一定的“应用内货币”。我们记录每一笔交易。最近，我们发现存在一个持续了几周的错误，它允许用户在某个地方消费货币，即使他们没有货币。发生这种情况时，用户根本不会被收取费用：例如，用户有 4 亩，并购买了价值 10 亩的东西，其余额将保持为 4。

现在我们需要找出谁滥用了它以及他们的可用余额是多少。

我想获得BUG_ABUSE和WISHFUL_CUMMULATIVE列，它们反映了非法交易以及我们的用户在他们的应用程序内钱包中真正看到的金额，但我已经不知道如何到达那里了。

我想知道如果结果超过 0 否则我是否可以做一些类似 sum(estrelas) 的事情，否则会滞后（按日期按用户顺序分区）或类似的事情来获得如意累积。

我们正在使用甲骨文。非常感谢任何帮助

用户身份	活动日期	数量	方向	RK	暨	WISHFUL_CUMMULATIVE	BUG_ABUSE
1	2021 年 2 月 1 日 13:37:19,009000	-5	0	1	-5	0	1
1	08/01/2021 01:55:40,000000	40	1	2	35	40	0
1	10/01/2021 10:45:41,000000	2	1	3	37	42	0
1	2021 年 10 月 1 日 10:45:58,000000	2	1	4	39	44	0
1	10/01/2021 13:47:37,456000	-5	0	5	34	39	0
2	13/01/2021 20:09:59,000000	2	1	1	2	2	0
2	16/01/2021 15:14:54,000000	-50	0	2	-48	2	1
2	19/01/2021 02:02:59,730000	-5	0	3	-53	2	1
2	23/01/2021 21:14:40,000000	3	1	4	-50	5	0
2	23/01/2021 21:14:50,000000	-5	0	5	-55	0	0

标签： sqloracle

Answer 1

这是你可以尝试的东西。这使用递归子查询分解（递归 WITH 子句），因此它仅适用于 Oracle 11.2 及更高版本。

我使用您输入中的 USER_ID、EVENT_DATE 和 AMOUNT 列。我假设所有三列都被限制为 NOT NULL，对于同一用户，两个事件不能具有完全相同的时间戳，并且 AMOUNT 对于购买和其他借方（费用等）是负数，对于存款或其他贷方是正数。

输入数据如下所示：

select user_id, event_date, amount
from   sample_data
order  by user_id, event_date
;

USER_ID EVENT_DATE                    AMOUNT
------- ----------------------------- ------
      1 02/01/2021 13:37:19,009000000     -5
      1 08/01/2021 01:55:40,000000000     40
      1 10/01/2021 10:45:41,000000000      2
      1 10/01/2021 10:45:58,000000000      2
      1 10/01/2021 13:47:37,456000000     -5
      2 13/01/2021 20:09:59,000000000      2
      2 16/01/2021 15:14:54,000000000    -50
      2 19/01/2021 02:02:59,730000000     -5
      2 23/01/2021 21:14:40,000000000      3
      2 23/01/2021 21:14:50,000000000     -5

也许您的输入数据有额外的列（如累积量，我忽略了它，因为它在问题或其解决方案中没有任何作用）。您显示了一个 RK 列 - 我假设您将其计算为您尝试解决问题的一个步骤；我在下面的解决方案中重新创建它。

以下是您可以使用递归查询（递归 WITH 子句）执行的操作：

with
  p (user_id, event_date, amount, rk) as (
    select user_id, event_date, amount,
           row_number() over (partition by user_id order by event_date)
    from   sample_data
  )
, r (user_id, event_date, amount, rk, bug_flag, balance) as (
    select  user_id, event_date, amount, rk,
            case when amount < 0 then 'bug' end, greatest(amount, 0)
      from  p
      where rk = 1
    union all
    select  p.user_id, p.event_date, p.amount, p.rk, 
            case when p.amount + r.balance < 0 then 'bug' end,
            r.balance + case when r.balance + p.amount >= 0
                             then p.amount else 0 end
      from  p join r on p.user_id = r.user_id and p.rk = r.rk + 1
  )
select *
from   r
order  by user_id, event_date
;

输出：

USER_ID EVENT_DATE                    AMOUNT RK BUG BALANCE
------- ----------------------------- ------ -- --- -------
      1 02/01/2021 13:37:19,009000000     -5  1 bug       0
      1 08/01/2021 01:55:40,000000000     40  2          40
      1 10/01/2021 10:45:41,000000000      2  3          42
      1 10/01/2021 10:45:58,000000000      2  4          44
      1 10/01/2021 13:47:37,456000000     -5  5          39
      2 13/01/2021 20:09:59,000000000      2  1           2
      2 16/01/2021 15:14:54,000000000    -50  2 bug       2
      2 19/01/2021 02:02:59,730000000     -5  3 bug       2
      2 23/01/2021 21:14:40,000000000      3  4           5
      2 23/01/2021 21:14:50,000000000     -5  5           0