sql - 用户交易时间差

Question

Table: txn

customer_id | time_stamp 
-------------------------
1           | 00:01:03
1           | 00:02:04
2           | 00:03:05
2           | 00:04:06

希望查询 customer_id 的每笔第一笔交易和下一笔交易之间的时间差

结果：

客户编号 | 时差

1 | 61

从 txn 中选择 customer_ID, ...

标签： sqlsql-servergoogle-bigquery

Answer 1

你想要lead()。. . 但众所周知，日期/时间函数是特定于数据库的。在 SQL Server 中：

select t.*,
       datediff(second,
                time_stamp,
                lead(time_stamp) over (partition by customer_id order by time_stamp)
               ) as diff_seconds
from t;

在 BigQuery 中：

select t.*,
       timestamp_diff(time_stamp,
                      lead(time_stamp) over (partition by customer_id order by time_stamp),
                       second
                     ) as diff_seconds
from t;