用户画像基础（五）

2024-12-18 10:25:04 # 项目基础 # 用户画像 # APP层用户状态详细讲解 #用户画像

app层设计

我们这里是使用的是impala的引擎，如果单用户画像查询的话可能就直接hfile的文件或者api的格式传输到hbase就好，

这里注意hfile和api格式的优势和缺点

这里我们使用impala，impala可以对批量进行人群圈选，使用view视图里面就有hive离线的数据和kudu的实时的数据，impala就可以进行批量查询和批量筛选，可以既获得离线的数据也获取实时的数据

这里的_a表表示我们不存放历史数据，只展示最新的数据

CREATE EXTERNAL TABLE loan.app_user_personas_label_a (
-- 基本属性-人口属性:
  user_id bigint COMMENT '用户ID',
  user_sex string COMMENT '性别',
  user_name string COMMENT '姓名',
  user_chinese_zodiac COMMENT '用户生肖',
-- 基本属性-地理信息:
  user_city string COMMENT '城市',
  phone_city string COMMENT '手机号城市',
  ip_city_level string COMMENT '城市等级',
-- 基本属性-设备属性
  --device_id string COMMENT '设备id',
  device_os string COMMENT '设备类型 iOS，android',
-- 业务流程-业务属性:
  first_channel string COMMENT 'first_channel',
  second_channel string COMMENT 'second_channel',
-- 业务流程-用户访问:
  user_create_time string COMMENT '用户生成时间',
  7d_pv_cnt int COMMENT '7天内pv页面的访问次数',
  7d_pv_event_cnt int COMMENT '7天内event页面的访问次数'
  365d_pv_cnt int COMMENT '365天内pv页面的访问次数',
  365d_pv_event_cnt int COMMENT '365天内event页面的访问次数'
-- 业务流程-用户注册:
  bind_mobile_succ_time string  COMMENT '用户绑定手机号成功时间'
-- 业务流程-用户授信:
  first_credit_apply_time string COMMENT '首次申请授信时间'
  first_credit_apply_succ_time string COMMENT '首次申请授信成功时间'
-- 业务流程-用户借款:
  first_loan_apply_time string COMMENT '首次借款申请时间',
  first_loan_apply_succ_time string COMMENT '首次借款成功申请时间',
-- 业务流程-用户还款:
  recent_repay_succ_time string comment '最近一次还款成功时间',
  next_due_day string comment '下次应还时间',
-- 用户权益-额度:
  curr_total_amt string comment '当前总额度',
  
-- 用户权益-期限&利率:
  user_fixed_equity string comment  '固定权益信息',
  user_tmp_equity string comment  '临时权益信息',
-- 用户权益-减免权益&优惠券:
  is_user_discount string comment  '用户是否有满减权益',
-- 用户营销:
  first_market_time string comment  '用户第一次被打标时间',
-- 用户价值:
  user_status_level string comment  '用户生命周期',--这个标签有一些敏感，主要是在借贷里有引出期，借贷期，成长期，成熟期，青年期，休眠期，流失期
  user_market_level string comment  '用户状态',
-- 用户风险:
  user_duotou_score string comment  '用户多头分数',
-- 用户特征:
  weibo_user_verified string comment  '是否是微博用户',
  
 )
COMMENT '用户画像标签表'
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/app_user_personas_label_a';

   insert overwrite table loan.app_user_personas_label_a
   select user_id
          ,
          ,
          ,
     from (select user_id
                  ,
             from loan.dws_user_base_info_d
            where dt='${dt}'
           )user_base
left join (select user_id
                  ,
             from loan.dws_loan_user_base_info_d
            where dt='${dt}'
          )user_loan
        on user_base.user_id=user_loan.user_id
left join (select user_id
                  ,
             from loan.dws_decision_base_info_d
            where dt='${dt}'
          )decision
        on user_base.user_id=decision.user_id
 left join (select user_id
                  ,
             from event.dws_user_etk_event_info_d
            where dt='${dt}'
          )etk
        on user_base.user_id=etk.user_id

用户状态标签

营销同学现在有一个需求，需要对所有的用户在借贷业务中所处的阶段进行标示，方便后续的营销活动中进行人群的圈选

需要的用户状态的标签有：

–# 1 新增uv

–# 2 之前访问未注册

–# 3 注册未申请授信

–# 4 注册申请授信未通过

