1、统计2017年8月，有多少商品销售的天数超过1天，2天，3天，……31天的；
SELECT sales_days
,count(DISTINCT GoodsID)
FROM
(
SELECT GoodsID
,count(DISTINCT SDate) as sales_days
FROM OrderItem
where SDate BETWEEN '20170801' and '20170831'
and ShopID = 'WDGC'
GROUP BY GoodsID) t1
GROUP BY sales_days
ORDER BY sales_days;
2、提取会员主要的数据指标（字段）： 会员ID、会员注册时间、会员首单时间、会员最后一次购买时间、累计购买金额、累计购买次
数、累计购买商品数、最后一单距离2017年12月1号的时间间
select t1.MemberID
,t2.res_time
,min(t1.SDate) as '会员首单时间'
,max(t1.SDate) as '会员末单时间'
,sum(t1.CashValue) as '总金额'
,count(DISTINCT t1.SheetID) as '购买次数'
,count(DISTINCT t3.GoodsID) as '购买商品数'
,datediff('20171201',max(t1.SDate)) as '间隔时间'
from OrderList t1
JOIN MemberInfo t2 ON t1.MemberID=t2.MemberID
JOIN OrderItem t3 on t1.SheetID=t3.SheetID
where t1.MemberID is not null
GROUP BY t1.MemberID
,t2.res_time
3、计算每天的客流，以及每天客流占当周全店客流的比例
SELECT t2.ShopID
,t2.SDate
,t2.order_number
,concat(round(t2.order_number/t3.total_order_number*100,2),'%') as rate
FROM
(
select t1.ShopID
,t1.SDate
,count(DISTINCT t1.SheetID) as 'order_number'
from OrderList t1
where t1.SDate BETWEEN '20170815' and '20170821'
and t1.ShopID = 'WDGC'
GROUP BY t1.ShopID
,t1.SDate) t2
JOIN
(select count(DISTINCT t1.SheetID) as 'total_order_number'
,t1.ShopID
FROM OrderList t1
where t1.SDate BETWEEN '20170815' and '20170821'
and t1.ShopID = 'WDGC'
) t3 on t2.ShopID = t3.ShopID
1
计算周末与非周末的客流，以及占当周全店客流的比例
SELECT t2.ShopID
,t2.week_kind
,t2.order_number
,concat(round(t2.order_number/t3.total_order_number*100,2),'%') as rate
FROM
(
select t1.ShopID
,case when weekday(t1.SDate) in (0,6) then '周末' else '非周末' end as week_kind
,count(DISTINCT t1.SheetID) as 'order_number'
from OrderList t1
where t1.SDate BETWEEN '20170815' and '20170821'
and t1.ShopID = 'WDGC'
GROUP BY t1.ShopID
,case when weekday(t1.SDate) in (0,6) then '周末' else '非周末' end) t2
JOIN
(select count(DISTINCT t1.SheetID) as 'total_order_number'
,t1.ShopID
FROM OrderList t1
where t1.SDate BETWEEN '20170815' and '20170821'
and t1.ShopID = 'WDGC'
) t3 on t2.ShopID = t3.ShopID
4、分别计算各个类别的客流数，以及占当周全店客流的比例。
SELECT a.ShopID
,a.CateID
,a.CateName
,a.cate_number
,concat(round(a.cate_number/b.total_number*100,2),'%') as rate
FROM
(
select t2.ShopID
,t2.CateID
,t1.CateName
,count(DISTINCT t2.SheetID) as cate_number
from Category t1
join OrderItem t2 on t1.CateID=t2.CateID
where t2.SDate BETWEEN '20170815' and '20170821'
and t2.ShopID = 'WDGC'
GROUP BY t2.ShopID
,t2.CateID
,t1.CateName) a
JOIN
(
select shopID
,count(DISTINCT SheetID) as total_number
from OrderItem
where SDate BETWEEN '20170815' and '20170821'
and ShopID = 'WDGC') b
5、各个类别与周末与非周末交叉的客流，以及占当周全店客流的比例
SELECT a.ShopID
,a.CateID
,a.CateName
,a.cate_number
,a.week_kind
,concat(round(a.cate_number/b.total_number*100,2),'%') as rate
FROM
(
select t2.ShopID
,t2.CateID
,t1.CateName
,count(DISTINCT t2.SheetID) as cate_number
,case when weekday(t2.SDate) in (0,6) then '周末' else '非周末' end as week_kind
from Category t1
join OrderItem t2 on t1.CateID=t2.CateID
where t2.SDate BETWEEN '20170815' and '20170821'
and t2.ShopID = 'WDGC'
GROUP BY case when weekday(t2.SDate) in (0,6) then '周末' else '非周末' end
,t2.ShopID
,t2.CateID
,t1.CateName
) a
JOIN
(
select shopID
,count(DISTINCT SheetID) as total_number
from OrderItem
where SDate BETWEEN '20170815' and '20170821'
and ShopID = 'WDGC') b
6、取2017年8月份，会员的消费特征分析，对比会员 VS 非会员： 平均客单价=销售额/客流量（订单）
SELECT ShopID
,sum(CashValue) as AMT
,count(DISTINCT SheetID) as order_num
,case when MemberID is null then '非会员' else '会员' end member_type
,round(sum(CashValue)/count(DISTINCT SheetID),2) as 'kdj'
FROM OrderList
WHERE SDate BETWEEN '20170801' and '20170831'
GROUP BY ShopID
,case when MemberID is null then '非会员' else '会员' end
image.png
# 取2017年8月份，会员的消费特征分析，对比会员 VS 非会员： 平均客单价=销售额/客流量（订单）
# 不同订单销售金额的客单价
SELECT ShopID
,case when MemberID is null then '非会员' else '会员' end member_type
,case when CashValue<=20 then '(0-20]'
when CashValue>20 and CashValue <=40 then '(20-40]'
when CashValue>40 and CashValue <=60 then '(40-60]'
when CashValue>60 and CashValue <=80 then '(60-80]'
when CashValue>80 and CashValue <=100 then '(80-100]'
when CashValue>100 and CashValue <=150 then '(100-150]'
when CashValue>150 and CashValue <=200 then '(150-200]'
when CashValue>200 then '(200-)'
end as order_type
,sum(CashValue) as AMT
,count(DISTINCT SheetID) as order_num
,round(sum(CashValue)/count(DISTINCT SheetID),2) as 'kdj'
FROM OrderList
WHERE SDate BETWEEN '20170801' and '20170831'
GROUP BY ShopID
,case when MemberID is null then '非会员' else '会员' end
,case when CashValue<=20 then '(0-20]'
when CashValue>20 and CashValue <=40 then '(20-40]'
when CashValue>40 and CashValue <=60 then '(40-60]'
when CashValue>60 and CashValue <=80 then '(60-80]'
when CashValue>80 and CashValue <=100 then '(80-100]'
when CashValue>100 and CashValue <=150 then '(100-150]'
when CashValue>150 and CashValue <=200 then '(150-200]'
when CashValue>200 then '(200-)'
end
# 取2017年8月份，会员的消费特征分析，对比会员 VS 非会员：订单购买的商品数分布【购买1，2，3，4，5，。。。件,有多少订单】
SELECT t2.Qty
,case when t1.MemberID is null then '非会员' ELSE '会员' end as member_type
,count(DISTINCT t1.SheetID) as order_num
FROM OrderList t1
JOIN OrderItem t2 on t1.SheetID= t2.SheetID
WHERE t1.SDate BETWEEN '20170801' and '20170831'
and t1.ShopID ='WDGC'
GROUP BY t2.Qty
,case when t1.MemberID is null then '非会员' ELSE '会员' end
# 取2017年8月份，会员的消费特征分析，对比会员 VS 非会员：周末 VS 非周末 中的会员 VS非会员 的订单量占比分布
SELECT t2.ShopID
,t2.week_type
,t2.member_type
,t2.order_num
,concat(round(t2.order_num/@total_order_num*100,2),'%')
FROM
(
SELECT t1.ShopID
,case when weekday(t1.SDate) in (0,6) then '周末' else '非周末' end week_type
,case when t1.MemberID is null then '非会员' else '会员' end member_type
,count(DISTINCT t1.SheetID) as order_num
FROM OrderList t1
where t1.SDate BETWEEN '20170801' and '20170831'
AND ShopID = 'WDGC'
GROUP BY case when weekday(t1.SDate) in (0,6) then '周末' else '非周末' end
,case when t1.MemberID is null then '非会员' else '会员' end) t2
JOIN
(select @total_order_num :=count(t1.SheetID)
from OrderList t1
where t1.SDate BETWEEN '20170801' and '20170831'
AND ShopID = 'WDGC') t3
会员销售订单占比有待提高，需要加强对会员的管理
image.png
