sql语句优化 oracle

[qq_42230619]

现有一张商户交易的表 字段如下
id 商户id
open_date 开户日期
dt 交易流水具体时间
amt 交易金额
off 0代表入账 1 代表出账
需求是这样的：
计算各个商户每一笔出款距离其上一笔入款的时间间隔（分钟），并取所有时间间隔中的最小值。
我写的sql如下 但是执行半个多小时都没有结果 哪位大神帮我优化一下 十分感谢
SELECT
t3.id,
min(t3.jiange) as off1st_jiange_min
FROM
(
SELECT
t1.id,
t1.dt AS rukuan_time,
t2.dt AS qukuan_time,
(to_date(t1.dt, 'ddmonyyyy:HH24:mi:ss') -
to_date(t2.dt, 'ddmonyyyy:HH24:mi:ss')) * 24 * 60
AS jiange
FROM
Pol_Trans t1,
Pol_Trans t2
WHERE
t1.id = t2.id
AND t1.off = '0'
AND t2.off = '1'
AND to_date(t1.dt, 'ddmonyyyy:HH24:mi:ss') > to_date(t2.dt, 'ddmonyyyy:HH24:mi:ss')
ORDER BY t2.dt,t1.dt DESC
) t3
GROUP BY t3.id;

[qq_42230619]

数据量很大 三百多万条的数据

[「已注销」]

300W的数据不大，你试试这样执行要多久 SELECT t3.id, min(t3.jiange) as off1st_jiange_min FROM ( SELECT /*+ use_nl(t1,t2) */ t1.id, t1.dt AS rukuan_time, t2.dt AS qukuan_time, (to_date(t1.dt, 'ddmonyyyy:HH24:mi:ss') - to_date(t2.dt, 'ddmonyyyy:HH24:mi:ss')) * 24 * 60 AS jiange FROM Pol_Trans t1, Pol_Trans t2 WHERE t1.id = t2.id AND t1.off = '0' AND t2.off = '1' AND to_date(t1.dt, 'ddmonyyyy:HH24:mi:ss') > to_date(t2.dt, 'ddmonyyyy:HH24:mi:ss') ORDER BY t2.dt,t1.dt DESC ) t3 GROUP BY t3.id;

[「已注销」]

SELECT t3.id, min(t3.jiange) as off1st_jiange_min FROM ( SELECT /* use_nl(t2,t1) */ t1.id, t1.dt_start AS rukuan_time, t2.dt AS qukuan_time, (t2.dt - t1.dt_start) * 24 * 60 AS jiange FROM (select t.id,t.dt dt_start,nvl(lead(t.dt) over(partition by t.id order by t.dt),sysdate+30) dt_end from Pol_Trans t where AND t1.off = '0' ) t1 ,Pol_Trans t2 WHERE t1.id = t2.id AND t2.off = '1' and t2.dt>= t1.dt_start and t2.dt < t1.dt_end ) t3 GROUP BY t3.id; 这是我目前根据你原有sql改动的，dt我默认为时间类型了,思路是，先获取上次入账和下次入账的时间段，然后再获取出账落在那一段时间内，里面的hint是防止走hash的，id重复很多的时候默认哈希是跑不出来的

[卖水果的net]

楼主研究一下 lag 和 lead 这两个函数，应该能用上。

[qq_42230619]

lag 和 lead 考虑了 实现不了这个需求的 还有没有大神来指教了

[「已注销」]

你这种sql执行的快不快取决于Pol_Trans 数据大不大，交易次数多不多，如果不大的话可以用 /*+ use_nl(t1,t2) */来解决