今天头儿遇到一个问题查询用户购买时间间隔 n多努力终于搞定 以便备忘
select b1.userid, trunc(b2.createtime) - trunc(b1.createtime)
from (select t.createtime,
t.userid,
ROW_NUMBER() over(partition by t.userid order by t.createtime asc) as num1
from t_orders t
order by t.createtime desc) b1
left outer join (
select t.createtime,
t.userid,
ROW_NUMBER() over(partition by t.userid order by t.createtime asc) as num1
from t_orders t
order by t.createtime desc) b2
on b1.userid = b2.userid
where b1.num1 = 1
and b2.num1 = 2