hive中自增序列，连续登陆及拉链表

1，实现hive中的自增列

           

with tmp as(
select
coalesce(max(orders),0) as rk
from increase_csv ic 
),
source_table as(
SELECT
  idfa
, sequence
, constant
,load_date
FROM csv )
select
csv.idfa,
csv.sequence,
csv.constant,
csv.load_date as update_date,
tmp.rk+row_number() over() as orders
from source_table as csv
     cross join tmp
     
向 increase_csv 表中新增数据并生成自增序列，csv 表为源数据表。

先查 increase_csv 表中已有记录最大的序列，如果 increase_csv 表中还没有记录，

利用coalesce函数返回0。然后使用cross join连接生成 source_table 表和 最大的序列值 的笛卡尔集，

最后使用row_number()函数生成行号，并将行号与 最大的序列值 相加的值，作为新装载记录的 序列值。

2，判断连续登陆

          1,先把数据按照用户id分组，根据登录日期排序。

2,用登录日期减去排序数字rn，得到的差值日期如果是相等的，则说明这两天肯定是连续的。

3,根据user_id和日期差date_diff 分组，最小登录日期即为此次连续登录的开始日期start_date，最大登录日期即为结束日期end_date，登录次数即为分组后的count(1)

select

t2.user_id         as user_id

,count(1)           as times

,min(t2.login_date) as start_date

,max(t2.login_date) as end_date

from

(

    select

     t1.user_id

    ,t1.login_date

    ,date_sub(t1.login_date,rn) as date_diff

    from

    (

        select

         user_id

        ,login_date

        ,row_number() over(partition by user_id order by login_date asc) as rn

        from

        wedw_dw.t_login_info

    ) t1

) t2

group by

t2.user_id

,t2.date_diff

having count(1) >= 3;

3，拉链表大致实现逻辑

         拉链表：记录一个事物从开始，一直到当前状态的所有变化的信息

适用情况：

1.数据量比较大

2.表中的部分字段会被更新

3.需要查看某一个时间点或者时间段的历史快照信息

4.更新的比例和频率不是很大

优点

1、满足反应数据的历史状态

2、最大程度节省存储

ods层数据源表:

create table order_info(

order_id     string    COMMENT '订单id'

,order_status string    COMMENT '订单状态'

,create_time  timestamp COMMENT '创建时间'

,update_time  timestamp COMMENT '更新时间'

) COMMENT '订单表'

partitioned by (date_id string)

dwd层拉链表:

create table  order_info_df(

order_id     string    COMMENT '订单id'

,order_status string    COMMENT '订单状态'

,create_time  timestamp COMMENT '创建时间'

,update_time  timestamp COMMENT '更新时间'

,start_dt     date      COMMENT '开始生效日期'

,end_dt       date      COMMENT '结束生效日期'

) COMMENT '订单表'

partitioned by (date_id string)

insert into table order_info_df partition(date_id = '2020-09-11')

--更新当前周期变更的上周期数据

select

t1.order_id    

,t1.order_status

,t1.create_time

,t1.update_time

,t1.start_dt as start_dt

--对比上一周期数据和最新周期数据中是否存在同一订单信息，如果存在将上一周期数据结束时间更新，否则不变化

,case when t1.end_dt='9999-12-31' and t2.order_id is not null  then cast(t1.date_id  as date) else t1.end_dt  end as  end_dt

from (select * from order_info_df where date_id = '2020-09-10') t1

left join (select * from order_info where date_id = '2020-09-11') t2

on t1.order_id=t2.order_id

--最新周期的全量数据

union all

SELECT

t1.order_id    

,t1.order_status

,t1.create_time

,t1.update_time

,cast(to_date(t1.update_time) as date) as start_dt

,cast('9999-12-31' as date) as end_dt

FROM (select * from order_info where date_id = '2020-09-11') t1