专栏首页SQL实现SQL 统计用户留存

SQL 统计用户留存

问题描述

有一个用来记录每日客户消耗数据的表 t,它的表结构如下:

字段

类型

描述

created_day

Date

消耗日期

customer_id

Integer

客户ID

amount

Integer

消耗金额

要求:

统计出头部客户、腰部客户、尾部客户在上个月(2020-06-01 ~ 2020-06-30)的留存情况。

输出结果的格式:

层级

客户数量

留存数量

头部客户

腰部客户

尾部客户

数据定义:

  • 头部客户:上个月消耗金额大于等于 30000 的客户;
  • 腰部客户:上个月消耗金额在 10000 ~ 30000(不包含30000)的客户;
  • 尾部客户:上个月消耗金额小于 10000 的客户;
  • 留存:最近两个月(上个月和本月)消耗金额大于 0 的客户;
  • 时间:上个月(2020-06-01 ~ 2020-06-30)、本月(2020-07-01 ~ 2020-07-31)。

解决方案

今天这个问题和我上一篇文章(SQL 订单揽收统计)里面的问题很相似,只不过这里要求多统计一列,因此,解决的思路也差不多。

下面我将用 CTE 来演示每个步骤。

第一步,计算出上个月每个客户的消耗金额。

with t1 AS
(SELECT
  customer_id,
  SUM(amount) AS amount
FROM
  t
WHERE created_day BETWEEN '2020-06-01'
  AND '2020-06-30'
GROUP BY customer_id)
SELECT * FROM t1

第二步,在第一步的基础上,统计头部客户、腰部客户、尾部客户的数量。

t2 AS
(SELECT
  CASE
    WHEN amount >= 30000
    THEN 1
    WHEN amount >= 10000
    THEN 2
    ELSE 3
  END AS customer_level,
  COUNT(*) AS customter_cnt
FROM
  t1
GROUP BY customer_level)
SELECT * FROM t2

在脚本中使用代码 1、2、3 分别表示头部客户、腰部客户、尾部客户。

需要注意的是,在 GROUP BY 子句中使用了 SELECT 子句中的字段别名 customer_level,这种语法在 MySQL 上能编译通过,在其它数据库中则不行。

第三步,计算留存。根据留存的定义,只要客户在本月中有消耗,就计入留存数。比如客户 A,A 在上个月的消耗金额是 40000,那么 A 属于头部客户,假如 A 在本月的消耗金额大于 0,A 就为【留存】贡献了 1 。

我们在 t1 之后插入表达式 t12t12 用来计算每个客户的消耗金额和留存状态,它的脚本如下:

t12 AS
(SELECT
  t1.customer_id,
  t1.amount,
  IF(tmp.amount > 0, 1, 0) AS keep_state
FROM
  t1
  LEFT JOIN
    (SELECT
      customer_id,
      SUM(amount) AS amount
    FROM
      t
    WHERE created_day BETWEEN '2020-07-01'
      AND '2020-07-31'
    GROUP BY customer_id) tmp
    ON tmp.customer_id = t1.customer_id)
SELECT * FROM t12

t12 中的左连接也可以改造成标量子查询。

完整的 SQL 实现:

with t1 AS
(SELECT
  customer_id,
  SUM(amount) AS amount
FROM
  t
WHERE created_day BETWEEN '2020-06-01'
  AND '2020-06-30'
GROUP BY customer_id),
t12 AS
(SELECT
  t1.customer_id,
  t1.amount,
  IF(tmp.amount > 0, 1, 0) AS keep_state
FROM
  t1
  LEFT JOIN
    (SELECT
      customer_id,
      SUM(amount) AS amount
    FROM
      t
    WHERE created_day BETWEEN '2020-07-01'
      AND '2020-07-31'
    GROUP BY customer_id) tmp
    ON tmp.customer_id = t1.customer_id),
t2 AS
(SELECT
  CASE
    WHEN amount >= 30000
    THEN 1
    WHEN amount >= 10000
    THEN 2
    ELSE 3
  END AS customer_level,
  COUNT(*) AS customter_cnt,
  SUM(keep_state) AS keep_cnt
FROM
  t12
GROUP BY customer_level)

SELECT
  CASE
    customer_level
    WHEN 1
    THEN '头部客户'
    WHEN 2
    THEN '腰部客户'
    ELSE '尾部客户'
  END AS '层级',
  customter_cnt AS '客户数量',
  keep_cnt AS '留存数量'
FROM
  t2
ORDER BY customer_level

感兴趣的朋友可以尝试不使用左连接或者标量子查询的写法,而是只查一次 t 表就能实现需求。

封面图片由 Daniel Hannah 在 Pixabay 上发布。

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-08-13

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 打印全年日历

    上文我们实现了“打印一个月日历”的需求,今天在原来的基础实现一个更复杂的需求:打印一年的日历。

    白日梦想家
  • SQL 确定序列里缺失值的范围

    有一个序列表 seq,它有一个存整数序列值的字段叫作 id,原本序列的值是连续递增的,但因某些原因,有的值丢失了,我们希望能通过 SQL 找出缺失值的范围。

    白日梦想家
  • 删除列中的 NULL 值

    今天接到一个群友的需求,有一张表的数据如图 1,他希望能通过 SQL 查询出图 2 的结果。

    白日梦想家
  • CCF-GAIR AI医疗论坛:翻越医疗行业的三座大山

    「AI医疗」专场将以“后深度学习时代的医疗变局”为主题,设立“医学影像AI”、“医疗大数据”、“医疗机器人”三大环节,分别邀请三大领域的顶尖专家,阐述未来人工智...

    AI掘金志
  • Google和微软分别提出分布式深度学习训练新框架:GPipe & PipeDream

    【导读】微软和谷歌一直在致力于开发新的用于训练深度神经网络的模型,最近,谷歌和微软分别发布了新的用于分布式深度学习训练的框架——GPipe 和 PipeDrea...

    AI科技大本营
  • 跨界时尚圈、代言奢侈品的斯坦福AI研究僧,是一个怎样的小哥哥?

    李林 假装发自 巴黎 量子位 出品 | 公众号 QbitAI 你眼里AI研究者、码农、学霸,还都是这样这样这样的? ? 正式通知:你奥特了。 不信你仔细看看这支...

    量子位
  • 一个神经网络学习一切!谷歌又放了个大卫星(附论文)

    李林 问耕 编译自 Arxiv 量子位 出品 | 公众号 QbitAI 最近,Google又在论文题目上口出狂言:One Model To Learn The...

    量子位
  • mysql学习总结07 — 优化(存储层)-存储引擎与缓存

    mysql作为最流行的数据库,在开发过程中仍然有较多优化的空间,mysql的优化主要有4个方向:

    csxiaoyao
  • 利用.htaccess文件实现伪静态

    雨落凋殇
  • mysql学习总结07 — 优化(存储层)-存储引擎与缓存

    mysql作为最流行的数据库,在开发过程中仍然有较多优化的空间,mysql的优化主要有4个方向:

    CS逍遥剑仙

扫码关注云+社区

领取腾讯云代金券