关于时间段总和的查询

Question

sign db_username db_ip beginTime endTime
downloadFiles tom 125.92.142.224 1378063471 1378063472
downloadFiles tom 125.92.142.224 1378072128 1378072129
downloadFiles tom2 84.14.196.239 1378077892 1378077893
downloadFiles tom2 84.14.196.239 1378077893 1378077894
downloadFiles tom2 84.14.196.239 1378077894 1378077895
downloadFiles tom2 84.14.196.239 1378077895 1378077896
downloadFiles tom2 84.14.196.239 1378077896 1378077897
downloadFiles tom2 84.14.196.239 1378077897 1378077898
downloadFiles tom2 84.14.196.239 1378079181 1378079182
downloadFiles tom2 84.14.196.239 1378079181 1378079182
downloadFiles tom2 84.14.196.239 1378079182 1378079183
downloadFiles tom2 84.14.196.239 1378079182 1378079183
downloadFiles tom2 84.14.196.239 1378079184 1378079185
downloadFiles tom2 84.14.196.239 1378079184 1378079185
downloadFiles tom2 84.14.196.239 1378079184 1378079185
downloadFiles tom2 84.14.196.239 1378079185 1378079186
downloadFiles tom2 84.14.196.239 1378079185 1378079186
downloadFiles tom2 84.14.196.239 1378079185 1378079186
downloadFiles tom2 84.14.196.239 1378079188 1378079189
downloadFiles tom2 84.14.196.239 1378079188 1378079189
downloadFiles tom2 84.14.196.239 1378079189 1378079190
downloadFiles tom2 84.14.196.239 1378079190 1378079191
downloadFiles tom2 84.14.196.239 1378079190 1378079191
downloadFiles tom2 84.14.196.239 1378079191 1378079192
downloadFiles tom2 84.14.196.239 1378079192 1378079193

我数据库里有这样的数据(关于时间值，我己转为unix 时间戳，方便比较)
现在我想要查询一个用户不重复的时间段的总和(如有重复的时间段，不计入总和，只计一次)
如　tom 第一条　为　2013-09-11 18:22:34 as T1 2013-09-11 18:22:54 as T2
　　　　第二条　为　2013-09-11 18:23:34 as T3 2013-09-11 18:24:54 as T4 　
SUM=(T2-T1)+(T4-T3) boz T3>T2
如果 第二条　为　2013-09-11 18:24:00 as T3 2013-09-11 18:24:54 as T4
SUM=(T4-T1) boz T3T1
如果 第二条　为　2013-09-11 18:22:37 as T3 2013-09-11 18:22:50 as T4
sum=T2-T1 boz T3<T1 && T4<T1

想将各个用户的时间段总和求出来
结果集为
downloadFiles tom 125.92.142.224 TotalTime 15(seconds)
downloadFiles tom2 84.14.196.239 TotalTime 309(seconds)
分组为sign/username/ip

Answer 1

该回答引用CHATGPT

您可以使用以下SQL语句求出每个用户不重复的时间段总和：

WITH temp_table AS (
  SELECT 
    sign, 
    db_username, 
    db_ip, 
    LEAD(beginTime) OVER (PARTITION BY db_username ORDER BY beginTime) AS next_beginTime,
    endTime - beginTime AS time_difference
  FROM 
    your_table
)
SELECT 
  sign, 
  db_username, 
  db_ip, 
  SUM(CASE WHEN next_beginTime IS NOT NULL AND next_beginTime <= endTime THEN 0 ELSE time_difference END) AS total_time
FROM 
  temp_table
GROUP BY 
  sign, 
  db_username, 
  db_ip;

上面的语句使用了LEAD函数，它可以返回每一行之后的第一行的某一列的值（在本例中是下一行的beginTime）。

---

接下来的CASE语句用于判断两个时间段是否重叠，如果不重叠则把它们的差值累加到总和中。最后，通过GROUP BY语句按照sign、db_username和db_ip分组，求出每一组的总和。

---

最后的结果应该是类似于：

sign           db_username  db_ip                 total_time
downloadFiles  tom          125.92.142.224       15
downloadFiles  tom2         84.14.196.239        309