在互联网行业，会有一些常用的数据指标和常见的数据分析方法包括：事件分析，留存分析，漏斗分析，分布分析，路径分析，LTV分析等。在做数据分析，数据报表时，通常用SQL来计算这些数据指标或者实现这些数据分析方法。

下面模拟一个简单的用户行为事件表。基于这个事件表，用SQL来实现常见的数据指标和数据分析方法。

# 用户行为事件表
CREATE TABLE Events (
    user_id VARCHAR(20) NOT NULL DEFAULT '',
    event_name VARCHAR(20) NOT NULL DEFAULT '',
    event_time VARCHAR(20) NOT NULL DEFAULT ''
);

事件量，用户量

一段时间内的事件量。在真实的大数据分析场景，因为数据量比较大，通常一定是需要限定时间范围的。而且时间字段通常用来作为分区表的分区字段，比如：按天分区。

一段时间内的用户量就是总事件量按用户做去重处理。

# 一段时间内的总事件量
SELECT count(event_name)
FROM Events
WHERE event_time >= '2022-01-01 00:00:00'
    AND event_time < '2022-01-08 00:00:00';

# 一段时间内的总用户量
SELECT count(DISTINCT user_id)
FROM Events
WHERE event_time >= '2022-01-01 00:00:00'
    AND event_time < '2022-01-08 00:00:00';

DAU，UV，PV

数据分析中经常需要按天展示DAU，在Web侧通常称为UV。或者按天展示PV，APP侧不太讲PV这个概念。

# 一段时间内特定事件的按天DAU
SELECT date(event_time) AS day, COUNT(DISTINCT user_id)
FROM Events
WHERE event_name = "play"
    AND event_time >= "2022-01-01 00:00:00"
    AND event_time < "2022-01-08 00:00:00"
GROUP BY date(event_time);

# 一段时间内特定事件的按天PV或者说事件量
SELECT date(event_time) AS day, COUNT(event_name)
FROM Events
WHERE event_name = "play"
    AND event_time >= "2022-01-01 00:00:00"
    AND event_time < "2022-01-08 00:00:00"
GROUP BY date(event_time);

以上SQL直接通过GROUP BY按天展示数据的一个问题是：如果某一天没有数据，那么在SQL查询的结果中就不会出现该天的数据。但因为最终的数据是要拿来做展示的，所以如果某天没有数据，也需要增加该日期的数据行，用0来补充。具体到项目中，如果用来展示数据的 BI 能够兼容缺失日期的数据当然最好。如果 BI 无法兼容，用于请求数据库或者数仓的后端服务也可以兼容该逻辑。如果后端服务也不愿意兼容，最后只能还是利用SQL实现，不过SQL实现这个需求并不简单且不直观。

SQL实现该需求需要分两步进行：

生成按行的连续日期。根据所用数据库或者数仓的不同，应该有不同的实现方式。
按行的连续日期和原数据做LEFT JOIN。利用LEFT JOIN时右表某行不存在则用NULL替代的特性，将NULL替换为0即可。

# 给定时间范围，生成按行的连续日期
SELECT
    @i := @i + 1 AS 'NO',
    DATE(
    DATE_ADD('2022-01-01',INTERVAL @i DAY)) as date    # 开始时间
FROM
    Events,                                            # 随便给表，需要保证表的行数比需要的天数多
    (SELECT @i := - 1) t                               # 设置初始值为-1的变量i
WHERE
    @i < DATEDIFF('2022-01-10','2022-01-01')           # 结束时间，开始时间

以时间范围内的按行连续日期表为基础，结合之前查询DAU的数据，进行联表查询，获得每个日期的DAU数据。观察一下数据查询结果中，2022-01-08~2022-01-10三天的DAU数据均为0。

SELECT
    t1.day, ifnull(t2.DAU, 0) as DAU
FROM
    (SELECT
        @i := @i + 1 AS NO,
        DATE(
        DATE_ADD( '2022-01-01', INTERVAL @i DAY )) AS day     # 开始时间
    FROM
        Events,
        ( SELECT @i := - 1 ) t
    WHERE
    @i < DATEDIFF( '2022-01-10', '2022-01-01' )) t1           # 结束时间，开始时间
