SQL 项目实战专题三：选取某一周，任何一家店：(不同情况下客流

一、业务背景介绍：

1、数据源说明：

二家超市的真实数据，数据时间段是从 20160101 到 20171231。

2、超市的主要商业场景：

• 零售最重要的是要让顾客来，来了以后可以购买超市中相关的商品，销售额=客流*客单价，所以客流量是必须每天关注的数据指标。
• 通过对客流的分析，知道什么时候是客流高峰期，什么时候是品类最能吸引客流，从而对人员排班安排，以及品类的结构做出相应分析。
• 业务指标定义介绍：
  客流数：一般用订单来衡量客流，订单也就是顾客购买的时候的小票；
  客单价：销售商品金额/客流，也就是顾客每次来了以后购买商品金额。

二、思考以下问题：

1. 如果发现不同时间点的客流占比不一样，你觉得背后代表是什么？
2. 再结合什么样的数据，可以更多的对顾客的消费特征进行分析？
3. 把这些数据结果内容拿出来做个图分析看看，会有什么假设形成？

三、需求：

1、计算每天的客流，以及每天客流占当周全店客流的比例
2、计算周末与非周末的客流，以及占当周全店客流的比例
3、分别计算各个类别的客流数，以及占当周全店客流的比例
4、各个类别与周末与非周末交叉的客流，以及占当周全店客流的比例。

说明：
客流：按订单统计，一个订单表示产生一个客流
思考：为什么类别层级的客流相加会不等于全店客流。

四、逻辑说明：

1、 选取某个自然周，统计每家门店的客流。
2、 需要计算出每周，每家门店整体的客流是多少。
3、 然后需要按相关的维度，例如：周末与非周末来拆分的相关客流

用到的SQL 知识点：
1、 子查询的理解与掌握；
2、 Case when 分类

五、代码实现过程：

选取时间：20160905-20160911

• 1、计算每天的客流，以及每天客流占当周全店客流的比例

SELECT b.ShopID
      ,b.SDate
      ,b.day_sheet
      ,a.total_sheets
      ,CONCAT(TRUNCATE(day_sheet/total_sheets * 100,2),"%") AS rate
FROM(SELECT ShopID                 #查询当周总客流
           ,COUNT(DISTINCT SheetID) AS total_sheets
      FROM OrderList
      WHERE SDate BETWEEN '20160905' AND '20160911' AND ShopID = 'WDGC'
      GROUP BY ShopID) AS a
INNER JOIN(SELECT ShopID           # 查询当周每日客流
                 ,SDate
                 ,COUNT(DISTINCT SheetID) AS day_sheet
           FROM OrderList
           WHERE SDate BETWEEN '20160905' AND '20160911'
           GROUP BY ShopID
                   ,SDate
           HAVING ShopID = 'WDGC') AS b ON a.ShopID = b.ShopID;

1

• 2、计算周末与非周末的客流，以及占当周全店客流的比例

SELECT a.ShopID
      ,a.day_type
      ,a.week_sheets
      ,b.total_sheets
      ,CONCAT(TRUNCATE(week_sheets/total_sheets * 100,2),"%") AS rate
FROM (SELECT ShopID                   # 周末与非周末客流统计
            ,COUNT(DISTINCT SheetID) AS week_sheets
            ,CASE WHEN WEEKDAY(SDate) IN (5,6) THEN '周末' ELSE '非周末' END AS day_type
      FROM OrderList
      WHERE SDate BETWEEN '20160905' AND '20160911' 
      GROUP BY ShopID
              ,day_type) AS a
INNER JOIN (SELECT ShopID                 # 当周全店客流统计
                  ,COUNT(DISTINCT SheetID) AS total_sheets
            FROM OrderList
            WHERE SDate BETWEEN '20160905' AND '20160911' 
            GROUP BY ShopID) AS b ON a.ShopID = b.ShopID

2

• 3、分别计算各个类别的客流数，以及占当周全店客流的比例。

SELECT a.ShopID
      ,a.CateName
      ,a.cate_sheets
      ,b.total_sheets
      ,CONCAT(TRUNCATE(a.cate_sheets/b.total_sheets * 100,2),"%") AS rate
FROM(SELECT oi.ShopID                     # 当周各品类客流统计
           ,cg.CateName
           ,COUNT(DISTINCT oi.SheetID) AS cate_sheets
      FROM OrderItem AS oi
      INNER JOIN Category AS cg ON oi.CateID = cg.CateID
      WHERE SDate BETWEEN '20160905' AND '20160911' AND ShopID = 'WDGC'
      GROUP BY cg.CateName
              ,oi.ShopID) AS a
INNER JOIN (SELECT ShopID                 # 当周全店客流统计
                  ,COUNT(DISTINCT SheetID) AS total_sheets
            FROM OrderList
            WHERE SDate BETWEEN '20160905' AND '20160911' 
            GROUP BY ShopID) AS b ON a.ShopID = b.ShopID
ORDER BY a.CateName;

3

• 4、各个类别与周末与非周末交叉的客流，以及占当周全店客流的比例。

SELECT a.ShopID
      ,a.day_type
      ,a.CateName
      ,a.cate_sheets
      ,b.total_sheets
      ,CONCAT(TRUNCATE(a.cate_sheets/b.total_sheets * 100,2),"%") AS rate
FROM (SELECT ShopID                              # 各品类在周末与非周末的客流统计
            ,CateName
            ,COUNT(DISTINCT SheetID) AS cate_sheets
            ,CASE WHEN WEEKDAY(SDate) IN (5,6) THEN '周末' ELSE '非周末' END AS day_type
      FROM OrderItem AS oi
      INNER JOIN Category  AS cg ON oi.CateID = cg.CateID
      WHERE SDate BETWEEN '20160905' AND '20160911' 
      GROUP BY ShopID
              ,CateName
              ,day_type) AS a
INNER JOIN (SELECT ShopID                 # 当周全店客流统计
                  ,COUNT(DISTINCT SheetID) AS total_sheets
            FROM OrderList
            WHERE SDate BETWEEN '20160905' AND '20160911' 
            GROUP BY ShopID) AS b ON a.ShopID = b.ShopID
ORDER BY a.day_type
        ,a.cate_sheets;

4

说明：这里不做过多业务上的分析，主要目的是将需求用代码实现出来。