用户画像基础（四）
2024-12-18 10:25:12 # 项目基础 # 用户画像 # DWS的用户/借贷/风控/流量/三方采买 #用户画像

数据模型

image-20240811100823210

借贷

用户主题

建表语句：

这里后面的_d表示的是这是一张全量的表，比如这张表在dt的分区下每个分区都有历史的全量的数据，假如里面有十个分区，第一个分区是2024年11月24号，第二个分区是2024年11月25号，第三个分区是2024年11月26号，那么第一个分区内的数据就是2024年11月24号到2024年11月24号的数据，第二个分区内就是2024年11月24号到2024年11月25号的数据，第三个分区内就是2024年11月14号到2024年11月26号的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
CREATE EXTERNAL TABLE loan.dws_user_base_info_d (
  user_id bigint COMMENT '用户ID', 
  user_sex string COMMENT '性别',
  user_name string COMMENT '姓名',
  user_birthday string COMMENT '用户生日',
  user_age int COMMENT '用户年龄',
  mobile bigint COMMENT '用户手机号',
  del_flag int COMMENT '用户是否注销',
  first_channel string COMMENT 'first_channel', --我们这里的一个第一次注册的登录界面这些信息是来源于下面的用户登陆信息表
  second_channel string COMMENT 'second_channel',
  user_city string COMMENT '城市',
  id_card string COMMENT '身份证',
  bank_id string COMMENT '银行卡号'
  -- user_create_time string COMMENT '用户创建时间',
  -- bind_mobile_succ_time 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_base_info_d';

CREATE EXTERNAL TABLE loan.dwd_user_base_info_d ( --这个表是用户自己填写的信息
  user_id bigint COMMENT '用户ID', 
  user_sex string COMMENT '性别',
  user_birthday string COMMENT '用户生日',
  user_age int COMMENT '用户年龄',
  mobile bigint COMMENT '用户手机号',
  del_flag int COMMENT '用户是否注销',
  user_city string COMMENT '城市',
  id_card string COMMENT '注册-身份证',
  user_name 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_base_info_d'; 

CREATE EXTERNAL TABLE loan.dwd_user_bank_info_d (--银行卡信息
  bank_id bigint COMMENT '银行卡ID', 
  mobile bigint COMMENT '用户手机号--银行卡预留手机号',
  id_card string COMMENT '身份证--银行卡预留手机号',
 )
COMMENT '用户银行卡信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_user_bank_info_d';

CREATE EXTERNAL TABLE loan.dwd_user_idcard_info_d ( --这个表是用户实名的信息
  user_id bigint COMMENT '用户ID', 
  user_sex string COMMENT '性别',
  user_birthday string COMMENT '用户生日',
  user_age int COMMENT '用户年龄',
  user_city string COMMENT '城市',
  id_card string COMMENT '身份证',
  user_name string COMMENT '姓名',
 )
COMMENT '用户身份证信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_user_idcard_info_d';

CREATE EXTERNAL TABLE loan.dwd_user_login_info_d (
  user_id bigint COMMENT '用户ID', 
  del_flag int COMMENT '用户是否注销',
  brand_name string COMMENT '用户访问渠道', --这里就是对应了前面的用户登录的渠道
  user_city string COMMENT '城市',
  create_time string COMMENT '创建时间',
  update_time string COMMENT '更新时间'
 )
COMMENT '用户登陆信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_user_login_info_d';

etl代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
  insert overwrite table loan.dws_user_base_info_d partition(dt='${dt}')
  select base_info.user_id 
         ,base_info.user_sex
         ,base_info.user_name
         ,base_info.user_birthday
         ,base_info.user_age 
         ,nvl(base_info.mobile,bank_info.mobile) as mobile
         ,base_info.del_flag 
         ,login_info.first_channel 
         ,login_info.second_channel 
         ,base_info.user_city 
         ,base_info.id_card 
         ,bank_info.bank_id
         -- ,create_time as user_create_time
         -- ,case when nvl(user_base.mobile,bank_info.mobile) is not null
         --       then create_time
         --       end as bind_mobile_succ_time
    from (select nvl(user_base.user_id,idcard_info.user_id) as user_id
                 ,nvl(user_base.user_sex,idcard_info.user_sex) as user_sex
                 ,nvl(user_base.user_birthday,idcard_info.user_birthday) as user_birthday
                 ,nvl(user_base.user_age,idcard_info.user_age) as user_age
                 ,user_base.mobile as mobile
                 ,user_base.del_flag as del_flag
                 ,nvl(user_base.user_city,idcard_info.user_city) as user_city
                 ,nvl(user_base.id_card,idcard_info.id_card) as id_card
                 ,nvl(user_base.user_name,idcard_info.user_name) as user_name
            from (select user_id
                         ,user_sex
                         ,user_birthday
                         ,user_age
                         ,mobile
                         ,del_flag
                         ,user_city
                         ,id_card
                         ,user_name
                    from loan.dwd_user_base_info_d
                   where dt='${dt}'
                 )user_base
        full join (select user_id
                          ,user_sex
                          ,user_birthday
                          ,user_age
                          ,user_city
                          ,id_card
                          ,user_name
                     from loan.dwd_user_idcard_info_d
                    where dt='${dt}'
                  )idcard_info
                on user_base.user_id=idcard_info.user_id
         )base_info
left join(select bank_id
                 ,mobile
                 ,id_card
            from loan.dwd_user_bank_info_d
           where dt='${dt}'
         )bank_info
       on base_info.id_card=bank_info.id_card
left join(select user_id
                 ,case when rn=1 
                       then brand_name
                       else null
                       end as first_channel
                 ,case when rn=2 
                       then brand_name
                       else null
                       end as second_channel
                  ,create_time
                  ,update_time
            from (select user_id
                         ,brand_name
                         ,min(create_time) as create_time
                         ,max(update_time) as update_time
                         ,row_number()over(partition by user_id order by create_time ) rn
                    from loan.dwd_user_login_info_d
                   where dt='${dt}'
                group by user_id
                         ,brand_name
                  )login_info
              where login_info.rn in (1,2)
         )login_info
       on base_info.user_id=login_info.user_id

借贷主题

这里是筛选漏洞模型最好的方式

这里的下面这两个字段是一个很好的使用漏斗模型分析的字段

1
2
first_loan_apply_succ_time string COMMENT '首次借款成功申请时间',
first_loan_succ_time string COMMENT '首次放款成功放款时间',

建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
CREATE EXTERNAL TABLE loan.dws_loan_user_base_info_d ( 
  user_id bigint COMMENT '用户ID',
  first_channel string COMMENT '一级渠道',
  second_channel string COMMENT '二级渠道',
  first_loan_apply_time string COMMENT '首次借款申请时间',
  first_loan_apply_succ_time string COMMENT '首次借款成功申请时间',
  first_loan_succ_time string COMMENT '首次放款成功放款时间',
  recent_loan_apply_time string COMMENT '最近一次借款申请提交时间',
  recent_loan_audit_time string COMMENT '最近一次借款申请审核时间',
  recent_loan_audit_status string COMMENT '最近一次借款审核状态（含自动审核拒绝永久、拒绝30天) 审核中-自动审核，审核中-人工审核，自动审核通过，审核通过，拒绝放款-可立即重申 永不放款,拒绝放款-可N天重申',
  total_loan_cnt int COMMENT '申请借款总单数',
  total_succ_loan_cnt int COMMENT '放款成功总单数',
  use_coupon_apply_amt string COMMENT '使用优惠券总申请金额',
  use_coupon_apply_cnt int COMMENT '使用优惠券总单数',
  recent_act_day string COMMENT '最近一次还款时间',
  recent_settle_time 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_base_loan_info_d';

CREATE EXTERNAL TABLE loan.dwd_credit_apply_info_d (
  credit_id bigint COMMENT '贷前订单ID',
  user_id bigint COMMENT '用户ID',
  user_name string COMMENT '姓名',
  apply_status string COMMENT '贷前授信状态',
  credit_amount string COMMENT '贷前授信金额',
  apply_time string COMMENT '订单申请时间',
  audit_time string COMMENT '订单通过时间',
  credit_risk_info string COMMENT '贷前风控返回结果',
 )
COMMENT '贷前订单表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_credit_apply_info_d';

CREATE EXTERNAL TABLE loan.dwd_loan_info_core_d ( 订单金额：10000 已还：2000 剩余：8000
  loan_id bigint COMMENT '借款ID',
  credit_id bigint COMMENT '申请借款ID',
  user_id bigint COMMENT '用户ID',
  interest_rate string COMMENT '借款利率',
  due_amount string COMMENT '应还本金', --8000
  act_amount string COMMENT '实还本金', --2000
  repay_type string COMMENT '还款方式',
  loan_amount string COMMENT '借款金额', --10000
  create_time string COMMENT '借款时间'
 )
COMMENT '贷中信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_loan_info_core_d';

CREATE EXTERNAL TABLE loan.dwd_repay_plan_d ( 
  repay_plan_id bigint COMMENT '还款计划ID',
  loan_id bigint COMMENT '借款ID',
  user_id bigint COMMENT '用户ID',
  loan_rates string COMMENT '利率  borrow_interest 利息利率, service_interest 服务费利率, overdue_borrow_interest 逾期利息利率, overdue_service_interest 逾期服务费利率,penalty_interest 违约金利率',
  due_amount string COMMENT '应还本金',
  due_day string COMMENT '应还日期',
  act_day string COMMENT '实还日期',
  act_amount string COMMENT '实还本金',
  repay_type string COMMENT '还款方式',
  due_service_interest string COMMENT '应还服务费',
  act_service_interest string COMMENT '实还服务费',
  settle_time string COMMENT '结清时间'
 )
COMMENT '贷后信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_repay_plan_d';

loan_id repay_plan_id
001.    001_01
001.    001_02
...
001.    001_10


CREATE EXTERNAL TABLE loan.dwd_use_coupon_d (
  user_id bigint COMMENT '用户ID',
  coupon_id bigint COMMENT '优惠券ID',
  coupon_apply_amt string COMMENT '使用优惠券总申请金额',
  flag int COMMENT '是否使用',
 )
COMMENT '优惠券表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_use_coupon_d';




etl代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
 insert overwrite table loan.dws_loan_user_base_info_d partition(dt='${dt}')
 select user_id
        ,first_channel
        ,second_channel
        ,first_loan_apply_time
        ,first_loan_apply_succ_time
        ,first_loan_succ_time
        ,recent_loan_apply_time
        ,recent_loan_audit_time
        ,recent_loan_audit_status
        ,total_loan_cnt
        ,total_succ_loan_cnt
        ,recent_discount_level
        ,use_coupon_apply_amt
        ,use_coupon_apply_cnt
        ,recent_act_day
        ,recent_settle_time
   from (select user_id
                ,case when rn=1 
                      then brand_name
                      else null
                      end as first_channel
                ,case when rn=2 
                      then brand_name
                      else null
                      end as second_channel
           from (select user_id
                        ,brand_name
                        ,row_number()over(partition by user_id order by create_time ) rn
                   from loan.dwd_user_login_info_d
                  where dt='${dt}'
                 )bank_info
            where bank_info.rn in (1,2)
         )user_info
left join(select user_id
                 ,min(apply_time)  as first_loan_apply_time
                 ,min(audit_time)  as first_loan_apply_succ_time
                 ,max(apply_time)  as recent_loan_apply_time
                 ,max(audit_time)  as recent_loan_audit_time
                 ,max(case when audit_time_desc_rn=1 
                       then apply_status
                        end)  as recent_loan_audit_status
                 ,count(credit_id) as total_loan_cnt
            from (select user_id
                         ,credit_id
                         ,apply_time
                         ,audit_time
                         ,apply_status
                         ,row_number()over(partition by user_id order by audit_time desc) audit_time_desc_rn
                    from loan.dwd_credit_apply_info_d
                   where dt='{dt}'
                 )credit_apply
         group by user_id
         )credit_apply
       on user_info.user_id=credit_apply.user_id
left join(select user_id
                 ,min(create_time) as first_loan_succ_time
                 ,count(loan_id) as total_succ_loan_cnt
            from (select credit_id
                         ,loan_id
                         ,user_id
                    from loan.dwd_loan_info_core_d
                   where dt='${dt}'
                  )loan_info
         group by user_id
         )loan_info
       on user_info.user_id=loan_info.user_id
left join(select user_id
                 ,max(act_day) as recent_act_day
                 ,max(settle_time) as recent_settle_time
            from loan.dwd_repay_plan_d
           where dt='${dt}'
        group by user_id
         )repay_plan
       on user_info.user_id=repay_plan.user_id 
left join(select user_id
                 ,sum(case when flag=1 then coupon_apply_amt then null end) as use_coupon_apply_amt
                 ,count(case when flag=1 then coupon_id then null end) as use_coupon_apply_cnt
            from loan.dwd_use_coupon_d
           where dt='${dt}'
        group by user_id
         )use_coupon
       on user_info.user_id=use_coupon.user_id

风控主题

建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
CREATE EXTERNAL TABLE loan.dws_decision_base_info_d (
  loan_id bigint COMMENT '订单ID',
  decision_id bigint COMMENT '决策流ID',
  decision_tree string COMMENT '决策树',
  decision_status string COMMENT '决策状态',
  model_id_string string COMMENT '对应模型ID用‘@’连接',
  factor_id string COMMENT '对应因子ID用‘@’连接',
  model_json string COMMENT '模型分json',
  factor_json string COMMENT '因子分json'
 )
COMMENT '风控信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dws_decision_base_info_d';

--决策流信息表
CREATE EXTERNAL TABLE loan.dwd_decision_base_info_d (
  loan_id bigint COMMENT '订单ID',
  decision_id bigint COMMENT '决策流ID',
  decision_tree string COMMENT '决策树',
  decision_status string COMMENT '决策状态'
 )
COMMENT '决策流信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_decision_base_info_d';


--模型结果表
CREATE EXTERNAL TABLE loan.dwd_model_base_info_d (
  model_id bigint COMMENT '模型ID',
  model_mark string COMMENT '模型分数',
  model_status string COMMENT '模型状态',
  decision_id bigint COMMENT '决策流ID'
 )
COMMENT '模型结果表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_model_base_info_d';

--风险因子信息表
CREATE EXTERNAL TABLE loan.dwd_risk_factor_base_info_d (
  factor_id bigint COMMENT '因子ID',
  factor_name string COMMENT '因子名',
  factor_mark string COMMENT '因子分数',
  model_id bigint COMMENT '模型ID',
  decision_id bigint COMMENT '决策流ID'
 )
COMMENT '风险因子信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/loan.db/dwd_risk_factor_base_info_d';

etl代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
   insert overwrite table loan.dws_decision_base_info_d partition(dt='${dt}')
   select loan_id
          ,decision_id
          ,decision_tree
          ,decision_status
          ,model_id_string
          ,factor_id
          ,model_json
          ,factor_json
     from (select decision_id
                  ,loan_id
                  ,decision_tree
                  ,decision_status
             from loan.dwd_decision_base_info_d
            where dt='${dt}' 
          )decision_base
left join (select decision_id
                  ,concat_ws("@" , collect_set(model_id)) as model_id_string
                  ,concat('{',concat_ws(',', collect_set(concat('"', model_id, '":"', model_mark, '"'))),'}') as model_json
             from loan.dwd_model_base_info_d
            where dt='${dt}' 
         group by decision_id
           )model_info
        on decision_base.decision_id=model_info.decision_id
left join (select decision_id
                  ,concat_ws("@" , collect_set(factor_id)) as factor_id_string
                  ,concat('{',concat_ws(',', collect_set(concat('"', factor_id, '":"', factor_mark, '"'))),'}') as factor_json
             from loan.dwd_risk_factor_base_info_d
            where dt='${dt}' 
         group by decision_id
         )factor_info
        on decision_base.decision_id=factor_info.decision_id

流量主题

这里的表是_i，如果是_i而不是_d的话，代表这个表不是全量数据，每个分区内就是当天数据的切片

最主要的就是分而聚合的思想，如果要计算365天就前面计算一下历史的数据内容加上今天的数据内容

建表语句：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
CREATE EXTERNAL TABLE event.dwd_h5_user_info_i (
  user_id bigint COMMENT '用户ID', 
  sessionid bigint COMMENT '事件ID', 
  h5_type string COMMENT 'h5类型', 
  h5_josn string COMMENT 'h5上报数据'
 )
COMMENT '用户h5上报信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/event.db/dwd_h5_user_info_i';

CREATE EXTERNAL TABLE event.dwd_user_event_info_i (
  user_id bigint COMMENT '用户ID', 
  sessionid bigint COMMENT '事件ID', 
  event_name string COMMENT '事件名称', 
  event_josn string COMMENT '事件上报数据'
 )
COMMENT '用户后端上报信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/event.db/dwd_user_event_info_i';

CREATE EXTERNAL TABLE event.dws_user_etk_event_info_d (
  user_id bigint COMMENT '用户ID', 
  total_pv_event_cnt int COMMENT 'pv累计访问次数',
  click_event_cnt int 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页面的访问次数'
 )
COMMENT '用户上报信息表'
PARTITIONED BY (
  `dt` string)
row format delimited fields terminated by  ','
STORED AS TEXTFILE
location 'file:/root/wangqi/spark-warehouse/event.db/dws_user_etk_event_info_d';

这里h5里面可能包含多种类型，pv只是其中之一，但是后端上报信息表中的event中只有一个event类型，所有h5里面需要判断一下h5_type，但是event中不需要判断

etl代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
  insert overwrite table event.dws_user_etk_event_info_d partition(dt='${dt}')
  select coalesce(etk_event.user_id,h5.user_id,event.user_id) as user_id
         ,(etk_event.total_pv_event_cnt+h5.pv_event_cnt) as total_pv_event_cnt
         ,(etk_event.click_event_cnt+h5.h5_cnt+event.event_cnt) as click_event_cnt
         ,(etk_event.7d_pv_cnt-h5_cnt_7d+pv_event_cnt) as 7d_pv_cnt
         ,(etk_event.7d_pv_event_cnt-event_cnt_7d+event_cnt) as 7d_pv_event_cnt
         ,(etk_event.365d_pv_cnt-h5_cnt_365d+pv_event_cnt) as 365d_pv_cnt
         ,(etk_event.365d_event_cnt-event_cnt_365d+event_cnt) as 365d_event_cnt
    from (select user_id
                 ,total_pv_event_cnt
                 ,click_event_cnt
                 ,7d_pv_cnt
                 ,7d_pv_event_cnt
                 ,365d_pv_cnt
                 ,365d_event_cnt
            from event.dws_user_etk_event_info_d
           where dt=date_sub('${dt}',1) --2024-08-09（2024-08-09～2024-08-02）+ 2024-08-10-2024-08-02 
                                        --2024-08-10 （2024-08-10～2024-08-03）
          )etk_event
full join (select user_id
                  ,count(case when h5_type='pv' and dt='${dt}' then sessionid else null end) as pv_event_cnt
                  ,count(case when  dt='${dt}' then sessionid else null end ) as h5_cnt
                  ,count(case when h5_type='pv' and dt=date_sub('${dt}',6) then sessionid else null end) as h5_cnt_7d
                  ,count(case when h5_type='pv' and dt=date_sub('${dt}',364) then sessionid else null end) as h5_cnt_365d
             from event.dwd_user_h5_info_i
            where dt in ('{dt}',date_sub('${dt}',6),date_sub('${dt}',364))
         group by user_id
           )h5
        on etk_event.user_id=h5.user_id
full join (select user_id
                  ,count(case when  dt='${dt}' then sessionid else null end ) as event_cnt
                  ,count(case dt=date_sub('${dt}',6) then sessionid else null end) as event_cnt_7d
                  ,count(case dt=date_sub('${dt}',364) then sessionid else null end) as event_cnt_365d
             from event.dwd_user_event_info_i
            where dt in ('{dt}',date_sub('${dt}',6),date_sub('${dt}',364))
         group by user_id
           )event
        on etk_event.user_id=event.user_id

三方购买数据落表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
msck repair table  --刷新整张表的元数据，三方采买就是先将数据放到hdfs，然后刷新表的元数据

CREATE EXTERNAL TABLE `ods.ods_third_{third_name}_i`(
  `additionparams` string COMMENT 'from deserializer', 
  `attempt_number` string COMMENT 'from deserializer', 
  `callback_status` string COMMENT 'from deserializer', 
  `channel` string COMMENT 'from deserializer',
  `cost` string COMMENT 'from deserializer',
  `mongouniqueid` string COMMENT 'from deserializer', 
  `order_id` string COMMENT 'from deserializer',
  `order_type` string COMMENT 'from deserializer',
  `query_interface` string COMMENT 'from deserializer',
  `query_result` struct  \<result_msg:string,req_msg_id:string,result_code:string,query_result:struct<umkt_out_put_info:string,base_info:struct<umkt_result:string,customer_key:string,query_template:string>COMMENT 'from deserializer',
  `query_source` string COMMENT 'from deserializer', 
  `stage` string COMMENT 'from deserializer', 
  `status` string COMMENT 'from deserializer',
  `user_id` string COMMENT 'from deserializer',
  `createtimestr` string COMMENT 'from deserializer', 
  `create_time` string COMMENT 'from deserializer', 
  `dstype` string COMMENT 'from deserializer',
  `dstimestamp` string COMMENT 'from deserializer')
COMMENT '原始三方'
PARTITIONED BY ( 
  `dt` string)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'mapping.channel'='channel|platformChannel', 
  'mapping.create_time'='create_time|createTime', 
  'mapping.createtimestr'='createTimeStr|createtimeStr', 
  'mapping.ds'='channel|platformChannel', 
  'mapping.ds_id'='1', 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://wejoyservice/user/hive/warehouse/ods.db/ods.ods_third_i/third_name=xxx1'


上一页
2024-12-18 10:25:12 # 项目基础 # 用户画像 # DWS的用户/借贷/风控/流量/三方采买 #用户画像
下一页