|
设定有一个日志表中有用户的id和用户访问时间(date类型),
现在想统计用户两次访问之间的间隔,这个用sql如何做呢?
示例如下:
- SQL> create table test (id int , crdate date);
- Table created
- SQL> insert into test values(1,sysdate - 1);
- 1 row inserted
- SQL> insert into test values(2,sysdate);
- 1 row inserted
- SQL> insert into test values(3,sysdate +1) ;
- 1 row inserted
- SQL> select id , to_char(crdate,'yyyy-mm-dd hh24:mi:ss') self,
- 2 to_char(lag(crdate,1) over(order by id) ,'yyyy-mm-dd hh24:mi:ss') prev,
- 3 to_char(lead(crdate,1) over(order by id),'yyyy-mm-dd hh24:mi:ss') next
- 4 from test ;
- ID SELF PREV NEXT
- ----- ------------------- ------------------- -------------------
- 1 2016-03-18 15:29:32 2016-03-19 15:29:32
- 2 2016-03-19 15:29:32 2016-03-18 15:29:32 2016-03-20 15:29:32
- 3 2016-03-20 15:29:32 2016-03-19 15:29:32
复制代码
|
|