LEFT JOIN
    (SELECT 
         date(event_time) AS day, COUNT(DISTINCT user_id) as DAU
       FROM Events
     WHERE 
        event_time >= "2022-01-01 00:00:00"
        AND event_time < "2022-01-08 00:00:00"
     GROUP BY date(event_time)) t2 
ON t1.day = t2.day

day	DAU
2022-01-01	3
2022-01-02	3
2022-01-03	3
2022-01-04	1
2022-01-05	2
2022-01-06	2
2022-01-07	2
2022-01-08	0
2022-01-09	0
2022-01-10	0

留存分析，留存率

A日期的活跃用户在B日期的留存率计算可以分为以下步骤：

计算A日期活跃的用户ID
计算A日期活跃的用户哪些在B日期也活跃过
将以上两步的计算结果做JOIN，算出留存率

对于复杂的SQL，可读性通常很差。可以利用WITH AS来相对提升可读性。t1用来计算起始天活跃的用户，t2用来计算留存天活跃的用户。用t1和t2做LEFT JOIN后，t1中有t2没有的会是NULL，t2有t1没有的不会出现在结果中。

WITH t1 AS (                                              # 计算起始天的活跃用户
        SELECT DISTINCT user_id
        FROM Events
        WHERE date(event_time) = '2022-01-01'
    ), 
    t2 AS (                                                 # 计算留存天的活跃用户
        SELECT DISTINCT user_id
        FROM Events
        WHERE date(event_time) = '2022-01-02'
    )

# LEFT JOIN后t2中没有留存的会是NULL，COUNT不会包含NULL
SELECT COUNT(t2.user_id) / COUNT(t1.user_id) AS retention
FROM t1
    LEFT JOIN t2 ON t1.user_id = t2.user_id;

以上SQL只是求得了某一日期的次日留存率。但在数据分析中，通常我们希望看到的是如下的留存曲线。留存曲线描绘了特定日期之后一段时间每天的留存数据。我们需要用SQL来实现绘制该留存曲线所需要的数据。

首先需要同时对用户ID和日期做GROUP BY，其次用LEFT JOIN求时间差。

以下SQL展示了如何求得时间差diff这一中间结果：

WITH t1 AS (
        SELECT user_id, date(event_time) AS day
        FROM Events
        WHERE date(event_time) = '2022-01-01'
        GROUP BY user_id, date(event_time)
    ), 
    t2 AS (
        SELECT user_id, date(event_time) AS day
        FROM Events
        WHERE event_time >= "2022-01-01 00:00:00"
            AND event_time < "2022-01-08 00:00:00"
        GROUP BY user_id, date(event_time)
    )

SELECT t1.user_id, t1.day AS start_day, t2.day AS retent_day, datediff(t2.day, t1.day) AS diff
FROM t1
    LEFT JOIN t2 ON t1.user_id = t2.user_id;

user_id	start_day	retent_day	diff
01	2022-01-01	2022-01-07	6
01	2022-01-01	2022-01-05	4
01	2022-01-01	2022-01-03	2
01	2022-01-01	2022-01-02	1
01	2022-01-01	2022-01-01	0
03	2022-01-01	2022-01-07	6
03	2022-01-01	2022-01-06	5
03	2022-01-01	2022-01-05	4
03	2022-01-01	2022-01-01	0
04	2022-01-01	2022-01-03	2
04	2022-01-01	2022-01-02	1
04	2022-01-01	2022-01-01	0

以上结果中retent_day表示该用户有留存的日期，diff就是留存日期到起始日期的差值，1就是次日留存，2就2日留存，3就是3日留存，以此类推。要求留存人数就只需要按照diff进行GROUP BY求COUNT。如果要求留存率就还要增加一步，这里就不赘述了。而且这里同样存在某天留存人数为0，则结果中不存在该天的问题，也不多说。

SELECT datediff(t2.day, t1.day) AS diff, COUNT(*)
FROM t1
    LEFT JOIN t2 ON t1.user_id = t2.user_id
