mysql - 每个user_id的时间总和

Question

好的，我有一个表，其中每个 user_id 都有 operation_timestamp。要检查每个 user_id 执行操作需要多长时间，我使用查询 max(operation_timestamp)-min(operation_timestamp) ，然后将其转换为小数，假设我得到的结果是:

  SHIFT   USER ID       MIN         MAX       MAX-MIN    DECIMAL
  shift1  user_1     08:19:42    09:55:20    01:35:37     1.59
  shift2  user_2     10:04:27    10:28:22    00:23:54     0.40
  shift2  user_3     10:44:07    10:55:58    00:01:51     0.04
  shift2  user_4     06:25:33    10:51:52    04:26:19     4.44

现在我的问题是:如何计算出小数点后的时间总和，以便获得所有用户在事件上花费的总时间？如下所示:

 SHIFT     TOTAL_DECIMAL
  shift1        1.59
  shift2        4.88

我尝试过相同的查询，但没有 group by user_id 函数，但它随后计算最大值和最小值而不查看单独的 user_id，所以假设它将计算 shift2 总计如 06:25:33 (user_4) 到 10:55:58 (user_3)，这会得到小数点后 4.45 的结果。

这是我使用但没有成功的查询:

select
case 
when SUBSTR(a.operation_ts,12,13) between '00:00:00.000' and '05:59:59.000' then 'Nights'
when SUBSTR(a.operation_ts,12,13) between '06:00:00.000' and '13:59:59.000' then 'Days'
when SUBSTR(a.operation_ts,12,13) between '14:00:00.000' and '21:59:59.000' then 'Lates'
when SUBSTR(a.operation_ts,12,13) between '22:00:00.000' and '23:59:59.000' then 'Nights'
else 'other'
end as shift,
a.userid,
substr(min(a.operation_ts),11,9),
substr(max(a.operation_ts),11,9),
substr((max(a.operation_ts)-min(a.operation_ts)),10,9) as time_on_go,
round(((substr((max(a.operation_ts)-min(a.operation_ts)),11,2))*3600+(substr((max(a.operation_ts)-min(a.operation_ts)),14,2))*60+(substr((max(a.operation_ts)-min(a.operation_ts)),17,2)))/3600,2) time_decimal
from dc_sys_common:user_operation a
where a.activity_code = 1012
and date(a.operation_ts) between today and today+1
and SUBSTR(a.operation_ts,12,9) between '00:00:00' and '21:59:59'
group by userid, shift
having max(a.operation_ts)-min(a.operation_ts)>'0 00:00:01.000'
order by shift asc

Answer 1

在嵌套的第一个查询上进行 GROUP BY SHIFT:

SELECT `SHIFT`, SUM(`DECIMAL`)
FROM
(
    -- your query here
)
GROUP BY `SHIFT`