sql - 计算 session 中两个 Action 之间的时间

Question

我有如下表结构

id | sessionId | event | created_on
---|-----------|-------|--------------------
 1 | 1         | view  | 2016-01-01 12:24:01
 2 | 1         | buy   | 2016-01-01 12:25:05
 3 | 2         | view  | 2016-01-01 12:25:09
 4 | 1         | view  | 2016-01-01 12:27:10
......

我正在尝试获取两个事件之间的时间，在这种特殊情况下，我想知道一个 session 中的“第一次查看”和“第一次购买”事件之间经过了多少时间。

如何在 GROUP BY 中申请 WHERE 数据？基本上我想按 sesssionID 分组，只获取包含查看和购买操作的 session ，我想在单个 session 的 FIRST view 和 FIRST buy 事件之间获得时间。

如何达到要求的结果？

谢谢

Answer 1

希望 google-bigquery 支持的相当常见的 sql 语法

select sessionId, 
  min(case event when 'view' then created_on end) as firstView,
  min(case event when 'buy' then created_on end) as firstBuy
from t
where event in ('view', 'buy')
group by sessionId
having max(event) != min(event)