SQL统计活跃客户（用户数据连续性分析）

SQL策略：基于日志分析用户活跃度连续性
最新推荐文章于 2024-08-05 12:37:12 发布
原创 最新推荐文章于 2024-08-05 12:37:12 发布 · 1.9k 阅读 · 8 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #数据库

本文介绍了如何使用SQL查询在业务系统中分析用户活跃度，如连续登录天数和存款连续性，通过ETL设计和不同的SQL技巧，如窗口函数和日期差值，实现高效的数据统计和连续性判断。

SQL统计活跃客户（用户数据连续性分析）

场景说明

对于一般数据分析项目，关于客户活跃度(有效性)相关维度数据统计必不可少。根据重要指标的持续性情况反应客户的粘性，衡量用户忠诚度指导业务开展，我们通常会考虑客户数据的一个连续性情况。例如：1、当月用户使用APP的连续登录天数超过等于3天，定义为本月活跃用户；2、客户在某银行存款当年有200天及其以上连续存款余额大于1千元，定义为有效客户；

思路分析

  • 从业务系统ETL设计角度，可以考虑对于此类场景设计对应的信息记录表（客户连续登录次数记录表，字段客户ID，连续登录次数），每天根据用户行为汇总，例如我们根据用户操作日志记录的信息，可以统计出当天的所有登录用户，然后与昨日信息比较（在昨日连续登录次数上+1，昨日汇总中没有则连续登录次数记作1），然后将今日所有用户数据记录到实体表中。
  • 从业务数据分析统计角度，即当前业务表中没有相关直接记录汇总信息表，只能通过现有的业务数据表，或日志表中数据进行分析找到定义的客户用户范围。就比如通过操作日志表，统计当月所有连续登录天数超过3天的用户：
    • 第一步，汇总基础数据，把日志表中每天数据汇总到用户维度，找出每天有登录相关操作的用户，日志结果集就是字段就是客户ID+年月日就行；
    • 第二步，分析汇总数据， 直接思路①：我要分析当天用户是否连续3天，那我把当T，T-1，T-2三天数据都拿出来看看都有的用户就是3天连续登录的，SQL角度相当于我要最低是从日志结果集至少取3次数据进行关联，但是如果规则改为连续4天，就要再加代码一共取4次灵活性很差不易拓展。分析思路②：取当天日期T数据然后和日期小于等于T，但是日期大于T-3的数据进行笛卡尔关联，我们再看如果统计用户不同日期出现次数等于3说明就是连续3天了，这种方式是取两个结果集关联，如果改为其它连续次数只需要改下限制条件中日期范围和判断次数的比较值即可；分析思路③：我们直接把用户+日期，按照日期顺序加上个递增序号，那么通过这个递增序号考虑，假如这个用户连续三天登录了，日期T的序号-2对应那条数据用户对应日期就应该是T-2,否则就不是连续3天了因为序号每天递增和日期递增量是保持一致的。

SQL示例（pg数据库样例）

用户日志明细表

-- 这里我们只记录登录用户的行为日志
CREATE TABLE DWPDATA.cust_log_det
(
   cust_id     VARCHAR(200) -- 用户ID
  ,happen_time TIMESTAMP(0) -- 发生时间
  ,action_desc TEXT         -- 行为描述
)
;
-- 插入测试数据
INSERT INTO DWPDATA.cust_log_det
(
   cust_id       -- 用户ID
  ,happen_time   -- 发生时间
  ,action_desc   -- 行为描述
)
VALUES 
 ('James23','2022-02-01 02:00:00'::TIMESTAMP(0),'登录')
,('James23','2022-02-01 10:00:00'::TIMESTAMP(0),'下载') 
,('James23','2022-02-03 03:00:00'::TIMESTAMP(0),'登录')
,('James23','2022-02-03 06:00:00'::TIMESTAMP(0),'上传')
,('James23','2022-02-04 03:00:00'::TIMESTAMP(0),'登录')
,('James23','2022-02-05 06:00:00'::TIMESTAMP(0),'登录')
,('James06','2022-02-06 03:00:00'::TIMESTAMP(0),'登录')
,('James06','2022-02-07 06:00:00'::TIMESTAMP(0),'登录')
,('James06','2022-02-08 06:00:00'::TIMESTAMP(0),'登录')
,('Paul03','2022-02-06 03:00:00'::TIMESTAMP(0),'登录')
,('Paul03','2022-02-07 06:00:00'::TIMESTAMP(0),'登录')
,('Paul03','2022-02-10 06:00:00'::TIMESTAMP(0),'登录')
;

