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.username)/2 ##from ofloginlog t
by t.username
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*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 codeselect
username,sum
(b),count (username) / 2
from (select id, username, (CREATIONDATE - lgtime) * 24 * 60 * 60 as b
(type) over(partition by username order by
CREATIONDATE) lgtype,lag(CREATIONDATE) over(partition by username order by
CREATIONDATE) lgtimefrom ofloginlog t))
by username
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
g.username, g.
time, h.
count
from ( select t.username,
(t.CREATIONDATE) - min(t.CREATIONDATE)) * 24 * 60 * 60) as time
# from ofloginlog t, ofuser b ## ## and t.username = b.username
(select t.username, count(t.username) as
##
##where g.username = h.username
by count desc
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
Related recommendations: .net2.0 connection Mysql5 database configuration
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!