SQL项目实战专题二：提取会员主要的数据指标字段

一、业务背景介绍：

1、数据源说明：

项目的实战背景是二家超市的真实数据，数据时间段是从 20160101 到 20171231。

2、商业场景：

• 场景一：沉睡会员激活

• 通过最后一次购买时间距离当前时间，评估哪些是沉睡用户。从而对于沉睡用户进行刺激，提升用户活跃度。
  进一步可以根据会员历史购买的金额，对于进行价值等级划分。对于不同价值等级的会员，制定不一样的活跃刺激策略。

• 场景二：日常会员监控统计
  统计每天的新注册用户数，购买的金额，及时观察会员的状态的变化。

• 场景三：RFM 模型
  会员的累计购买金额、次数、最后一单到当前的时间：M、F、R，用于构建 RFM 模型，从而及时监控会员活跃状态，构建会员价值等级，从而及时对会员变化及时调整。

• 场景四：会员购买集中度
  通过会员购买商品数，分析会员是不是购买的商品数量少，再结合他的购买的次数与金
  额。分析对于购买一定次数，但是只购买几个商品的顾客，考虑对会员进行交叉销售。

二、思考以下问题：

1、 怎么基于会员的购买次数来确定会员的活跃度？
2、 怎么基于会员最后一单距离当前时间确定会员的活跃度？

三、需求：

• 1、查询会员ID、会员注册时间、会员首单时间、会员最后一次购买时间、最后一单距离2017年12月1号的时间间隔、累计购买次数、累计购买商品数、累计购买金额。

• 2、通过会员的相关数据指标，可以知道会员关键信息，基于这些信息可以构建初步各类专题分析，从而设计相应的运营方案：

四、逻辑分析：

1、 会员数据从哪些表取：

• 会员表：会员注册时间
• 订单表：订单数量，购买金额，会员首单时间、会员最后一次时间，最后一单距离2017年12月1号的时间间隔
• 订单商品表：累计购买商品数，也可以取：订单数量，购买金额，首单日期，最后一单日期
  2、 订单商品明细表与订单表关联导致字段内容重复，需要去重；
  3、 需要使用时间的函数：datediff（日期 1，日期 2） 这二个日期之间的间隔；
  4、 数据量大，可以考虑分开跑；【对于没有会员记录的订单表进行过滤，可以提取数据运算效率】，也可以一起跑

用到的SQL 知识点：

• 多表查询
• 学会使用时间间隔的统计函数

五、代码实现过程：

分开跑（性能更优）：
1.从订单表Orderlist中取会员ID、首单时间、末单时间、末单距20171201的时间间隔、累计购买次数、累计购买金额.

SELECT MemberID
      ,MIN(SDate) AS first_order
      ,MAX(SDate) AS last_order
      ,TIMESTAMPDIFF(DAY,MAX(SDate),"20171201") AS day_interval
      ,COUNT(DISTINCT SheetID) AS count_sheet
      ,SUM(CashValue) AS sum_value
FROM OrderList
WHERE MemberID IS NOT NULL
GROUP BY MemberID;

1

2.从订单编辑表OrderItem中取累计购买商品数。因为OrderItem表没有会员ID，所以需要跟OrderList做内连接,关联字段为SheetID

SELECT ol.MemberID
      ,SUM(DISTINCT oi.GoodsID) AS SUM_goods
FROM OrderItem AS oi
INNER JOIN OrderList AS ol ON oi.SheetID = ol.SheetID
WHERE ol.MemberID IS NOT NULL
GROUP BY ol.MemberID;

2

3.从会员表MemberInfo取会员注册时间

SELECT MemberID
      ,res_time
FROM MemberInfo;

3

一起跑：

SELECT mi.MemberID AS 会员ID
      ,mi.res_time AS 注册时间
      ,MIN(ol.SDate) AS 首单时间
      ,MAX(ol.SDate) AS 最后购买时间
      ,DATEDIFF('20171201',MAX(ol.SDate)) AS 最后一单距离时间
      ,COUNT(DISTINCT ol.SheetID) AS 累计购买次数
      ,COUNT(oi.Qty) AS 累计购买商品数
      ,SUM(oi.SaleValue) AS 累计购买金额
FROM OrderList AS ol
INNER JOIN OrderItem AS oi ON ol.SheetID = oi.SheetID
INNER JOIN MemberInfo AS mi ON mi.MemberID = ol.MemberID
WHERE mi.MemberID IS NOT NULL
GROUP BY mi.MemberID
        ,mi.res_time;

查询结果