sql - Big Query 中的窗口函数和时差

Question

我有一个大的查询表定义为:

+----+----------------------------+------------+| id |            time            |   event    |+----+----------------------------+------------+|  1 | 2015-10-01 16:31:48.000000 | signup     ||  1 | 2015-10-01 16:41:48.000000 | 1_purchase ||  1 | 2015-10-01 16:51:48.000000 | 2_purchase ||  2 | 2015-10-01 16:31:48.000000 | signup     ||  2 | 2015-10-01 16:41:48.000000 | 1_purchase ||  3 | 2015-10-01 16:31:48.000000 | signup     |+----+----------------------------+------------+

我想计算每个 id 组 (1,2,3) 内的时间差，得到的结果为:

+----+----------------------------+------------+-----------------+--+| id |            time            |   event    | timedifference  |  |+----+----------------------------+------------+-----------------+--+|  1 | 2015-10-01 16:31:48.000000 | signup     | -               |  ||  1 | 2015-10-01 16:41:48.000000 | 1_purchase | 00:10:00.000000 |  ||  1 | 2015-10-01 16:61:48.000000 | 2_purchase | 00:20:00.000000 |  ||  2 | 2015-10-01 16:31:48.000000 | signup     | -               |  ||  2 | 2015-10-01 16:41:48.000000 | 1_purchase | 00:10:00.000000 |  ||  3 | 2015-10-01 16:31:48.000000 | signup     | no_purchase     |  |+----+----------------------------+------------+-----------------+--+

经过一些研究，我想我需要使用窗口函数...但我想不出任何解决方案。非常感谢任何帮助!最好的，五、

Answer 1

select 
  id, time, event, 
  time(sec_to_timestamp((timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time))))) as timedifference,
  (timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time)))/60 as timefifference_in_min,

  right('0' + string(datediff(timestamp(time),timestamp(prev_time))),2) + ' ' +
  time(sec_to_timestamp((timestamp_to_sec(timestamp(time)) -     
    timestamp_to_sec(timestamp(prev_time))))) as timedifference_as_dd_hh_mm_ss

from (
  select 
    id, time, event,
    lag(time) over(partition by id order by time) as prev_time
  from (
  select f0_ as id, f1_ as time, f2_ as event from
    (select 1, '2015-10-01 16:31:48.000000', 'signup'),
    (select 1, '2015-10-01 16:41:48.000000', '1_purchase'),
    (select 1, '2015-10-01 16:51:48.000000', '2_purchase'),
    (select 2, '2015-10-01 16:31:48.000000', 'signup'),
    (select 2, '2015-10-01 16:41:48.000000', '1_purchase'),
    (select 3, '2015-10-01 16:31:48.000000', 'signup')
  )
)
order by id, time