本帖最后由 bell6248 于 2016-11-8 16:57 编辑
代码测试如下, 如果有问题, 详细说明
SQL> select * from pp1;
ID TRAN_DATE AMOUNT
---------- ----------- ----------
1 2013/1/1 100
2 2013/4/3 90
3 2015/5/2 1000
4 2015/5/3 2040
5 2015/5/5 200
6 2015/5/8 160
7 2015/5/12 1054
8 2015/5/13 2024
9 2015/5/16 343
20 2015/5/17 343
21 2015/5/20 1315
22 2015/5/22 1235
25 2015/5/25 5555
100 2015/7/24 5552
101 2015/12/11 22
102 2016/3/19 11
103 2016/7/11 222
104 2016/10/10 4444
105 2016/11/11 222
19 rows selected
SQL>
SQL> select id,
2 tran_date,
3 amount,
4 tran_date_diff days_diff
5 from
6 (select id,
7 tran_date,
8 amount,
9 tran_date_diff,
10 count(*) over(partition by flag, trunc(tran_date, 'mm')) cnt
11 from
12 (select id,
13 tran_date,
14 amount,
15 tran_date_diff,
16 max(flag) over(order by id, tran_date) flag
17 from
18 (select id,
19 tran_date,
20 amount,
21 trunc(tran_date, 'mm'),
22 tran_date - lag(tran_date) over(order by id, tran_date) tran_date_diff,
23 months_between(trunc(tran_date, 'mm'), lag(trunc(tran_date, 'mm')) over(order by id, tran_date)) diff,
24 case when months_between(trunc(tran_date, 'mm'), lag(trunc(tran_date, 'mm')) over(order by id)) > 12 then id else null end flag
25 from pp1))
26 where flag is not null)
27 where cnt > 10
28 order by 1,2
29 /
ID TRAN_DATE AMOUNT DAYS_DIFF
---------- ----------- ---------- ----------
3 2015/5/2 1000 759
4 2015/5/3 2040 1
5 2015/5/5 200 2
6 2015/5/8 160 3
7 2015/5/12 1054 4
8 2015/5/13 2024 1
9 2015/5/16 343 3
20 2015/5/17 343 1
21 2015/5/20 1315 3
22 2015/5/22 1235 2
25 2015/5/25 5555 3
11 rows selected
SQL>