如何计算Bigquery中两个事件之间的时间? [英] How to Calculate Time between two events in Bigquery?

问题描述

我有一个具有以下架构的表.

I have a table with the following schema.

   Date          Time          Event_Type
2018-02-12    02:32:00 AM     Session_Start
2018-02-12    03:15:00 AM        event
2018-02-12    04:05:00 AM     Session_Start
2018-02-12    10:10:00 AM        event
2018-02-12    10:15:00 AM        event
2018-02-12    10:25:00 AM        event
2018-02-13    02:32:00 PM     Session_Start
2018-02-13    03:15:00 PM        event
2018-02-13    04:05:00 AM     Session_Start

我想计算用户在特定会话中花费的时间.因此，会话持续时间介于Session_start和下一个Session_Start之前的最后一个事件之间.

I want to calculate the time a user spends during a particular session. So, a session duration is between the Session_start and the last event before the next Session_Start.

我想到了使用以下命令将Session_Start分为一组:

I thought of putting Session_Start as a group using the following command:

WITH grps AS (
  SELECT Date, Time, Event_Type, 
    COUNTIF(Event_Type = 'Session_Start') OVER(PARTITION BY Date ORDER BY Time) grp
  FROM `project.events`
)

但是，这似乎毫无用处，除非我找到在session_start之前到达事件的方法.任何人都知道在这种情况下可以做什么?

But this seems useless unless I find out a way to get to the event before the session_start. Any one have an idea what can be done in this case?

推荐答案

我认为您的做法正确.然后，您想要聚合.如果您的日期/时间在单个 datetime 列中，则可以执行以下操作:

I think you are on the right track. Then you want aggregation. If your date/time were in a single datetime column, you would do:

WITH grps AS (
      SELECT datetime, Event_Type, 
             COUNTIF(Event_Type = 'Session_Start') OVER (ORDER BY datetime) as grp
      FROM `project.events`
     )
SELECT min(datetime), max(datetime),
       datetime_diff(min(datetime), max(datetime), second) as seconds_diff
FROM grps
GROUP BY grp;

如果您的日期/时间值为字符串，则可以执行以下操作:

If your date/time values are strings, you can do:

WITH grps AS (
      SELECT parse_datetime('%Y-%m-%d %I:%M:%S %p', concat(date, ' ', time) ) as datetime, Event_Type, 
             COUNTIF(Event_Type = 'Session_Start') OVER (ORDER BY datetime) as grp
      FROM `project.events`
     )

这篇关于如何计算Bigquery中两个事件之间的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！