LZ, 你测试一下, 如果有问题详细说明
代码如下:
SQL> select telno, to_char(dt_date, 'yyyy-mm-dd hh24:mi') dt_date from tt;
TELNO DT_DATE
---------- ----------------
111 2016-10-01 00:00
111 2016-10-01 00:15
111 2016-10-01 00:45
222 2016-10-01 00:45
222 2016-10-01 01:00
222 2016-10-01 01:15
6 rows selected
SQL>
SQL> select telno,
2 count(*) cnt,
3 to_char(max(new_dt), 'yyyy-mm-dd hh24:mi') new_dt
4 from
5 (select telno,
6 dt_date,
7 rn,
8 decode(row_number() over(partition by telno, rn order by dt_date), 2, dt_date, null) new_dt
9 from
10 (select telno,
11 dt_date,
12 max(flag) over(partition by telno order by dt_date) rn
13 from
14 (select telno,
15 dt_date,
16 case
17 when ((dt_date - lag(dt_date, 1, dt_date) over(partition by telno order by rownum)) * 24 * 60) / 15 = 1 then
18 0
19 else
20 row_number() over(partition by telno order by dt_date)
21 end flag
22 from tt)))
23 group by telno, rn
24 having count(*) > 1
25 order by telno, rn;
TELNO CNT NEW_DT
---------- ---------- ----------------
111 2 2016-10-01 00:15
222 3 2016-10-01 01:00
SQL>