–# 5 授信通过未申请过借款

–# 6 授信通过申请借款未通过

–# 7 活跃老客 (结清时间在30日内或最近30天申请过订单,且历史至少存在一笔结清订单或当前在贷)

–# 8 沉默老客 (结清30天以上无复借,30天内无申请记录，无在贷)

user_id	start_time_date	end_time_date	tag	tag_detail
User1	2024-01-01	2024-01-01	1	新增uv
User1	2024-01-02	2024-02-11	2	访问未注册
User1	2024-02-12	2024-04-30	4	注册申请授信未通过
User1	2024-04-31	2024-05-02	5	授信通过未申请过借款
User1	2024-05-03	2024-06-03	7	活跃老客
User1	2024-06-04	2024-07-14	8	沉默老客
User1	2024-07-15	2999-12-30	7	活跃老客

接到这个需求我们应该怎么处理

这里使用拉链表的问题是，如果我们要查看一个人的这十年之间的一个状态，如果我们做分区的话，一天一个分区，这样如果有一个客户是沉默老客，十年前下单一次，然后再也没有下单，我们岂不是需要每天都存放一条重复的数据存放十年，这种情况太离谱了，所以这里我们使用拉链表

create external table `loan.dws_user_market_level_d` (
  `user_id`               int                     comment '用户ID',
  `start_time_date`       string                  comment '开始日期',
  `end_time_date`         string                  comment '结束日期',
  `tag`                   string                  comment '用户类别id',
  `tag_detail`            string                  comment '用户类别释义'
  )comment '用户类别拉链表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dws_user_market_level_d';

1、业务需求的分析

2、拉链表的建设，应用

3、各种窗口函数，以及面试中排序题的解题思路

首先做需求拆分：判断哪些标签是独立的，哪些标签是互斥的，哪些标签是可以共存的

tag 进行分组：

tag=1是一组这个标签只需要单纯的给新增的用户打上标记就可以

tag=2-6是一组这组标签可以看作是业务过程，需要做的是对业务过程的判断，存在逻辑上的顺序和互斥关系

tag=7,8 是一组这组标签是我们人为的逻辑定义标签，他的逻辑依赖于状态的计算，而不是某个真实存在的业务过程

同时这3组标签又是独立，互斥的

Tag =1 新增uv:

需要拿到用户的创建时间作为新增标签的日期

tag=2-6:

tag=2 之前访问未注册

首先判断用户之前访问过，但是没有进行注册的动作，在tag=1的基础上多了一个对用户是否注册的判断

一般公司对用户是否注册都是通过手机号来做判断的

tag=3 注册未申请授信

首先判断用户之前注册过，但是没有进行授信，在tag=2的基础上多了一个对用户是否授信的判断

tag=4 注册申请授信未通过

首先判断用户申请过授信，但是没有授信成功的时间，在tag=3的基础上多了一个对用户授信是否通过的判断

tag=5 授信通过未申请过借款

首先判断用户授信通过，但是没有进行过借款，在tag=4的基础上多了一个对用户是否进行过借款的判断

tag=6 授信通过申请借款未通过

首先判断用户授信通过并且申请过借款，但是没有借款通过，在tag=5的基础上多了一个对用户是否借款成功的判断

tag=7-8:

tag=7 活跃老客 (结清时间在30日内或最近30天申请过订单,且历史至少存在一笔结清订单)

tag=8 沉默老客 (结清30天以上无复借,30天内无申请记录，无在贷)

这两个tag的逻辑可以看作是一个整体，首先判断用户是一个历史已经结清过的用户，然后根据他结清后的申请借款时间来判断他是一个活跃还是沉默的用户

with一张表是把这个临时表放在内存里面，速度会很快

create一张临时表的话会把这个临时表落地，等到进程结束之后再把这个临时表的文件删除掉。落盘对io资源比较大

