Рассчитайте время, потраченное на одного пользователя на задачу в sql

У меня есть таблица, в которой у меня работают несколько пользователей, мне нужно рассчитать общее время, потраченное на пользователя, на основе заявленного статуса

 ISSUEID    Time             User       date     status
10101   2019-08-30 19:51:43 peter   2019-08-30  CLAIMED
10101   2019-08-30 19:51:46 peter   2019-08-30  CLAIMED
10101   2019-08-30 20:10:39 miller  2019-08-30  CLAIMED
10101   2019-08-30 21:21:14 miller  2019-08-30  CLAIMED
10101   2019-08-30 21:22:33 Renus   2019-08-30  CLAIMED
10101   2019-08-31 1:31:58  Renus   2019-08-31  CLAIMED
10101   2019-08-31 1:32:08  peter   2019-08-31  CLAIMED
10101   2019-08-31 1:32:12  peter   2019-08-31  CLAIMED
10101   2019-08-31 1:32:58  miller  2019-08-31  CLAIMED
10101   2019-09-04 23:16:24 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:16:26 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:16:50 peter   2019-09-04  CLAIMED
10101   2019-09-04 23:17:01 Renus   2019-09-04  CLAIMED
10101   2019-09-04 23:17:06 Renus   2019-09-04  CLAIMED

Желаемый результат

IssueID user    timespent
10101   Peter   33
10101   Miller  4285
10101   Renus   30
Источник

2 ответа

Решение

Ниже приведен стандартный SQL BigQuery.

#standardSQL
SELECT ISSUEID, user, SUM(timespent) timespent
FROM (
  SELECT ISSUEID, user, TIMESTAMP_DIFF(MAX(time), MIN(time), SECOND) timespent
  FROM (
    SELECT *, COUNTIF(start) OVER(ORDER BY time) grp
    FROM (
      SELECT *, IFNULL(LAG(User) OVER(ORDER BY time), '') != User AS start 
      FROM `project.dataset.table`
    )
  )
  GROUP BY ISSUEID, user, grp
)
GROUP BY ISSUEID, user
Источник

Использовать DATETIME_DIFF() и перед этим преобразовать дату и время в дату, используя DATETIME_TRUNC

SELECT
  ISSUEID,
  User,
  DATETIME_DIFF(
    min(DATETIME_TRUNC(Time, DAY)), 
    max(day), 
    day)
FROM table 
GROUP BY ISSUEID, User
Источник
Другие вопросы по тегам