-- LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

-- 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

-- LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

-- 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

-- FIRST_VALUE 取分组内排序后，截止到当前行，第一个值

-- LAST_VALUE  取分组内排序后，截止到当前行，最后一个值

-- 这几个函数不支持WINDOW子句

select

     t2.id

    ,t2.day

    ,t2.lead_default_day

    ,t2.lead_2_day

    ,t2.lag_default_day

    ,t2.lag_2_day

    ,t2.first_day_1

    ,t2.first_day_2

    ,t2.last_day_1

    ,t2.last_day_2

    ,(unix_timestamp(t2.lead_default_day)-unix_timestamp(t2.day))/3600 as diff_hour

from (

    select

         t1.id

        ,t1.day

        ,lead(t1.day) over(partition by t1.id order by t1.day)                         as lead_default_day

        ,lead(t1.day,1,'2018-01-01 00:00:00') over(partition by t1.id order by t1.day) as lead_2_day

        ,lag(t1.day) over(partition by t1.id order by t1.day)                          as lag_default_day

        ,lag(t1.day,1,'2018-01-01 00:00:00') over(partition by t1.id order by t1.day)  as lag_2_day

        ,first_value(t1.day) over(partition by t1.id order by t1.day)                  as first_day_1

        ,first_value(t1.day) over(partition by t1.id)                  as first_day_2

        ,last_value(t1.day) over(partition by t1.id order by t1.day)                   as last_day_1

        ,last_value(t1.day) over(partition by t1.id)                                   as last_day_2

    from (

        select 'a' as id, '2018-01-01 12:22:00' as day union all

        select 'a' as id, '2018-01-09 00:00:00' as day union all

        select 'a' as id, '2018-01-02 00:00:00' as day union all

        select 'a' as id, '2018-01-03 00:00:00' as day union all

        select 'a' as id, '2018-01-04 00:00:00' as day union all

        select 'b' as id, '2018-01-08 00:00:00' as day union all

        select 'b' as id, '2018-01-05 00:00:00' as day union all

        select 'b' as id, '2018-01-06 00:00:00' as day union all

        select 'b' as id, '2018-01-07 00:00:00' as day

    ) t1

) t2

;

+-----+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--+

| id  |         day          |   lead_default_day   |      lead_2_day      |      lag_2_day       |     first_day_1      |     first_day_2      |      last_day_1      |      last_day_2      |      diff_hour      |

+-----+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--+

| b   | 2018-01-05 00:00:00  | 2018-01-06 00:00:00  | 2018-01-06 00:00:00  | 2018-01-01 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-08 00:00:00  | 24.0                |

| b   | 2018-01-06 00:00:00  | 2018-01-07 00:00:00  | 2018-01-07 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-06 00:00:00  | 2018-01-08 00:00:00  | 24.0                |

| b   | 2018-01-07 00:00:00  | 2018-01-08 00:00:00  | 2018-01-08 00:00:00  | 2018-01-06 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-07 00:00:00  | 2018-01-08 00:00:00  | 24.0                |

| b   | 2018-01-08 00:00:00  | NULL                 | 2018-01-01 00:00:00  | 2018-01-07 00:00:00  | 2018-01-05 00:00:00  | 2018-01-05 00:00:00  | 2018-01-08 00:00:00  | 2018-01-08 00:00:00  | NULL                |

| a   | 2018-01-01 12:22:00  | 2018-01-02 00:00:00  | 2018-01-02 00:00:00  | 2018-01-01 00:00:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-09 00:00:00  | 11.633333333333333  |

| a   | 2018-01-02 00:00:00  | 2018-01-03 00:00:00  | 2018-01-03 00:00:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-02 00:00:00  | 2018-01-09 00:00:00  | 24.0                |

| a   | 2018-01-03 00:00:00  | 2018-01-04 00:00:00  | 2018-01-04 00:00:00  | 2018-01-02 00:00:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-03 00:00:00  | 2018-01-09 00:00:00  | 24.0                |

| a   | 2018-01-04 00:00:00  | 2018-01-09 00:00:00  | 2018-01-09 00:00:00  | 2018-01-03 00:00:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-04 00:00:00  | 2018-01-09 00:00:00  | 120.0               |

| a   | 2018-01-09 00:00:00  | NULL                 | 2018-01-01 00:00:00  | 2018-01-04 00:00:00  | 2018-01-01 12:22:00  | 2018-01-01 12:22:00  | 2018-01-09 00:00:00  | 2018-01-09 00:00:00  | NULL                |

+-----+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+----------------------+---------------------+--+