第五阶段模块二作业

最新推荐文章于 2021-08-09 09:57:13 发布

匿名张3

最新推荐文章于 2021-08-09 09:57:13 发布

阅读量385

点赞数 1

分类专栏：作业笔记

本文链接：https://blog.csdn.net/crckerfang/article/details/118614771

版权

作业笔记专栏收录该内容

28 篇文章 4 订阅

订阅专栏

题目：
1.用拉链表实现核心交易分析中DIM层商家维表，并实现该拉链表的回滚
（自己构造数据，编写SQL，并要有相应的文字说明）

2.在会员分析中计算沉默会员数和流失会员数：

沉默会员的定义：只在安装当天启动过App，而且安装时间是在7天前
流失会员的定义：最近30天未登录的会员

3.在核心交易分析中完成如下指标的计算

统计2020年每个季度的销售订单笔数、订单总额
统计2020年每个月的销售订单笔数、订单总额
统计2020年每周（周一到周日）的销售订单笔数、订单总额
统计2020年国家法定节假日、休息日、工作日的订单笔数、订单总额

解题步骤：
第一题：
创建商家维表

drop table if exists dim.dim_shops;
create table dim.dim_shops(
shopid string COMMENT '商店编号',
shopName string COMMENT '商店名称',
cityId string COMMENT '城市编号',
cityName string COMMENT '城市名称',
regionId string COMMENT '地区编号',
regionName string COMMENT '地区名称'
) 
COMMENT '商家维度表'
PARTITIONED BY (dt string)
row format delimited fields terminated by ',';

构造数据并保存为/root/tmp/shop.dat

001,1号商店,S001,上海市,R001,黄浦区,2021-01-01
002,2号商店,S001,上海市,R002,嘉定区,2021-01-01
003,3号商店,S001,上海市,R003,浦东新区,2021-01-01
001,10号商店,S001,上海市,R001,黄浦区,2021-01-02
004,4号商店,S001,上海市,R001,黄浦区,2021-01-02
005,5号商店,S001,上海市,R001,黄浦区,2021-01-02
006,6号商店,S001,上海市,R001,黄浦区,2021-01-02
001,100号商店,S001,上海市,R001,黄浦区,2021-01-03
005,15号商店,S001,上海市,R001,黄浦区,2021-01-03
007,7号商店,S001,上海市,R001,黄浦区,2021-01-03
008,8号商店,S001,上海市,R001,黄浦区,2021-01-03
003,13号商店,S001,上海市,R001,黄浦区,2021-01-04
006,16号商店,S001,上海市,R001,黄浦区,2021-01-04
001,1号商店,S001,上海市,R001,黄浦区,2021-01-04

载入数据商店维表数据

drop table if exists dim.tmp;
create table dim.tmp(
shopid string COMMENT '商店编号',
shopName string COMMENT '商店名称',
cityId string COMMENT '城市编号',
cityName string COMMENT '城市名称',
regionId string COMMENT '地区编号',
regionName string COMMENT '地区名称',
dt string) 
row format delimited fields terminated by ','; 
load data local inpath '/root/tmp/shop.dat' into table dim.tmp;
set hive.exec.dynamic.partition.mode=nonstrict;
insert into table dim.dim_shops partition(dt) select * from dim.tmp;

商店维表数据成功载入

hive (dim)> show partitions dim.dim_shops;
OK
partition
dt=2021-01-01
dt=2021-01-02
dt=2021-01-03
dt=2021-01-04

初始化商家拉链维表

drop table if exists dim.dim_shops_his;
create table dim.dim_shops_his(
shopid string COMMENT '商店编号',
shopName string COMMENT '商店名称',
cityId string COMMENT '城市编号',
cityName string COMMENT '城市名称',
regionId string COMMENT '地区编号',
regionName string COMMENT '地区名称',
start_date string,
end_date string) 
row format delimited fields terminated by ','; 

insert overwrite table dim.dim_shops_his
select
  shopid,
  shopName,
  cityId,
  cityName,
  regionId,
  regionName,
  dt as start_date,
  '9999-12-31' as end_date
from
  dim.dim_shops
where
  dt = '2021-01-01'

创建载入数据脚本

#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql=" 
insert
  overwrite table dim.dim_shops_his
select
  shopid,
  shopName,
  cityId,
  cityName,
  regionId,
  regionName,
  dt as start_date,
  '9999-12-31' as end_date
