数仓可视化3--dws层宽表

一、用户主题

1.1、用户启动表：

昨天是统计的一个用户启动一次，就记录一次，将启动时间变为了时间段

create external table if not exists dws_nshop.dws_nshop_ulog_launch(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  launch_count int comment '启动次数'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_ulog_launch/'

insert into table dws_nshop.dws_nshop_ulog_launch partition(bdp_day='20231227')
select 
distinct 
user_id,
device_num ,
device_type ,
os  ,
os_version,
manufacturer,
carrier,
network_type,
area_code,
count(1) over(partition by user_id) as launch_count 
from 
dwd_nshop.dwd_nshop_actlog_launch 
where bdp_day='20231227';

 1.2、用户浏览表

create external table if not exists dws_nshop.dws_nshop_ulog_view(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  view_count int comment '浏览次数'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_ulog_view/';

insert overwrite table dws_nshop.dws_nshop_ulog_view partition(bdp_day='20231227')
select 
distinct user_id ,
device_num ,
device_type ,
os ,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
count(user_id) over(partition by user_id) view_count
from dwd_nshop.dwd_nshop_actlog_pdtview
where bdp_day="20231227";

1.3、用户查询

create external table if not exists dws_nshop.dws_nshop_ulog_search(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  search_count int comment '搜索次数'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_ulog_search/';

insert overwrite table dws_nshop.dws_nshop_ulog_search partition(bdp_day='20231227')
select 
distinct user_id ,
device_num ,
device_type ,
os ,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
count(user_id) over(partition by user_id) search_count
from dwd_nshop.dwd_nshop_actlog_pdtsearch
where bdp_day="20231227";

1.4、用户关注店铺汇总表

create external table if not exists dws_nshop.dws_nshop_ulog_comment(
  user_id string comment '用户id',
  device_num string comment '设备号',
  device_type string comment '设备类型',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  comment_count int comment '用户关注次数店铺的次数',-- 不去重
  comment_target_count int comment '店铺目前有多少人关注',--去重
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_ulog_comment/';

insert overwrite table dws_nshop.dws_nshop_ulog_comment partition(bdp_day='20231227')
select 
distinct
user_id ,
device_num ,
device_type ,
os ,
os_version ,
manufacturer,
carrier ,
network_type,
area_code ,
-- 一个用户关注了这个店铺多少次
count(1) over(partition by user_id,target_id) ,
-- 一个店铺被多少人关注
count(distinct user_id) over(partition by target_id) ,
current_timestamp()
from 
dwd_nshop.dwd_actlog_product_comment
where bdp_day='20231227';

 二、用户交易信息宽表

create external table if not exists dws_nshop.dws_nshop_user_orders(
  user_id string comment '用户id',
  customer_natives string comment '所在区域',
  orders_count int comment '订单数量',
  orders_pay DECIMAL(10,1) comment '订单金额',
  orders_shipping DECIMAL(10,1) comment '订单运费金额',
  orders_district DECIMAL(10,1) comment '订单优惠金额',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_user_orders/';

insert into table dws_nshop.dws_nshop_user_orders partition (bdp_day='20231227')
select 
distinct 
o.customer_id,
c.customer_natives,
count(1) over(partition by o.customer_id) orders_count ,
sum(o.payment_money)  over(partition by o.customer_id) orders_pay,
sum(o.shipping_money) over(partition by o.customer_id) orders_shipping,
sum(o.district_money) over(partition by o.customer_id) orders_district,
current_timestamp()
from 
dwd_nshop.dwd_nshop_orders_details  o
join 
ods_nshop.ods_02_customer c
on c.customer_id = o.customer_id
where o.bdp_day='20231227';

 三、用户投诉订单表

create external table if not exists dws_nshop.dws_nshop_user_complainant(
  user_id string comment '用户id',
  area_code string comment '地区编码',
  compl_orders_count int comment '订单数量',
  compl_orders_pay DECIMAL(10,1) comment '订单金额',
  compl_supplier_count int comment '商家数量',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_user_complainant/';

insert into table dws_nshop.dws_nshop_user_complainant partition (bdp_day='20231227')
select 
o.customer_id,
c.customer_natives area_code,
count(1) orders_count,
sum(o.payment_money)  orders_pay,
count(distinct supplier_code) compl_supplier_count,
current_timestamp()
from 
dwd_nshop.dwd_nshop_orders_details  o
join 
ods_nshop.ods_02_customer c
on c.customer_id = o.customer_id
where o.bdp_day='20231227' and o.order_status=7
group by o.customer_id,c.customer_natives;

四、商家浏览统计表

 

create external table if not exists dws_nshop.dws_nshop_supplier_user(
  supplier_id string comment '商家id',
  supplier_type int comment '供应商类型：1.自营，2.官方 3其他',
  view_count int comment '浏览次数',
  comment_users int comment '关注人数',
  comment_area_code int comment '关注地区数量',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/supplier/dws_nshop_supplier_user/';

思路：
1、通过浏览表千方百计的跟产品表关联，产品表可以获取商家表（供应商、店铺）
2、根据商家id以及商家类型分组，统计指标

insert overwrite table dws_nshop.dws_nshop_supplier_user partition(bdp_day='20231227')
select
d.supplier_code,
d.supplier_type,
count(1) view_count,
count(distinct user_id) comment_users,
count(distinct area_code) comment_area_code,
current_timestamp()

from dwd_nshop.dwd_nshop_actlog_pdtview a
join dim_nshop.dim_pub_page  b
on a.target_id= b.page_code  and a.bdp_day='20231227'
join dim_nshop.dim_pub_product c
on b.page_target = c.product_code
join dim_nshop.dim_pub_supplier d
on c.supplier_code= d.supplier_code
group by d.supplier_code,d.supplier_type;

 五、商家日销售统计表

create external table if not exists dws_nshop.dws_nshop_supplier_sales(
  supplier_id string comment '供应商id',
  supplier_type int comment '供应商类型：1.自营，2.官方 3其他',
  sales_users int comment '购物人数',
  sales_users_area int comment '购物地区数量',
  sales_orders int comment '购物订单数',
  salaes_orders_pay DECIMAL(10,1) comment '订单金额',
  salaes_orders_district DECIMAL(10,1) comment '订单优惠金额',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/supplier/dws_nshop_supplier_sales/';

insert overwrite table dws_nshop.dws_nshop_supplier_sales partition(bdp_day='20231227')
select 
   s.supplier_code,
   s.supplier_type,
  count(distinct c.customer_id) sales_users,
  count(distinct c.customer_natives) sales_users_area,
  count(1)  sales_orders,
  sum(payment_money)  salaes_orders_pay,
  sum(district_money)  salaes_orders_district,
  current_timestamp()
  from 
dim_nshop.dim_pub_supplier   s
join 
dwd_nshop.dwd_nshop_orders_details  o
on o.supplier_code = s.supplier_code
join ods_nshop.ods_02_customer  c
on o.customer_id = c.customer_id

where o.bdp_day='20231227'
group by s.supplier_code,s.supplier_type;

 六、广告投放统计表

create external table if not exists dws_nshop.dws_nshop_release_user(
  release_sources string comment '投放渠道',
  release_category string comment '投放浏览产品分类',
  release_users int comment '投放浏览用户数',
  release_product_page int comment '投放浏览产品页面数',
  ct bigint comment '创建时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/release/dws_nshop_release_user/';

insert overwrite table dws_nshop.dws_nshop_release_user partition(bdp_day='20231227')
select 
      release_sources,
      release_category,
      count(distinct customer_id) release_users,
      count(distinct release_product_page) release_product_page,
      current_timestamp()
      from dwd_nshop.dwd_nshop_releasedatas 
   where bdp_day='20231227' group by release_sources,release_category;

七、用户营销活动表

create external table if not exists dws_nshop.dws_nshop_user_release(
  user_id string comment '用户id',
  os string comment '手机系统',
  os_version string comment '手机系统版本',
  manufacturer string comment '手机制造商',
  carrier string comment '电信运营商',
  network_type string comment '网络类型',
  area_code string comment '地区编码',
  source_count int comment '投放来源数量',
  ct bigint comment '产生时间'
) partitioned by (bdp_day string)
stored as parquet
location '/data/nshop/dws/user/dws_nshop_user_release/';

insert overwrite table dws_nshop.dws_nshop_user_release partition(bdp_day='20231227')
select 
distinct 
a.customer_id,
a.os ,
a.os_version ,
a.manufacturer,
b.carrier,
b.network_type,
a.area_code ,
count(1) over (partition by a.release_sources),
current_timestamp()
from 
dwd_nshop.dwd_nshop_releasedatas a
join ods_nshop.ods_nshop_01_useractlog b
on a.customer_id = b.customer_id and a.bdp_day='20231227' and b.bdp_day='20231227';