汇总日志明细到客户日期

我们按照客户加日期年月日方式把日志明细汇总聚合，得到了用户登录日期情况表，可以看到用户哪一天登录，一个用户一个日期一条数据。

 CREATE TABLE DWPDATA.cust_login_date
(
   cust_id     VARCHAR(200) -- 用户ID
  ,login_date  DATE         -- 登录日期
)
;
INSERT INTO DWPDATA.cust_login_date
(
   cust_id       -- 用户ID
  ,login_date    -- 登录日期
)
SELECT 
      cust_id                          -- 用户ID
     ,happen_time::DATE  login_date    -- 登录日期
FROM DWPDATA.cust_log_det
WHERE happen_time >= '2022-02-01 00:00:00'   -- 根据需要限定日期范围 计算口径差异性不同限制数据范围不同 比如有的定义连续必须是本月内，那么上月后两天有登录当月1号有记录也不会算作活跃
  AND happen_time <  '2022-03-01 00:00:00'
GROUP BY 1,2
;
SELECT *
FROM DWPDATA.cust_login_date
ORDER BY 1,2
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-06|
James06|2022-02-07|
James06|2022-02-08|
James23|2022-02-01|
James23|2022-02-03|
James23|2022-02-04|
James23|2022-02-05|
Paul03 |2022-02-06|
Paul03 |2022-02-07|
Paul03 |2022-02-10|

SQL查询活跃用户

直接SQL①：

要分析当天用户是否连续3天，那我把当T，T-1，T-2三天数据都拿出来看看都有的用户就是3天连续登录的，SQL角度相当于我要最低是从日志结果集至少取3次数据进行关联，但是如果规则改为连续4天，就要再加代码一共取4次灵活性很差不易拓展。

SELECT 
   T01.cust_id       -- 用户ID
  ,T01.login_date    -- 登录日期
FROM DWPDATA.cust_login_date T01 
     LEFT JOIN DWPDATA.cust_login_date T02
          ON  T01.cust_id = T02.cust_id
          AND T01.login_date = T02.login_date + 1		  
     LEFT JOIN DWPDATA.cust_login_date T03
          ON  T01.cust_id = T03.cust_id
          AND T01.login_date = T03.login_date + 2
WHERE T02.cust_id IS NOT NULL 
  AND T03.cust_id IS NOT NULL
ORDER BY 1,2
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-08|
James23|2022-02-05|
分析SQL②：

取当天日期T数据然后和日期小于等于T，但是日期大于T-3的数据进行笛卡尔关联，我们再看如果统计用户不同日期出现次数等于3说明就是连续3天了，这种方式是取两个结果集关联，如果改为其它连续次数只需要改下限制条件中日期范围和判断次数的比较值即可；

SELECT 
   T01.cust_id              -- 用户ID
  ,T01.login_date           -- 登录日期
  ,COUNT(T02.login_date)    -- 近三天登录次数
  ,MAX(T02.login_date)      -- 近三天登录过最大日期
  ,MIN(T02.login_date)      -- 近三天登录过最小日期
FROM DWPDATA.cust_login_date T01 
     LEFT JOIN DWPDATA.cust_login_date T02
          ON  T01.cust_id = T02.cust_id
          AND T01.login_date - 2 <= T02.login_date  -- 限定比较范围为近3天
          AND T01.login_date     >= T02.login_date  
GROUP BY 1,2
HAVING COUNT(T02.login_date) = 3 -- 近三天登录次数 为3 说明连续登录3天了
;
-- 查询结果
cust_id|login_date|count|max       |min       |
-------+----------+-----+----------+----------+
James23|2022-02-05|    3|2022-02-05|2022-02-03|
James06|2022-02-08|    3|2022-02-08|2022-02-06|
分析SQL③：

我们直接把用户+日期，按照日期顺序加上个递增序号，那么通过这个递增序号考虑，假如这个用户连续三天登录了，日期T的序号-2对应那条数据用户对应日期就应该是T-2,否则就不是连续3天了因为序号每天递增和日期递增量是保持一致的。

-- 使用 with 相当于建一个临时表，把排序后存储一下，然后可以自身关联，不用取原表两次
WITH VT_CUST_RK_INFO AS 
(
  SELECT 
     T01.cust_id              -- 用户ID
    ,T01.login_date           -- 登录日期
    ,ROW_NUMBER()OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) rk -- 使用开窗函数，每个客户按照登录日期排序，加上序号
  FROM DWPDATA.cust_login_date T01
)
SELECT
      P01.cust_id              -- 用户ID
     ,P01.login_date           -- 登录日期 