GROUP BY diff
ORDER BY diff;

diff	count(*)
0	3
1	2
2	2
4	2
5	1
6	2

漏斗分析，转化率

漏斗分析中的转化率其实和留存率有相似之处。留存率可以看成是相对于日期的转化率，而漏斗分析中的转化率可以看成是相对于具体事件的转化率。但是通常在漏斗分析时，我们会对转化的时间做出限制，比如转化必须发生在1小时之内。

选取Events表中的login、play、like三个事件做转化分析。用Events表和自身进行JOIN，筛选其中间隔小于1小时的两个不相同的事件。每个转化，同一个用户只记录一次。

SELECT event1.event_name AS event1, 
       event2.event_name AS event2, 
       COUNT(DISTINCT event1.user_id) AS num_conversions
FROM Events event1
    INNER JOIN Events event2
    ON event1.event_name <> event2.event_name
        AND event1.user_id = event2.user_id
        AND TIMESTAMPDIFF(SECOND, event1.event_time, event2.event_time) < 3600
        AND TIMESTAMPDIFF(SECOND, event1.event_time, event2.event_time) > 0
WHERE event1.event_name IN ('login', 'play', 'like')
    AND event2.event_name IN ('play', 'like')
GROUP BY event1.event_name, event2.event_name

event1	event2	num_conversions
login	like	3
login	play	4
play	like	3

以上可以看出login到like发生了3人次的转化，login到play发生了4次转化，play到like发生了3次转化。如果最终要求出转化率，还要先把起始事件的人数求出。对以上SQL做一点改动。

SELECT
  event1.event_name AS event1,
  event2.event_name AS event2,
  COUNT(DISTINCT event1.user_id) AS num_conversions,
  CAST(COUNT(DISTINCT event1.user_id) AS FLOAT) / (SELECT COUNT(DISTINCT user_id) FROM Events AS e WHERE e.event_name = event1.event_name) AS conversion_rate
FROM Events AS event1
INNER JOIN Events AS event2
ON event1.event_name <> event2.event_name
AND event1.user_id = event2.user_id
AND TIMESTAMPDIFF(SECOND, event1.event_time, event2.event_time) < 3600 and TIMESTAMPDIFF(SECOND, event1.event_time, event2.event_time) > 0
where event1.event_name in ('login', 'play', 'like') and event2.event_name in ('play', 'like')
GROUP BY event1.event_name, event2.event_name

event1	event2	num_conversions	conversion_rate
login	like	3	0.75
login	play	4	1
play	like	3	0.75

用户分群

在实际数据分析中，我们通常需要按照一定的用户属性或者行为特征对用户进行分群分析。一道经典的SQL面试题：求连续登录3天的用户ID，就是一个典型的用户分群案例。用户连续登录N天可以反应用户某种程度的活跃。用户连续N次做某件事情也会经常被应用于运营活动中。

以下就尝试用SQL求解连续登录3天的用户ID：

将事件时间event_time转换为事件天event_day。
利用窗口函数，按用户ID分组，按照事件天event_day排序。给每一天打上一个排序后的行号rn。
将事件天event_day减去对应的行号rn得到一个flag_day。如果是连续登录的，则flag_day应该是同一天。
按照用户ID和flag_day分组，如果同一天出现大于等3，则表示该用户从start_day开始连续登录了3天及以上。

WITH t1 AS ( # 将事件时间event_time转换为事件天event_day
        SELECT user_id, date(event_time) AS event_day
        FROM Events
        GROUP BY user_id, date(event_time)
    ), 
    t2 AS ( # 利用窗口函数，按用户ID分组，按照事件天event_day排序。给每一天打上一个排序后的行号rn
        SELECT user_id, event_day, row_number() OVER (PARTITION BY user_id ORDER BY event_day) AS rn
        FROM t1
    ), 
    t3 AS ( # 将事件天event_day减去对应的行号rn得到一个flag_day。如果是连续登录的，则flag_day应该是同一天
        SELECT *, date_sub(event_day, INTERVAL rn DAY) AS flag_day
        FROM t2
    )