with tag_1_6 as (
    select user.user_id
           ,user_create_time_date
           ,bind_mobile_succ_time_date
           ,first_credit_apply_time_date
           ,first_credit_apply_succ_time_date
           ,first_loan_apply_time_date
           ,first_loan_succ_time_date
      from (select user_id
                   ,to_date(user_create_time) as user_create_time_date
              from loan.dws_user_base_info_d
             where dt='${dt}'
           )user
 left join (select user_id
                   ,to_date(bind_mobile_succ_time) as bind_mobile_succ_time_date
                   ,to_date(first_credit_apply_time) as first_credit_apply_time_date
                   ,to_date(first_credit_apply_succ_time) as first_credit_apply_succ_time_date
                   ,to_date(first_loan_apply_time) as first_loan_apply_time_date
                   ,to_date(first_loan_succ_time) as first_loan_succ_time_date
              from loan.dws_loan_user_base_info_d
             where dt='${dt}'
            )loan
          on user.user_id= loan.user_id
)
with tag_7 as(
    select user_id
           ,apply_time
           ,7 as tag
      from (select user_id
                   ,min(settle_time) as min_settle_time
              from loan.dwd_repay_plan_d
             where dt='${dt}'
          group by user_id
           )settle_user
 left join (select user_id
                   ,apply_time
              from loan.dwd_loan_info_core_d
             where dt='${dt}'
           )loan_info
        on settle_user.user_id=loan_info.user_id
     where case when min_settle_time<apply_time
                then 7
                 end =7
)
    insert overwrite table loan.dws_user_market_level_d partition(dt='${dt}')
    select user_id
           ,start_time_date
           ,end_time_date
           ,tag
           ,case when tag = 1 then '新增uv'
                 when tag = 2 then '未注册'
                 when tag = 3 then '注册未申请授信'
                 when tag = 4 then '申请授信未通过'
                 when tag = 5 then '授信通过未申请借款'
                 when tag = 6 then '申请借款未通过'
                 when tag = 7 then '活跃老客'
                 when tag = 8 then '沉默老客'
             end tag_detail 
      from (--tag=1
            select user_id
                   ,user_create_time_date as start_time_date
                   ,user_create_time_date as end_time_date
                   ,1 as tag
              from tag_1_6
         union all
            --tag=2-6
            select time_queue.user_id
                   ,time_queue.start_time
                   ,time_queue.end_time
                   ,case when first_loan_apply_time_date>=time_queue.start_time and 

                  --这里需要倒排，因为满足6的话一定满足5，因为是有顺序的，但是我们显示的是6，所以需要这样倒排

                   first_loan_apply_time_date <end_time    --借款未通过
                         then 6
                         when first_credit_apply_succ_time_date>=time_queue.start_time and first_credit_apply_succ_time_date <end_time    --授信未借款
                         then 5
                         when first_credit_apply_time_date>=time_queue.start_time and first_credit_apply_time_date <end_time    --授信未通过
                         then 4
                         when bind_mobile_succ_time_date>=time_queue.start_time and bind_mobile_succ_time_date <end_time    --注册未授信
                         then 3
                         when date_add(user_create_time_date,1)>=time_queue.start_time and date_add(user_create_time_date,1)< end_time   --未注册
                         then 2
                         end tag
              from (select user_id
                               ,change_time start_time
                               ,lead(change_time,1,'2999-12-31') over(partition by user_id order by change_time asc) end_time
                          from (select user_id
                                       ,change_time1 change_time
                                  from (select user_id
                                               ,concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time
                                          from (select user_id
                                                       ,user_create_time_date
                                                       ,bind_mobile_succ_time_date
                                                       ,first_credit_apply_time_date
                                                       ,first_credit_apply_succ_time_date
                                                       ,first_loan_apply_time_date
                                                       ,first_loan_succ_time_date
                                                  from tag_1_6
                                                ) a 
                                       )aa lateral view outer explode(split(change_time,',')) --这个其实是把一行数据拆分成多行，先把一行数据拼接，然后再根据逗号拆分成多行，就变成了一行数据变成多行
                                       change_time as change_time1
                                ) time_queue1
                      group by user_id
                              ,change_time
                       ) time_queue      
         union all
            select user_id
                   ,min(apply_time) as start_time_date
                   ,lead(apply_time,1,'2999-12-31')over(partition by user_id order by apply_time) as end_time_date
                   ,tag
              from (
                    select user_id
                           ,apply_time
                           ,row_number()over(partition by user_id order by apply_time) as rn_1
                           ,row_number()over(partition by user_id,tag order by apply_time) as rn_2
                      from (select user_id
                                   ,apply_time
                                   ,7  as tag
                              from tag_7
                         union all
                            select user_id
                                   ,case when datediff(lag(apply_time,1)over(partition by user_id order by apply_time),apply_time)>30
                                         then date_add(lag(apply_time,1)over(partition by user_id order by apply_time),30)
                                          end as apply_time --如果两次申请时间间隔超过30天则增加一个时间节点 作为tag=8的开始节点
                                   ,8  as tag
                              from tag_7
                             where case when datediff(lag(apply_time,1)over(partition by user_id order by apply_time),apply_time)>30
                                         then date_add(lag(apply_time,1)over(partition by user_id order by apply_time),30)
                                          end is not null
                            ) a
                    )tag7_8
           group by user_id
                    ,rn_1-rn_2
                    ,tag
           )a

