会员分析与订单统计

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

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

下面的统计使用了会员日启动汇总dws.dws_member_start_day，该表存储着每天启动APP的会员明细数据，该表的字段如下：

`device_id` string,
`uid` string, 
`app_v` string, 
`os_type` string, 
`language` string, 
`channel` string, 
`area` string, 
`brand` string

1、沉默会员数
统计逻辑说明：统计安装时间在前7天的所有用户的历史登录天数，如果只登录过1天，说明他第一次启动后，就没有再来，这样就找出了只在安装当天启动过APP的用户

select count(1) as silent_uv
from 
(select `device_id`,count(distinct dt) as dt_count
from dws.dws_member_start_day
where dt < date_sub(current_date(), 7)
group by `device_id`
) t1
where dt_count = 1

2、流失会员数
统计逻辑说明：题目没有说明是统计哪个月的流失会员数，所以我统计了全部的流失会员，t1表是全部的会员，t2表是最近30天登录过的会员，将t1表和t2表left join匹配，不在t2表的中，说明最近30天没有登录

select count(1) as runOff_uv
from
(select `device_id`
from dws.dws_member_start_day
group by `device_id`
) t1
left join
(select `device_id`
from dws.dws_member_start_day
where dt >= date_sub(current_date(),30)
group by `device_id`
) t2
where t2.`device_id` is null

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

下面的统计指标均使用订单明细表dws.dws_trade_orders聚合统计得到，该表的字段如如下：

orderid string, -- 订单id 
cat_3rd_id string, -- 商品三级分类id 
shopid string, -- 店铺id 
paymethod tinyint, -- 支付方式 
productsnum bigint, -- 商品数量 
paymoney double, -- 订单商品明细金额 
paytime string -- 订单时间

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

select quarter(dt) as `季度`,
count(distinct orderid) as `订单笔数` ,
sum(paymoney) as `订单总额`
from dws.dws_trade_orders
where year(dt) = '2020'
group by quarter(dt)

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

select month(dt) as `月`,
count(distinct orderid) as `订单笔数` ,
sum(paymoney) as `订单总额`
from dws.dws_trade_orders
where year(dt) = '2020'
group by month(dt)

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

select weekofyear(dt) as `周`,
count(distinct orderid) as `订单笔数` ,
sum(paymoney) as `订单总额`
from dws.dws_trade_orders
where year(dt) = '2020'
group by weekofyear(dt)

4、统计2020年国家法定节假日、休息日、工作日的订单笔数、订单总额

--1、使用一个法定节假日的维表去匹配判断是否为节假日，这个表中包含法定节假日的日期，以及补班的日期
--2、当两张表能匹配上，且排除匹配上的补班的日期，那这写就是法定节假日
--3、使用pmod(datediff(t1.dt, '1920-01-01') - 3, 7) not in (0,6)选出的周六日加上补班的日期，就是工作日
--4、其余则为休息日
select 
case 
when t2.dt is not null and t2.dt !='补班' then "法定节假日"
when (pmod(datediff(t1.dt, '1920-01-01') - 3, 7) not in (0,6) or (t2.dt is not null and t2.dt ='补班')) then "工作日"
else "休息日"
end as `日期类型`,
count(distinct orderid) as `订单笔数` ,
sum(paymoney) as `订单总额`
from
(select dt,
orderid,
paymoney
from dws.dws_trade_orders
where year(dt) = '2020'
) t1
left join
(select dt, 
holiday
from dim.dim_holiday_2020
) t2
on t1.dt =t2.dt
group by case 
when t2.dt is not null and t2.dt !='补班' then "法定节假日"
when (pmod(datediff(t1.dt, '1920-01-01') - 3, 7) not in (0,6) or (t2.dt is not null and t2.dt ='补班')) then "工作日"
else "休息日"
end

dim.dim_holiday_2020表的数据是2020年的法定节假日的日期和补班日期，数据如下：