sql - 查找行的财务模式 - 带递归的 sql

Question

假设我有一个下表:

id    date        transaction_type     amount
 1    2017-01-01  deposit              30
 1    2017-01-01  deposit              20
 1    2017-01-02  withdrawal           -20
 1    2017-01-02  deposit              40
 1    2017-01-04  deposit              50
 1    2017-01-05  withdrawal           -100
 1    2017-01-07  withdrawal           -10
 1    2017-01-09  deposit              100
 1    2017-01-11  withdrawal           -50
 1    2017-01-21  deposit              20
 1    2017-01-22  deposit              30
 1    2017-01-31  withdrawal           -60
 2    2017-01-01  deposit              200
 ...  ...         ...                  ...

表中的日期对于每个 id 从最旧到最新排序(时间戳不可见)。我想找出特定交易模式发生了多少次:

充值->充值->提现，首次充值到提现时间不超过7天。

所以对于id = 1的客户，我会有2种这样的情况(第三种不满足时间条件)。

因此，我想得到下表:

id   number_of_times
1    2
2    ...
...  ...

它可以用 SQL 完成吗？我是否需要递归才能进入决赛 table ？

更新:正如正确指出的那样，没有中间交易 - 但如果有中间交易怎么办？就像第一笔和第二笔存款之间的任何其他交易一样。

Answer 1

假设您没有干预交易:

select id, count(*)
from (select t.*,
             lead(transaction_type) over (partition by id order by date) as next_tt,
             lead(transaction_type, 2) over (partition by id order by date) as next_tt_2,
             lead(date, 2) over (partition by id order by date) as next_date_2
      from t
     ) t
where transaction_type = 'deposit' and next_tt = 'deposit' and
      next_tt_2 = 'withdrawal' and
      next_date_2 < date + interval '7 day'
group by id;