user_id	apply_time	tag	rn_1	rn_2	rn_1-rn_2
User1	2024-01-01	7	1	1	0
User1	2024-01-20	7	2	2	0
User1	2024-02-21	8	3	1	2
User1	2024-02-22	7	4	3	1
User1	2024-03-01	7	5	4	1
User1	2024-04-02	8	6	2	4
User1	2024-04-11	7	7	5	2
User1	2024-04-20	7	8	6	2

user_id	Start_time	End_time	tag
User1	2024-01-01	2024-02-20	7
User1	2024-02-21	2024-02-21	8
User1	2024-02-22	2024-04-01	7
User1	2024-04-02	2024-04-10	8
User1	2024-04-11	2999-12-30	7

上面的这个sql，我来好好的解析一下，首先解析一下

with tag_1_6 as (
    select user.user_id
           ,user_create_time_date --用户创建时间
           ,bind_mobile_succ_time_date --用户手机号创建时间
           ,first_credit_apply_time_date --首次授信申请时间
           ,first_credit_apply_succ_time_date --首次授信成功时间
           ,first_loan_apply_time_date --首次借款申请时间
           ,first_loan_succ_time_date --首次借款成功时间
      from (select user_id
                   ,to_date(user_create_time) as user_create_time_date
              from loan.dws_user_base_info_d
             where dt='${dt}'
           )user
 left join (select user_id
                   ,to_date(bind_mobile_succ_time) as bind_mobile_succ_time_date
                   ,to_date(first_credit_apply_time) as first_credit_apply_time_date
                   ,to_date(first_credit_apply_succ_time) as first_credit_apply_succ_time_date
                   ,to_date(first_loan_apply_time) as first_loan_apply_time_date
                   ,to_date(first_loan_succ_time) as first_loan_succ_time_date
              from loan.dws_loan_user_base_info_d
             where dt='${dt}'
            )loan
          on user.user_id= loan.user_id
)

这里根据我们需要的

这样的标签，除了7和8，前面的主要是通过一个借款的流程来判断的，所以这里我们直接可以根据时间来判断，这个用户在什么时间内到什么时间内是一个什么状态，所以我们先准备好这个各个流程的时间

然后下面的

with tag_7 as(
    select user_id
           ,apply_time
           ,7 as tag
      from (select user_id
                   ,min(settle_time) as min_settle_time --最小结清时间
              from loan.dwd_repay_plan_d
             where dt='${dt}'
          group by user_iddsa
           )settle_user
 left join (select user_id
                   ,apply_time --申请时间
              from loan.dwd_loan_info_core_d
             where dt='${dt}'
           )loan_info
        on settle_user.user_id=loan_info.user_idd
     where case when min_settle_time<apply_time --申请时间大于最小结清时间，说明是老客，之前申请过
                then 7
                 end =7
)

这里是我们计算7和8需要准备的表，这里的处理就是筛选出我们的老客这个定义，我们的当前的申请时间大于我们的最小结清时间，说明我们之前是结清过一笔还款的，所以这就是老客

下面就是重点了：

--tag=1
            select user_id
                   ,user_create_time_date as start_time_date
                   ,user_create_time_date as end_time_date
                   ,1 as tag
              from tag_1_6

这里是新增的uv，uv也就是访客，直接当这个用户刚创建的时候这个时间他就是我们的新增uv

再下面