from
  dim.dim_shops
where
  dt = '$do_date'
union all
select
  B.shopid,
  B.shopName,
  B.cityId,
  B.cityName,
  B.regionId,
  B.regionName,
  B.start_date,
  case
    when B.end_date = '9999-12-31'
    and A.shopid is not null then date_add('$do_date', -1)
    else B.end_date
  end as end_date
from
  (
    select
      *
    from
      dim.dim_shops
    where
      dt = '$do_date'
  ) A
  right join dim.dim_shops_his B on A.shopid = B.shopid"
  hive -e "$sql"

载入数据

sh load_dim_shops_his.sh 2021-01-02
sh load_dim_shops_his.sh 2021-01-03
sh load_dim_shops_his.sh 2021-01-04

查看数据

select * from dim.dim_shops_his order by shopid;
001	1号商店	S001	上海市	R001	黄浦区	2021-01-01	2021-01-01
001	100号商店	S001	上海市	R001	黄浦区	2021-01-03	2021-01-03
001	1号商店	S001	上海市	R001	黄浦区	2021-01-04	9999-12-31
001	10号商店	S001	上海市	R001	黄浦区	2021-01-02	2021-01-02
002	2号商店	S001	上海市	R002	嘉定区	2021-01-01	9999-12-31
003	3号商店	S001	上海市	R003	浦东新区	2021-01-01	2021-01-03
003	13号商店	S001	上海市	R001	黄浦区	2021-01-04	9999-12-31
004	4号商店	S001	上海市	R001	黄浦区	2021-01-02	9999-12-31
005	15号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31
005	5号商店	S001	上海市	R001	黄浦区	2021-01-02	2021-01-02
006	6号商店	S001	上海市	R001	黄浦区	2021-01-02	2021-01-03
006	16号商店	S001	上海市	R001	黄浦区	2021-01-04	9999-12-31
007	7号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31
008	8号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31

拉链表回滚到2021-01-03

#!/bin/bash
source /etc/profile
if [ -n "$1" ] ;then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
sql="
drop table dim.tmp;
create table dim.tmp as 
select
  shopid,
  shopName,
  cityId,
  cityName,
  regionId,
  regionName,
  start_date,
  end_date
from
  dim.dim_shops_his
where end_date<'$do_date'
union all
select
  shopid,
  shopName,
  cityId,
  cityName,
  regionId,
  regionName,
  start_date,
  '9999-12-31' as end_date
from
  dim.dim_shops_his
where 
start_date <= '$do_date' and
end_date>='$do_date'"
hive -e "$sql"
sql1="insert overwrite table dim.dim_shops_his select * from dim.tmp"
hive -e "$sql1"

执行脚本并查看结果

sh roll_back.sh 2021-01-03
select * from dim.dim_shops_his;
001	1号商店	S001	上海市	R001	黄浦区	2021-01-01	2021-01-01
002	2号商店	S001	上海市	R002	嘉定区	2021-01-01	9999-12-31
003	3号商店	S001	上海市	R003	浦东新区	2021-01-01	9999-12-31
001	10号商店	S001	上海市	R001	黄浦区	2021-01-02	2021-01-02
004	4号商店	S001	上海市	R001	黄浦区	2021-01-02	9999-12-31
005	5号商店	S001	上海市	R001	黄浦区	2021-01-02	2021-01-02
006	6号商店	S001	上海市	R001	黄浦区	2021-01-02	9999-12-31
001	100号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31
005	15号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31
007	7号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31
008	8号商店	S001	上海市	R001	黄浦区	2021-01-03	9999-12-31

以下内容借鉴于https://zhuanlan.zhihu.com/p/356068565
第二题：
计算沉默会员

select 
count(*) clientNum 
from (
        select device_id, count(*) cnt 
        from dws.dws_member_start_day 
        where dt <= date_add(current_date,-7)
        group by device_id having cnt = 1
)tmp;

计算流失会员

select count(0)
from (select distinct device_id 
    from dws.dws_member_start_day 
    where dt <date_add(current_date,30))a--30天前的用户
left join (select distinct device_id 
    from dws.dws_member_start_day 
    where dt >=date_add(current_date,30))b--30天内的用户
on a.device_id=b.device_id
where b.device_id is null;

第三题：
数据准备
创建表

