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