select time_queue.user_id
                   ,time_queue.start_time
                   ,time_queue.end_time
                   
                   --这里需要倒排，因为满足6的话一定满足5，因为是有顺序的，但是我们显示的是6，所以需要这样倒排

                   ,case when first_loan_apply_time_date>=time_queue.start_time and 
                   first_loan_apply_time_date <end_time    --借款未通过
                         then 6
                         when first_credit_apply_succ_time_date>=time_queue.start_time and first_credit_apply_succ_time_date <end_time    --授信未借款
                         then 5
                         when first_credit_apply_time_date>=time_queue.start_time and first_credit_apply_time_date <end_time    --授信未通过
                         then 4
                         when bind_mobile_succ_time_date>=time_queue.start_time and bind_mobile_succ_time_date <end_time    --注册未授信
                         then 3
                         when date_add(user_create_time_date,1)>=time_queue.start_time and date_add(user_create_time_date,1)< end_time   --未注册 这里是用户在一段时间内没有完成注册就是未注册的状态，这里选择了第二天，如果一个账号在第二天还没有注册，这个账号就会标记为未注册
                         then 2
                         end tag
              from (select user_id
                               ,change_time start_time
                               ,lead(change_time,1,'2999-12-31') over(partition by user_id order by change_time asc) end_time
                          from (select user_id
                                       ,change_time1 change_time
                                  from (select user_id
                                               ,concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time
                                          from (select user_id
                                                       ,user_create_time_date
                                                       ,bind_mobile_succ_time_date
                                                       ,first_credit_apply_time_date
                                                       ,first_credit_apply_succ_time_date
                                                       ,first_loan_apply_time_date
                                                       ,first_loan_succ_time_date
                                                  from tag_1_6
                                                ) a 
                                       )aa lateral view outer explode(split(change_time,',')) --这个其实是把一行数据拆分成多行，先把一行数据拼接，然后再根据逗号拆分成多行，就变成了一行数据变成多行
                                       change_time as change_time1
                                ) time_queue1
                      group by user_id
                              ,change_time
                       ) time_queue

这里有点多看着有些困难，我们拆开看

首先

select user_id
      ,change_time1 change_time
from (select user_id
              ,concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time
        from (select user_id
                      ,user_create_time_date
                      ,bind_mobile_succ_time_date
                      ,first_credit_apply_time_date
                      ,first_credit_apply_succ_time_date
                      ,first_loan_apply_time_date
                      ,first_loan_succ_time_date
                from tag_1_6
              ) a 
      )aa lateral view outer explode(split(change_time,',')) --这个其实是把一行数据拆分成多行，先把一行数据拼接，然后再根据逗号拆分成多行，就变成了一行数据变成多行
      change_time as change_time1

这里的内容是首先我们获取到前面我们准备好的各个时间，然后

先通过concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time先将所有的这些行先拼接到一起

假如我们拼接后有下面这样的数据

user_id	change_time
1	2024-11-27 00:00:00,2024-11-27 05:30:30,2024-11-27 13:10:00
2	2024-11-27 01:20:00,2024-11-27 02:00:00

那么我们再使用lateral view outer explode(split(change_time,','))这个函数，其实这也是行转列的一种方式，将一行数据拆分成多行就会变成

user_id	change_time
1	2024-11-27 00:00:00
1	2024-11-27 05:30:30
1	2024-11-27 13:10:00
2	2024-11-27 01:20:00
2	2024-11-27 02:00:00

那么下面我们继续看

select user_id
      ,change_time start_time
      ,lead(change_time,1,'2999-12-31') over(partition by user_id order by change_time asc) end_time
-- from (select user_id
--               ,change_time1 change_time
--         from (select user_id
--                       ,concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time
--                 from (select user_id
--                               ,user_create_time_date
--                               ,bind_mobile_succ_time_date
--                               ,first_credit_apply_time_date
--                               ,first_credit_apply_succ_time_date
--                               ,first_loan_apply_time_date
--                               ,first_loan_succ_time_date
--                         from tag_1_6
--                       ) a 
--               )aa lateral view outer explode(split(change_time,',')) --这个其实是把一行数据拆分成多行，先把一行数据拼接，然后再根据逗号拆分成多行，就变成了一行数据变成多行
--               change_time as change_time1
--       ) time_queue1
group by user_id
         ,change_time

我们先把其他的sql注释掉，首先我们group by user_id,change_time，这里因为我们要获取的是时间段，现在我们是一个个的时间，所以我们要把这些数据连起来变成时间段

1	lead(change_time,1,'2999-12-31') over(partition by user_id order by change_time asc) end_time

这个先开窗，按照user_id，然后按照时间点进行排序，然后将我们每个用户的一个时间点的下一个这个用户数据的时间点作为结束时间，如果没有下一条数据了就记为2999-12-31

