sql - 查找同一列的时间戳差异，但依赖于其他列的事件

Question

我有一个自行车时间表，其中有一列日期和一列事件，事件列的值如bike_deployed, bike_assembled, journey, bike_cleaning, 对于所有这些事件我们都有一个特定的时间。我想要的是时间上的bike_deployed-bike_assembled差异bike_cleaning-bike_deployed和差异在自行车清洁活动中以及其他列中的所有这些差异。

我正在使用 hivesql，这就是表格的样子：

    timeline                events   
    2018-01-19 08:18:05     BIKE_ASM
    2018-02-25 06:36:52     ride
    2018-02-26 00:00:00     BIKE_DPLY
    2018-02-26 08:34:56     cleaning
    2018-03-02 13:32:00     ride
    2018-03-06 13:38:42     cleaning

我想要的是 ：

   timeline                events    dply-asm  ride-dply  cleaning-dply cleans  
    2018-01-19 08:18:05     BIKE_ASM    38       4            0            8
    2018-02-25 06:36:52     ride                
    2018-02-26 00:00:00     BIKE_DPLY
    2018-02-26 08:34:56     cleaning
    2018-03-02 13:32:00     ride
    2018-03-06 13:38:42     cleaning

标签： sqlhiveql

Answer 1

你可以试试这个，但请注意它是按 bike_id 分组的，并且只发现最后一次骑行和部署、最后一次清洁和部署等之间的区别

select
    bike_id,
    datediff(
        date(max(case when events = 'BIKE_DPLY' then timeline else null end)),
        date(max(case when events = 'BIKE_ASM' then timeline else null end))) as dply_asm,

    datediff(
        date(max(case when events = 'ride' then timeline else null end)),
        date(max(case when events = 'BIKE_DPLY' then timeline else null end))) as ride_dply,

    datediff(
        date(max(case when events = 'cleaning' then timeline else null end)),
        date(max(case when events = 'BIKE_DPLY' then timeline else null end))) as cleaning_dply,

    sum(case when events = 'cleaning' then 1 else 0 end) as cleans
from
    <table_name>
group by
    bike_id