drop table if exists dws.dws_trade_orders_day;
create table if not exists dws.dws_trade_orders_day(
  day_dt string comment '日期：yyyy-MM-dd',
  day_cnt decimal comment '日订单笔数',
  day_sum decimal comment '日订单总额'
) comment '日订单统计表';

插入数据

insert overwrite table dws.dws_trade_orders_day 
select day_dt, count(*) day_cnt, sum(totalMoney) day_sum 
from (select distinct orderid, dt, totalMoney 
          from dwd.dwd_trade_orders 
          where status >= 0 --订单状态 -3 用户拒收 -2未付款的订单 -1用户取消 0 待发货 1配送中 2用户确认收货
          and dataFlag = '1'--订单有效标志 -1 删除 1 有效
) tmp 
group by dt;

统计2020年每个季度的销售订单笔数、订单总额

select year,quarter,sum(day_cnt) quarter_cnt,sum(day_sum) quarter_sum from (
   select substr(day_dt,0,4) year,
          case when substr(day_dt,6,2)="01" or substr(day_dt,6,2)="02" or 
          substr(day_dt,6,2)="03" then "1" 
               when substr(day_dt,6,2)="04" or substr(day_dt,6,2)="05" or 
          substr(day_dt,6,2)="06" then "2"
               when substr(day_dt,6,2)="07" or substr(day_dt,6,2)="08" or 
          substr(day_dt,6,2)="09" then "3"
               when substr(day_dt,6,2)="10" or substr(day_dt,6,2)="11" or 
          substr(day_dt,6,2)="12" then "4" end as quarter, 
          day_cnt,day_sum
          from  dws.dws_trade_orders_day
          where substr(day_dt,0,4)="2020"
)tmp group by year,quarter;

统计2020年每个月的销售订单笔数、订单总额

select year,month,sum(day_cnt) month_cnt,sum(day_sum) month_sum
from (
            select substr(day_dt,0,4) year,
                   substr(day_dt,6,2) month,
                   day_cnt, day_sum 
                from dws.dws_trade_orders_day
                where substr(day_dt,0,4)="2020")tmp 
group by year,month;

统计2020年每周（周一到周日）的销售订单笔数、订单总额

select year,week,sum(day_cnt) week_cnt,sum(day_sum) week_sum
from (
            select substr(day_dt,0,4) year,
                   weekofyear(day_dt) week,
                   day_cnt, day_sum 
                from dws.dws_trade_orders_day
                where substr(day_dt,0,4)="2020")tmp 
group by year,week;

统计2020年国家法定节假日、休息日、工作日的订单笔数、订单总额
创建日期信息维表：dim_day_info

drop table if exists dim.dim_day_info;
create table if not exists dim.dim_day_info(
  day_dt string comment '日期',
  is_holiday int comment '节假日标识： 0不是 1是',
  is_workday int comment '工作日标识 0不是 1是'
) comment '日期信息表';

统计2020节假日的订单笔数，订单总额

SELECT nvl(sum(day_cnt), 0) holiday_cnt, nvl(sum(day_sum), 0) holiday_sum
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_holiday = 1;

统计2020年休息日的订单笔数，订单总额

SELECT nvl(sum(day_cnt), 0) weekend_cnt,nvl(sum(day_sum), 0) weekend_sum
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_workday = 0 and B.is_holiday = 0;

统计2020节工作日的订单笔数，订单总额

SELECT nvl(sum(day_cnt), 0) workday_cnt,nvl(sum(day_sum), 0) workday_sum
FROM dws.dws_trade_orders_day A
LEFT JOIN dim.dim_day_info B ON A.day_dt = B.day_dt
WHERE B.is_workday = 1;

匿名张3

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
第五阶段模块二作业

题目：1.用拉链表实现核心交易分析中DIM层商家维表，并实现该拉链表的回滚（自己构造数据，编写SQL，并要有相应的文字说明）2.在会员分析中计算沉默会员数和流失会员数：沉默会员的定义：只在安装当天启动过App，而且安装时间是在7天前流失会员的定义：最近30天未登录的会员3.在核心交易分析中完成如下指标的计算统计2020年每个季度的销售订单笔数、订单总额统计2020年每个月的销售订单笔数、订单总额统计2020年每周（周一到周日）的销售订单笔数、订单总额统计2020年国家法定节假日、休
复制链接

扫一扫