user_id	start_time	end_time
1	2024-11-27 00:00:00	2024-11-27 05:30:30
1	2024-11-27 05:30:30	2024-11-27 13:10:00
1	2024-11-27 13:10:00	2999-12-31
2	2024-11-27 01:20:00	2024-11-27 02:00:00
2	2024-11-27 02:00:00	2999-12-31

这样我们就获取到了这个时间段了

我们继续往下看这个判断

select time_queue.user_id
        ,time_queue.start_time
        ,time_queue.end_time
        
        --这里需要倒排，因为满足6的话一定满足5，因为是有顺序的，但是我们显示的是6，所以需要这样倒排

        ,case when first_loan_apply_time_date>=time_queue.start_time and 
        first_loan_apply_time_date <end_time    --借款未通过
              then 6
              when first_credit_apply_succ_time_date>=time_queue.start_time and first_credit_apply_succ_time_date <end_time    --授信未借款
              then 5
              when first_credit_apply_time_date>=time_queue.start_time and first_credit_apply_time_date <end_time    --授信未通过
              then 4
              when bind_mobile_succ_time_date>=time_queue.start_time and bind_mobile_succ_time_date <end_time    --注册未授信
              then 3
              when date_add(user_create_time_date,1)>=time_queue.start_time and date_add(user_create_time_date,1)< end_time   --未注册 这里是用户在一段时间内没有完成注册就是未注册的状态，这里选择了第二天，如果一个账号在第二天还没有注册，这个账号就会标记为未注册
              then 2
              end tag
  -- from (
  --   select user_id
  --                   ,change_time start_time
  --                   ,lead(change_time,1,'2999-12-31') over(partition by user_id order by change_time asc) end_time
  --             from (select user_id
  --                           ,change_time1 change_time
  --                     from (select user_id
  --                                   ,concat_ws(',',user_create_time_date,bind_mobile_succ_time_date,first_credit_apply_time_date,first_credit_apply_succ_time_date,first_loan_apply_time_date,first_loan_succ_time_date) as change_time
  --                             from (select user_id
  --                                           ,user_create_time_date
  --                                           ,bind_mobile_succ_time_date
  --                                           ,first_credit_apply_time_date
  --                                           ,first_credit_apply_succ_time_date
  --                                           ,first_loan_apply_time_date
  --                                           ,first_loan_succ_time_date
  --                                     from tag_1_6
  --                                   ) a 
  --                           )aa lateral view outer explode(split(change_time,',')) --这个其实是把一行数据拆分成多行，先把一行数据拼接，然后再根据逗号拆分成多行，就变成了一行数据变成多行
  --                           change_time as change_time1
  --                   ) time_queue1
  --         group by user_id
  --                 ,change_time
  --           ) 
            time_queue

我们这里需要倒排，因为满足6的话一定是满足5的，也就是如果这个客户，申请借款未通过（6），那么他一定是得先授信通过未申请借款（5）才行，所以我们先判断大的

如果我们的开始时间小于首次借款申请时间，结束时间大于首次借款说明就是借款没有通过，也就是6

如果开始时间小于首次授信成功时间，结束时间大于首次授信成功时间也就是说客户授信成功了，但是没有后续借款也就是5

以此类推

下面我们再看一下标签是7和8的判断活跃老客和沉默老客

--tag=7-8
  select user_id
          ,min(apply_time) as start_time_date
          ,lead(apply_time,1,'2999-12-31')over(partition by user_id order by apply_time) as end_time_date
          ,tag
    from (
          select user_id
                  ,apply_time
                  ,row_number()over(partition by user_id order by apply_time) as rn_1
                  ,row_number()over(partition by user_id,tag order by apply_time) as rn_2
            from (select user_id
                          ,apply_time
                          ,7  as tag
                    from tag_7
                union all
                  select user_id
                          ,case when datediff(lag(apply_time,1)over(partition by user_id order by apply_time),apply_time)>30 --lag(apply_time,1)这个是申请时间的前一条数据的时间，datediff是计算两个时间的相差时间
                                then date_add(lag(apply_time,1)over(partition by user_id order by apply_time),30)
                                end as apply_time --如果两次申请时间间隔超过30天则增加一个时间节点 作为tag=8的开始节点 这个时间节点就是申请时间+30天
                          ,8  as tag
                    from tag_7
                    where case when datediff(lag(apply_time,1)over(partition by user_id order by apply_time),apply_time)>30
                                then date_add(lag(apply_time,1)over(partition by user_id order by apply_time),30)
                                end is not null
                  ) a
          )tag7_8
  group by user_id
          ,rn_1-rn_2
          ,tag