# 按照用户ID和flag_day分组，如果同一天出现大于等3，则表示该用户从start_day开始连续登录了3天及以上
SELECT date_add(flag_day, INTERVAL 1 DAY) AS start_day, user_id
FROM t3
GROUP BY user_id, flag_day
HAVING COUNT(user_id) >= 3;

start_day	user_id
2022-01-01	01
2022-01-02	02
2022-01-04	03
2022-01-01	04

附

以上内容所需的数据库表结构和数据库表数据如下：

CREATE TABLE Events(
user_id VARCHAR(20) NOT NULL DEFAULT '',
event_name VARCHAR(20) NOT NULL DEFAULT '',
event_time VARCHAR(20) NOT NULL DEFAULT ''
);

insert into Events values('01', 'login', '2022-01-01 11:50:31');
insert into Events values('01', 'click', '2022-01-01 11:51:10');
insert into Events values('01', 'view', '2022-01-01 11:55:59');
insert into Events values('01', 'play', '2022-01-01 12:01:03');
insert into Events values('01', 'like', '2022-01-01 12:06:42');

insert into Events values('01', 'login', '2022-01-02 15:50:31');
insert into Events values('01', 'play', '2022-01-02 16:01:03');
insert into Events values('01', 'like', '2022-01-02 16:06:42');

insert into Events values('01', 'login', '2022-01-03 21:50:31');
insert into Events values('01', 'play', '2022-01-03 22:01:03');

insert into Events values('01', 'login', '2022-01-05 08:50:31');
insert into Events values('01', 'view', '2022-01-05 08:55:59');
insert into Events values('01', 'play', '2022-01-05 09:01:03');
insert into Events values('01', 'like', '2022-01-05 09:06:42');
insert into Events values('01', 'logout', '2022-01-05 09:51:10');

insert into Events values('01', 'login', '2022-01-07 22:50:31');

insert into Events values('02', 'login', '2022-01-02 09:10:31');
insert into Events values('02', 'play', '2022-01-02 09:15:03');
insert into Events values('02', 'like', '2022-01-02 09:20:55');

insert into Events values('02', 'login', '2022-01-03 13:15:31');
insert into Events values('02', 'play', '2022-01-03 13:20:03');
insert into Events values('02', 'logout', '2022-01-03 13:25:55');

insert into Events values('02', 'login', '2022-01-04 01:50:31');
insert into Events values('02', 'click', '2022-01-04 02:51:10');
insert into Events values('02', 'view', '2022-01-04 02:55:59');
insert into Events values('02', 'play', '2022-01-04 03:01:03');
insert into Events values('02', 'like', '2022-01-04 03:06:42');

insert into Events values('02', 'login', '2022-01-06 18:50:31');
insert into Events values('02', 'view', '2022-01-06 18:55:01');
insert into Events values('02', 'like', '2022-01-06 18:59:42');

insert into Events values('03', 'login', '2022-01-01 16:23:07');

insert into Events values('03', 'login', '2022-01-05 23:50:31');

insert into Events values('03', 'play', '2022-01-06 00:01:03');
insert into Events values('03', 'like', '2022-01-06 00:06:42');

insert into Events values('03', 'login', '2022-01-07 13:08:31');
insert into Events values('03', 'click', '2022-01-07 13:10:10');
insert into Events values('03', 'view', '2022-01-07 13:47:59');

insert into Events values('04', 'login', '2022-01-01 19:43:31');
insert into Events values('04', 'play', '2022-01-01 19:47:03');
insert into Events values('04', 'logout', '2022-01-01 19:53:55');

insert into Events values('04', 'login', '2022-01-02 18:43:31');
insert into Events values('04', 'play', '2022-01-02 18:47:03');
insert into Events values('04', 'logout', '2022-01-02 18:53:55');

insert into Events values('04', 'login', '2022-01-03 17:43:31');
insert into Events values('04', 'play', '2022-01-03 17:47:03');
insert into Events values('04', 'logout', '2022-01-03 17:53:55');

SQL案例

事件量，用户量

DAU，UV，PV

留存分析，留存率

漏斗分析，转化率

用户分群

附

results matching ""

No results matching ""