hive函数-lag()

1、准备数据

create table tmp.test_01 as
select '1001' as user_id, '2017-01-01' as login_date, 100 as pv
union all
select '1001' as user_id, '2017-01-02' as login_date, 90 as pv
union all
select '1001' as user_id, '2017-01-03' as login_date, 110 as pv
union all
select '1001' as user_id, '2017-01-04' as login_date, 200 as pv
union all
select '1001' as user_id, '2017-01-05' as login_date, 150 as pv
;

2、案例：求相对上周期的差值

-- 函数lag()

select 
    user_id
    ,login_date
    ,pv
    ,pv - lag(pv,1,0) over(partition by user_id order by login_date ) -- 本条数据 减去 lag()得到的上一条数据
from 
    tmp.test_01

1001    2017-01-01      100     100
1001    2017-01-02      90      -10
1001    2017-01-03      110     20
1001    2017-01-04      200     90
1001    2017-01-05      150     -50