没人解的开了。。Oracle SQL 如何计算start date到end date小于30天，且加交易总数值小于两百万

Question

cust_id	date	prd_id	amt	type
A123	2022/01/01	aa11	1,000,001	buy
A123	2022/01/05	aa11	1,000,000	sell
B123	2022/01/06	bb22	1,000	buy
B123	2022/01/07	bb22	1,000	sell
C123	2022/01/08	aa11	2,000,000	buy
D123	2022/01/09	aa11	2,000,000	buy
D123	2022/03/09	aa11	2,000,000	sell

怎么判断在date栏位内，30天内有buy 和sell的amt加总超过200万
以这个条件，应得到

cust_id	date	prd_id	amt	type
A123	2022/01/01	aa11	1,000,001	buy
A123	2022/01/05	aa11	1,000,000	sell

cust_id B123不符合，因为buy--sell不超过200万
cust_id C123不符合，因为没有sell
cust_id D123不符合，因为buy--sell超过30天

求解~！！

Answer 1

设表名为 record, date字段的类型为日期,并假设sell的date比buy的date要大
思路是这个表和这个表自身关联,左表为buy的,右表为sell,关联条件是cust_id和prd_id,这样就解决了 下面是伪代码 没验证

select 
  *
from 
  record a, 
  record b
where
  a.cust_id = b.cust_id
and
  a.prd_id = b.prd_id
and 
  a.type = 'buy'
and
  b.type = 'sell'
and
  b.date - a.date < 30
and
  a.amt+b.amt >2000000

Answer 2

你这题咋说没人解得开呢？
首先你没指定oracle的版本，我用21c版本写了个

with t ( cust_id, dt , prd_id,  amt, type) as (
select 'A123',  date'2022-01-01',  'aa11',  1000001, 'buy' from dual union all
select 'A123',  date'2022-01-05',  'aa11',  1000000, 'sell' from dual union all
select 'B123',  date'2022-01-06',  'bb22',  1000, 'buy' from dual union all
select 'B123',  date'2022-01-07',  'bb22',  1000, 'sell' from dual union all
select 'C123',  date'2022-01-08',  'aa11',  2000000, 'buy' from dual union all
select 'D123',  date'2022-01-09',  'aa11',  2000000, 'buy' from dual union all
select 'D123',  date'2022-03-09',  'aa11',  2000000, 'sell' from dual union all
select 'D123',  date'2022-03-10',  'aa11',  2000000, 'sell' from dual)
, t1 as (
select t.*,
bit_and_agg(case when type='sell' THEN 1 ELSE 0 END) over w1 as ct ,
sum(amt) over w1 as sum_amt
from t
window w1 as(partition by cust_id order by dt range between 30 preceding and current row)
)
select * from t where exists 
(select 1 from t1 where ct=0 and sum_amt>2000000 
and  t.cust_id=t1.cust_id and t.dt between t1.dt-30 and t1.dt)

t表是模拟数据，
t1的目的是增加两个列，即往前数30天是否存在多个类型，以及往前数30天求和，
低版本的oracle中，window 窗口需要放上去，这个可以改，
bit_and_agg这个函数是21c版本新增的，是按位与聚合运算，即只要有2个不同的值则返回0，主要原因是移动窗口不能用count(distinct)，当然低版本可以用其他方法，只是sql又变长了

---

还是写个低版本的处理方法吧

with t ( cust_id, dt , prd_id,  amt, type) as (
select 'A123',  date'2022-01-01',  'aa11',  1000001, 'buy' from dual union all
select 'A123',  date'2022-01-05',  'aa11',  1000000, 'sell' from dual union all
select 'B123',  date'2022-01-06',  'bb22',  1000, 'buy' from dual union all
select 'B123',  date'2022-01-07',  'bb22',  1000, 'sell' from dual union all
select 'C123',  date'2022-01-08',  'aa11',  2000000, 'buy' from dual union all
select 'D123',  date'2022-01-09',  'aa11',  2000000, 'buy' from dual union all
select 'D123',  date'2022-03-09',  'aa11',  2000000, 'sell' from dual union all
select 'D123',  date'2022-03-10',  'aa11',  2000000, 'sell' from dual)

, t1 as (
select t.*,
trunc(EXP(SUM(LN(case when type='sell' THEN 2 ELSE 3 END)) 
over (partition by cust_id order by dt range between 30 preceding and current row))) as ct ,
sum(amt) 
over (partition by cust_id order by dt range between 30 preceding and current row) as sum_amt
from t
)
select * from t where exists 
(select 1 from t1 where mod(ct,6)=0 and sum_amt>2000000 
and  t.cust_id=t1.cust_id and t.dt between t1.dt-30 and t1.dt)

这个sql在11g/12c/18c/19c/21c均测试通过。
主要和上面那个版本的区别，除了把窗口定义放上去了，更重要的一点是，将要做去重统计的两个值转换成两个质数，然后计算一列的乘积，如果最后这个乘积满足除以两个质数之积余0，那么说明它的因数肯定同时含有这两个质数，即同时含有多个值