FROM VT_CUST_RK_INFO P01
     LEFT JOIN VT_CUST_RK_INFO P02
          ON  P01.cust_id = P02.cust_id
          AND P01.rk - 2  = P02.rk
WHERE P01.login_date - 2 =  P02.login_date  -- 序号和日期的差值一样证明此段是连续的
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-08|
James23|2022-02-05|
分析SQL④：

在分析SQL③利用排序之后序号与日期的相同差值的解决方法后，思考可以直接利用函数LAG、LEAD。这两个函数，允许我们从窗口分区中，根据给定的相对于当前行的前偏移量（ LAG ）或后偏移量（ LEAD ），并返回对应行的值

  • 取当前数据向前偏移2行日期SQL，从新增列 lag_date 既是当前行按照顺序向前找2天对应的期

    SELECT 
    T01.cust_id              -- 用户ID
   ,T01.login_date           -- 登录日期
   ,LAG(T01.login_date,2,NULL)OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) lag_date -- 当前向前偏移2行对应日期，获取不到给NULL
FROM DWPDATA.cust_login_date T01 
ORDER BY 1,2
-- 查询结果
cust_id|login_date|lag_date  |
-------+----------+----------+
James06|2022-02-06|          |
James06|2022-02-07|          |
James06|2022-02-08|2022-02-06|
James23|2022-02-01|          |
James23|2022-02-03|          |
James23|2022-02-04|2022-02-01|
James23|2022-02-05|2022-02-03|
Paul03 |2022-02-06|          |
Paul03 |2022-02-07|          |
Paul03 |2022-02-10|2022-02-06|

  • 如果当前日期与lag_date差值为2说明，日期也是连续的了，所以可以用如下SQL找到活跃用户

SELECT
      P01.cust_id              -- 用户ID
     ,P01.login_date           -- 登录日期
FROM 
    (
    SELECT 
        T01.cust_id              -- 用户ID
       ,T01.login_date           -- 登录日期
       ,LAG(T01.login_date,2,NULL)OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) lag_date -- 当前向前偏移2行对应日期
    FROM DWPDATA.cust_login_date T01
    ) P01   
WHERE P01.login_date - 2 =  P01.lag_date
;
-- 查询结果
cust_id|login_date|
-------+----------+
James06|2022-02-08|
James23|2022-02-05|

总结分析SQL

此类问题核心是思考如何判断连续性，首选将数据转换处理汇总到对应需要判断连续性的维度上（保证一个日期一个客户一条数据），然后通过窗口函数方式直接增加一个有顺序的列，然后通过有顺序增加或者差异规律，去和日期的变化比较，二者一致则证明日期也是连续变化了。所以我们观察到这个规律，其实我们还可以进一步整理SQL,得到这个日期范围内客户连续登录情况比如如下第一段SQL，可以发现如果用户是连续登录的那么登录日期减去序号得到的日期就是开始连续的前天，也就是说获取登录日期减去序号如果是一样证明是在一个连续日期内，比如如下第2段SQL就可以反馈出用户连续登录情况！

 SELECT
      P01.cust_id              -- 用户ID
     ,P01.login_date           -- 登录日期
     ,P01.rk
     ,P01.login_date - P01.rk::INT  "登录日期减去排序"-- 登录日期减去排序  可以理解我们获取如果是连续情况的开始日期
FROM 
    (
    SELECT 
        T01.cust_id              -- 用户ID
       ,T01.login_date           -- 登录日期
       ,ROW_NUMBER()OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) rk -- 使用开窗函数，每个客户按照登录日期排序，加上序号
    FROM DWPDATA.cust_login_date T01
    ) P01
ORDER BY 1,2
-- 查询结果
cust_id|login_date|rk|登录日期减去排序  |
-------+----------+--+----------+
James06|2022-02-06| 1|2022-02-05|
James06|2022-02-07| 2|2022-02-05|
James06|2022-02-08| 3|2022-02-05|
James23|2022-02-01| 1|2022-01-31|
James23|2022-02-03| 2|2022-02-01|
James23|2022-02-04| 3|2022-02-01|
James23|2022-02-05| 4|2022-02-01|
Paul03 |2022-02-06| 1|2022-02-05|
Paul03 |2022-02-07| 2|2022-02-05|
Paul03 |2022-02-10| 3|2022-02-07|