这段代码的核心是：

区分用户的活跃状态（tag=7）和沉默状态（tag=8）。

如果两次申请时间（apply_time）间隔超过30天，则在上一条申请时间加30天生成一个新的时间节点，表示沉默期的开始。

使用 row_number 和分组逻辑（rn_1 - rn_2）将连续的时间段分组。

生成每个时间段的开始时间（start_time_date）和结束时间（end_time_date）。

假设有以下 tag_7 数据，表示用户的申请时间和默认的 tag=7（活跃老客）：

user_id	apply_time
1	2024-01-01
1	2024-01-20
1	2024-03-01
1	2024-05-01
2	2024-02-15
2	2024-04-01

1：判断两次申请时间间隔，生成 tag=8 的时间节点

select user_id,
       case when datediff(lag(apply_time, 1) over (partition by user_id order by apply_time), apply_time) > 30
            then date_add(lag(apply_time, 1) over (partition by user_id order by apply_time), 30)
       end as apply_time,
       8 as tag
from tag_7

使用窗口函数 lag 获取前一条申请时间,比较前一条申请时间和当前申请时间的间隔天数。

如果间隔超过 30 天，则生成一个新的时间节点，时间为前一条申请时间加 30 天，标签为 tag=8。

处理后的结果：

user_id	apply_time	tag
1	2024-01-01	7
1	2024-01-20	7
1	2024-03-01	7
1	2024-03-31	8
1	2024-05-01	7
2	2024-02-15	7
2	2024-04-01	7

计算 rn_1 和 rn_2

select user_id,
       apply_time,
       row_number() over (partition by user_id order by apply_time) as rn_1,
       row_number() over (partition by user_id, tag order by apply_time) as rn_2
from ...

rn_1：

对每个用户按申请时间（apply_time）排序，分配全局行号。
rn_2：

对每个用户按标签（tag）和申请时间（apply_time）排序，分配标签内的行号。
计算后的结果：

user_id	apply_time	tag	rn_1	rn_2	rn_1 - rn_2
1	2024-01-01	7	1	1	0
1	2024-01-20	7	2	2	0
1	2024-03-01	7	3	3	0
1	2024-03-31	8	4	1	3
1	2024-05-01	7	5	4	1
2	2024-02-15	7	1	1	0
2	2024-04-01	7	2	2	0

rn_1 - rn_2：

用于分组，值相同的记录属于同一组。

select user_id,
       min(apply_time) as start_time_date,
       lead(apply_time, 1, '2999-12-31') over (partition by user_id order by apply_time) as end_time_date,
       tag
from ...
group by user_id, rn_1 - rn_2, tag

对每组记录（rn_1 - rn_2 和 tag）：

min(apply_time)：获取组内最小的申请时间作为时间段的开始时间。

lead(apply_time, 1)：获取下一条记录的申请时间作为时间段的结束时间。

如果没有下一条记录，默认结束时间为 ‘2999-12-31’。

最后的结果是

user_id	start_time_date	end_time_date	tag
1	2024-01-01	2024-01-20	7
1	2024-01-20	2024-03-01	7
1	2024-03-01	2024-03-31	7
1	2024-03-31	2024-05-01	8
1	2024-05-01	2999-12-31	7
2	2024-02-15	2024-04-01	7
2	2024-04-01	2999-12-31	7

在所有的最后，我们再把这些内容放到外面的拉链表中

insert overwrite table loan.dws_user_market_level_d partition(dt='${dt}')
select user_id
       ,start_time_date
       ,end_time_date
       ,tag
       ,case when tag = 1 then '新增uv'
             when tag = 2 then '未注册'
             when tag = 3 then '注册未申请授信'
             when tag = 4 then '申请授信未通过'
             when tag = 5 then '授信通过未申请借款'
             when tag = 6 then '申请借款未通过'
             when tag = 7 then '活跃老客'
             when tag = 8 then '沉默老客'
         end tag_detail

2024-12-18 10:25:04 # 项目基础 # 用户画像 # APP层用户状态详细讲解 #用户画像