新零售数仓项目：用户行为深度分析-CSDN博客

本文链接：https://blog.csdn.net/qq_40333693/article/details/109053400

本文介绍了一个新零售数仓项目的实战过程，重点分析用户行为数据，包括：生成用户基本宽表，记录PC端最近及首次访问的时间、使用的session和cookie、PV量、浏览器及操作系统信息；同时，关注PC端连续多天访问次数，APP各时间段访问PV数，以及30天内的IP、cookie_id、浏览器和操作系统的使用频率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

–生成用户基本宽表

create table user_basic as
select u.user_id,u.user_name,u.user_gender,
u.user_birthday,u.user_age,u.constellation,
u.province,u.city,u.city_level,u.e_mail,op_mail,
u.mobile,u.num_seg_mobile,u.op_Mobile,register_time,
u.login_ip,login_source,request_user,total_score,
used_score,is_blacklist,is_married,education,
monthly_income,profession,is_pregnant_woman,
is_have_children,is_have_car,phone_brand,phone_brand_level,
phone_cnt,change_phone_cnt,is_maja,majia_account_cnt,
loyal_model,shopping_type_model,e.weight,e.height
from user u join user_extend e on u.user_id=e.user_id;

–pc端最近一次访问的时间，使用的session，cookie，pc端的pv量，使用的浏览器和操作系统

create table snbap_dw.user_last_pc_click as
(select user_id,session_id,cookie_id,visit_os,browser_name,visit_time,
rank() over(partition by user_id order by visit_time desc) rn,
count(page_id) over(partition by user_id,visit_time order by visit_time) cnt
from snbap_ods.user_pc_click_log) a where rn = 1;

–pc端第一次访问的时间，使用的session，cookie，pc端的pv量，使用的浏览器和操作系统

create table snbap_dw.user_first_pc_click as
(select user_id,session_id,cookie_id,visit_os,browser_name,visit_time,
rank() over(partition by user_id order by visit_time) rn,
count(page_id) over(partition by user_id,visit_time order by visit_time desc) cnt
from snbap_ods.user_pc_click_log) a where rn = 1;

CREATE TABLE user_pc_click_partition(
 `log_id` bigint, 
  `user_id` bigint, 
  `session_id` string, 
  `cookie_id` string, 
  `visit_time` string, 
  `visit_url` string, 
  `visit_os` string, 
  `browser_name` string, 
  `visit_ip` string, 
  `province` string, 
  `city` string, 
  `pv` int, 
  `goods_id` bigint, 
  `shop_id` bigint
)
PARTITIONed by (visit_date string);

  insert into snbap_dw.user_pc_click_partition partition (visit_date)
  select
  `log_id`, 
  `user_id`, 
  `session_id`, 
  `cookie_id`, 
  `visit_time