SELECT
      P01.cust_id                   cust_id          -- 用户ID 
     ,P01.login_date - P01.rk::INT  ld_login_date    -- 登录日期减去排序 
     ,MIN(P01.login_date)           begin_login_date -- 登录起日期 
     ,MAX(P01.login_date)           end_login_date   -- 登录止日期
     ,COUNT(1)                      login_cc         -- 登录次数   
FROM 
    (
    SELECT 
        T01.cust_id              -- 用户ID
       ,T01.login_date           -- 登录日期
       ,ROW_NUMBER()OVER(PARTITION BY T01.cust_id ORDER BY T01.login_date) rk -- 使用开窗函数，每个客户按照登录日期排序，加上序号
    FROM DWPDATA.cust_login_date T01
    ) P01
GROUP BY 1,2  
ORDER BY 1,5 DESC 
-- 获取的结果就是所有用户连续登录情况反馈
-- 用户 James06 连续登录过3天，分别是 2022-02-06 2022-02-07 2022-02-08 三天
-- 因此我们在这个结果集上限定 连续登录大于等于 3 的就把所有用户信息，同时如果是想找到哪些天是的话，可以关联表汇总日志明细到客户日期，限定对应客户的登录日期在 [begin_login_date, end_login_date]范围即可，具体SQL这里就不写了
cust_id|ld_login_date|begin_login_date|end_login_date|login_cc|
-------+-------------+----------------+--------------+--------+
James06|   2022-02-05|      2022-02-06|    2022-02-08|       3|
James23|   2022-02-01|      2022-02-03|    2022-02-05|       3|
James23|   2022-01-31|      2022-02-01|    2022-02-01|       1|
Paul03 |   2022-02-05|      2022-02-06|    2022-02-07|       2|
Paul03 |   2022-02-07|      2022-02-10|    2022-02-10|       1|
sql案例分析：统计连续登陆、日活、蚂蚁森林、排名等
.
07-28 3915
这样处理的结果是，相同的 group by key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，剩余的能量全部用来领取“p002-沙柳”。问题：查询user_low_carbon表中每日流水记录，条件为：用户在2017年，连续三天（或以上）的天数里，每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
SQL连续
Java/Python大数据随笔
12-15 1170
连续问题是实际数据开发中比较常见的场景。例如，统计用户连续活跃天数等SQL如何解决连续问题？本文主要介绍连续性问题，重点以常见的连续活跃场景为例，抽象出通用的连续问题解决方案。连续问题主要分为静态连续问题和动态连续问题两类本文尝试独辟蹊径，强调通过灵活的、发散性的数据处理思维，就可以用最基础的语法，解决复杂的数据场景。文章涉及的SQL语句使用了Hive SQL的基础语法和部分高级语法特性。
面试题 Hive-SQL查询连续活跃登录用户思路详解_sql查询连续登录两天的用户(1)
2401_84164503的博客
05-05 693
连续活跃登陆的用户指至少连续2天都活跃登录的用户解决类似场景的问题。
SQL拿捏：活跃用户数统计案例类型SQL 全网拿捏同款SQL
m0_49303490的博客
05-03 976
活跃用户数统计
sql计算连续三天活跃用户
weixin_43859562的博客
11-29 2738
1.筛选出七天内数据,group by id,date 2.rank() over() as rk 根据id排序id，date 3.date -rk 为同一个数，select date -rk .group by id date-rk having count(*) 4.group id 具体代码： select t4.id from ( select id, date_add(t2.dt-t2.rk) as rk2 from ( select t1.id, t1.dt, rank() over(p.
SQL统计连续登陆3天的用户(连续活跃超3天用户)
Taerge0110的博客
11-21 9509
【代码】SQL统计连续登陆3天的用户(连续活跃超3天用户)
SQL统计分析
weixin_33881753的博客
11-26 262
2019独角兽企业重金招聘Python工程师标准>>> ...
数据分析—用户行为数据分析
m0_74091312的博客
07-26 1267
一般价值用户（R高F高M低）：用户处于购买活跃期，感兴趣的商品多，购买次数少 → 针对该部分客户，目的就是要让他们购买，所以可以和运营部门沟通，降低利润，给出有力折扣促进购买，进而转为重要价值用户。重要价值用户（R高F高M高）维护：用户处于购买活跃期，感兴趣的商品多，购买次数也多 → 针对兴趣产品，保持一定频率的触达，持续维护。
数据分析师笔试试卷三：Excel、SQL、Python、算法
rubyw的博客
07-15 1418
例如，有一组学生的数学成绩（连续变量）和性别（分类变量），要研究性别对数学成绩的影响，可以使用 t 检验；一组学生，其中一部分是男生，一部分是女生，同时有他们的数学考试成绩。在控制每天学习时间这个变量的影响下，计算数学成绩和语文成绩之间的偏相关系数，以更准确地了解它们之间的直接关系。：当二分变量是人为划分的（例如，考试成绩 60 分以上为及格，60 分以下为不及格），与连续变量之间的相关性可用二列相关系数。例如，在研究教育程度与收入的关系时，控制工作年限这个变量的影响，计算出的就是偏相关系数。
Hive SQL进阶：掌握这些SQL查询技巧，让你的数据报告更加精准和高效！
最新发布
数据与算法架构提升之路专栏
08-05 1288
这份文档提供了一系列的SQL查询示例，用于解决数据分析中的常见问题，如用户活跃度、留存率、销售统计等。通过这些示例，用户可以学习如何使用SQL进行高效的数据查询和分析。
连续活跃的用户
weixin_43852674的博客
05-28 406
题目1 现在有一张用户活跃全量表，列有id，活跃日期（全部），分区 如何求得最大连续活跃天数的ID way1 with t1 as (select id,row_number() over(partition by id order by lastactivetime asc) as ranks, lastactivetime from table) select id,ate_sub(lastactivetime,ranks) as difftime, min(lastactiveimte) as m
统计分析 (sql)
起风了
12-31 1285
http://blog.csdn.net/lifetragedy/article/details/9935699 目录(?)[+] 写了这么多JAVA基础，来点SQL吧！ 一般面试时考SQL，主要就是考你“统计分析”这一块，下面我们来看面试官经常采用的手段。 由4张简单的不能再简单的表，演变出50道SQL 哈哈哈
网站用户活跃度统计服务器,活跃用户统计规则
weixin_39975122的博客
08-10 1856
1. 活跃用户1.1. 基础说明1.1.1. 指标定义活跃用户指的是当前统计时间周期内启动过应用并浏览了页面的设备，MTA通过用户/程序在不同场景下是否上报日志作为活跃的判断标准。注意：活跃用户默认只计算前台使用的用户为活跃用户，即上报页面日志和前后台切换日志才会计算。1.1.2. 更新周期日活跃数据实时更新，周活跃(WAU)次日更新，月活跃(MAU)次日更新。注意：APP的某些用户可能存在因为网...
统计活跃用户
weixin_30914981的博客
07-07 888
客户提出要登录系统的活跃用户，定义如下：an active definition can be one that at least download 5 times in a week. 构思如下：1.首先要循环每个7天的时间段2.找出时间段内登陆超过5次的3.循环插入临时表4.在临时表里group by 统计用户--创建临时表开始if exists (select * from dbo.sy...
SQL题-连续三天活跃用户统计
Taker256的博客
11-15 372
- 4- 根据user_id 和 new_dt进行分组,求count(new_dt) >=n,过滤出新的日期相同的用户id。-- 有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。-- 2- 使用新增加的列与原来的表进行join on dt=tomorrow。-- 1- 给原表增加一列:date_add(dt,1) tomorrow。-- 需求有日志如下，请写出代码求得所有用户和活跃用户的总数及平均年龄。-- 3- 通过当前的dt 减去 rn 得到一个新的日期 new_dt。
SQL 排名函数面试宝典
qq_32572085的博客
04-13 465
本文转载自sql 四大排名函数---（ROW_NUMBER、RANK、DENSE_RANK、NTILE）简介 1.ROW_NUMBER() 定义：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询， 比如查询前10个 查询10-100个学生。 实例： 1.1对学生成绩排序 这里number就...
【SQL】1141. 查询近30天活跃用户数（常规写法；date_add()）
Rudy的博客
03-20 1076
where activity_date between date_add('2019-07-27', interval -29 day) and '2019-07-27'
SQL 经典面试题：统计最近七天连续三天活跃的用户
SUNK2014的博客
10-10 4569
给定 mid，dt 的用户登录记录表，查找最近 7 天内连续 3 天活跃的用户 id，实现思路：获取最近 7 天的用户登录记录数据（在 where 中限定），对数据进行 rank 排序...
Redis-统计活跃用户
liao1990的专栏
07-24 807
Bitmap(即Bitset) Bitmap是一串连续的2进制数字（0或1），每一位所在的位置为偏移(offset)，在bitmap上可执行AND,OR,XOR以及其它位操作。 package test.jedis; import java.text.SimpleDateFormat; import java.util.BitSet; import java.util.Date; im...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值