mysql实现日期首单统计_手把手教你用 SQL 实现同期群分析

本文目录前言

清洗数据：筛选订单状态为“交易成功”的行

获取首单时间

求月份偏移：求出月份差，并对首次付款时间降采样

计算留存量：通过首次付款时间和月份差进行分组，求唯一的用户id数

求留存率

前言

数据字段、同期群含义、数据概览等细节，请参照前面几篇文章：Dima：手把手教你用 Python 实现同期群分析(1)​zhuanlan.zhihu.comDima：手把手教你用 Python 实现同期群分析(2)​zhuanlan.zhihu.com

本文我们依旧选择最经典，也是数分面试中最常考的留存率作为例子。本文，采用第一种方法实现！

本文所涉及的完整实战数据和代码等，详见百度云网盘链接：

提取码：maz8涉及的SQL脚本、SQL数据源

Ps：本文重代码工具实现，分析的思路及结论暂不涉及！

一、数据清洗

先筛选出 order_status 为“交易成功”的行，并筛选出 nick_name、pay_time 两个字段。由于我们后续的查询都是基于筛选后的数据，因此这里新建一个表 sheet1 去存储查询结果。

二、计算首单时间

对 nick_name 进行groupby，再求最小值即可。

三、计算月份差，重采样首次支付时间

此步骤，会涉及到两个重要的函数：MySQL 对时间戳重采样用：YEAR()、MONTH()等函数；

用于计算日期差的 TIMESTAMPDIFF，具体语法为：TIMESTAMPDIFF(频率，起始时间，结束时间)。注意：在计算月份差之前，需要以用户名称作为依据，拼接用户的首单时间。但由于数据量较大，拼接需要重复遍历整个表很多遍，耗时很长。而当前查询的结果并不是最终结果，我们只需要确保查询语句没有问题即可。因此我们引入分页查询(LIMIT语句)来限制查询结果的行数，从而提高查询效率。注意SQL运行效率问题！

四、计算留存量

将前面的三个步骤汇总，作为子查询，并以“首次支付时间 f_time”以及 “月份差m_diff” 作为条件对数据进行分组，用DISTINCT筛选出唯一的 nick_name 即可求出留存量。

这里创建一个名为 sheet2 的表储存查询结果。sheet2 结果

五、计算留存率

为了实现最终结果，需要多层子查询，多层嵌套！最终结果

第一步，查询首付月份的用户量；最内部的子查询

第二步，计算留存率；二级子查询：计算留存率。

第三步，转置表格，将月份差作为列名；一级子查询结果

最后一步，计算留存率！

根据上图查询结果，重点理解下面两行 SQL脚本 的妙处！

AVG(n.`留存量`) AS "本月新增",

CONCAT(SUM(n.`+1月`),"%") AS "+1月"最终实现留存计算的SQL语句最终留存率！

最后，总结一下，本文涉及到的重要知识点：时间重采样函数：YEAR、MONTH

时间差函数：TIMESTAMPDIFF

流程控制函数：CASE WHEN