Hive求连续登录问题

62 阅读 0 评论 41 点赞

我是靠谱客的博主隐形眼睛，这篇文章主要介绍Hive求连续登录问题，现在分享给大家，希望可以做个参考。

学生表中按班级求年龄前三

dense_rank:有并列排名，依次递增（开窗函数相当于Top-N）

复制代码

1
2
3
4
5
select * from (

    select *,dense_rank() over(partition by clazz order by age)as num from students

) as t where t.num<=3;

#### 连续登陆问题

> 在电商、物流和银行可能经常会遇到这样的需求：统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间、间隔天数等

##### 数据：

> 注意：每个用户每天可能会有多条记录

##### 建表语句

复制代码

1
2
3
4
5
6
7
8
9
create table deal_tb(

    id string

    ,datestr string

    ,amount string

)row format delimited fields terminated by ',';

#### 分析：

1.去重金额求sum() group by id,datestr

2.row_number() as index

3.datestr-index

//每天的数据汇总

复制代码

1
2
3
4
5
6
7
8
9
10
11
with datesum as(select id,datestr,sum(amount)as amount from deal_tb group by id,datestr)

    ,dateindex as(select *,row_number() over(partition by id order by datestr)as index  from datesum)

select *

      ,date_sub(datestr,index)

from

     dateindex;

统计用户连续交易的总额、连续登陆天数、连续登陆开始和结束时间

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
with datesum as(select id,datestr,sum(amount)as amount from deal_tb group by id,datestr)

    ,dateindex as(select *,row_number() over(partition by id order by datestr)as index  from datesum)

    ,dateend as (select *,date_sub(datestr,index)as gp from dateindex)

select id

      ,gp

      ,count(*)

      ,sum(amount)

      ,min(datestr)

      ,max(datestr)

from

     dateend group by id,gp ;

间隔天数

lag(col,n)往前第n行数据

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
with datesum as(select id,datestr,sum(amount)as amount from deal_tb group by id,datestr)

    ,dateindex as(select *,row_number() over(partition by id order by datestr)as index  from datesum)

    ,dateend as (select *,date_sub(datestr,index)as gp from dateindex)

select id

      ,gp

      ,count(*)

      ,sum(amount)

      ,datediff(gp,lag(gp,1) over(partition by id order by gp))

from

     dateend group by id,gp ;