SQL comparison of time difference between two adjacent records

In the afternoon, I saw that a statistical report was generated for the project, in which the statistical time difference between two adjacent records recorded in the XX table is

The data in the table is as follows:

It is required that the creation time difference between two adjacent records, such as the 1st and 2nd record, be calculated.

That is,

zhouhui 5 seconds

                                                   24 seconds                                                                                       

##select

t.username,(

max( t.CREATIONDATE)-

min

( t.CREATIONDATE))*24*60*60,
1. count

   (t.username)/2 ##from ofloginlog t

2. ##--where USERNAME = 'zhouhui'

3. group

   by t.username

4. Statistics of user online time are calculated through grouping (that is, the difference between the two records before and after )

   Rendering: #Explanation that the last field is used to count the number of user logins.

The default value for oracle to subtract two times is the number of days

The default value for oracle to subtract two times is the number of days*24, which is the number of hours difference

The default value for subtraction is days*24*60, which is the number of minutes difference.

The default value for oracle subtraction between two times is number of days*24*60*60, which is the number of seconds difference.

Method 2:

Sql code

select

username,

sum

(b),

count (username) / 2

1. from (select id, username, (CREATIONDATE - lgtime) * 24 * 60 * 60 as b

2.                                                                                         (type) over(partition by username order by

   CREATIONDATE) lgtype,

3. lag(CREATIONDATE) over(partition by username order by

   CREATIONDATE) lgtime

4. from ofloginlog t))

5. ##-- where USERNAME = 'zhouhui')

6. group

   by username

## The effect is the same and I won’t post it here

7. Reviewed the basic SQL again haha ​​20100520 Some changes in requirements require that the number of statistics is not the sum and mean of TYPE 1 and 0 records, but only the value of TYPE=0, This SQL grouping cannot be like this. I thought about it and improved the SQLSql code

select

g.username, g.

time

, h.

count

1. from ( select t.username,

## floor((
2. max

   (t.CREATIONDATE) - min(t.CREATIONDATE)) * 24 * 60 * 60) as time

3. # from ofloginlog t, ofuser b ##                                                            ##         and t.username = b.username 

4.                                                                                                              (select t.username, count(t.username) as

count

5.                                                                                                                                            

   ##                                                                                     

6. ##where g.username = h.username

7. ##order

   by count desc

8. Query results The analysis time difference is the difference between the two sets, and the number of statistics later is only the number of records with a separate restriction of TYPE=0. The number of statistical data is inconsistent, so it is difficult to implement it in one group. The idea is to implement USERNAME and TIME first. The records count USERNAME and the number of records that satisfy TYPE=0 and merge the two results through the inline relationship of SELECT XX FROM A B 2 temporary tables to achieve the merged result set

This article explains SQL adjacent 2 Compare the time difference of records. For more related content, please pay attention to the PHP Chinese website.

9. Related recommendations: .net2.0 connection Mysql5 database configuration

10. Detailed explanation of the difference between cookie and session

The above is the detailed content of SQL comparison of time difference between two adjacent records. For more information, please follow other related articles on the PHP Chinese website!