SQL进阶实战技巧：如何构建用户行为转移概率矩阵，深入洞察会话内活动流转？
目录
1 场景描述
1.1 用户行为转移概率矩阵概念
1.2 用户行为转移概率矩阵构建方法
（1） 数据收集
（2）定义状态
（3）数据预处理
（4）会话划分
（5）构建状态序列
（6）计算转移频率
（7）构建转移概率矩阵
a. 初始化矩阵
b. 填充矩阵
c. 计算概率
1.3 需求分析
1 数据准备
2 问题分析
3 小结
往期回顾
~~SQL进阶实战技巧系列~~
1 场景描述
1.1 用户行为转移概率矩阵概念
用户行为转移概率矩阵是一个用于描述用户在一系列行为或状态之间转移的概率的矩阵。在用户行为分析中，这个矩阵可以帮助我们理解用户的行为模式，预测用户的下一步行为，以及进行用户分群等。
以下是用户行为转移概率矩阵的几个关键点：
-
状态：在用户行为转移概率矩阵中，每个状态代表用户的一种行为，例如浏览网页、添加商品到购物车、下订单、评论产品等。
-
转移概率：矩阵中的元素表示从一个状态转移到另一个状态的概率。例如，如果用户当前正在浏览网页，那么转移到“添加商品到购物车”的概率是多少。
-
行和列：矩阵的每一行代表当前状态，每一列代表下一个可能的状态。矩阵的主对角线上的元素通常表示用户保持在同一状态的概率。
-
行和为1：由于用户必须转移到某个状态，所以矩阵每一行的元素之和必须等于1。
以下是一个简化的用户行为转移概率矩阵的例子：
| 浏览首页 | 浏览商品 | 加入购物车 | 结算 | 完成 purchase | |
|---|---|---|---|---|---|
| 浏览首页 | 0.7 | 0.2 | 0.1 | 0 | 0 |
| 浏览商品 | 0.3 | 0.5 | 0.2 | 0 | 0 |
| 加入购物车 | 0 | 0.1 | 0.6 | 0.3 | 0 |
| 结算 | 0 | 0 | 0 | 0.7 | 0.3 |
| 完成 purchase | 0 | 0 | 0 | 0 | 1 |
在这个例子中，如果一个用户当前正在浏览商品（第二行），那么有50%的概率他们将继续浏览商品，20%的概率他们会加入购物车，而30%的概率他们会返回浏览首页。
用户行为转移概率矩阵可以用于多种分析，例如：
- 用户路径分析：识别用户最常见的浏览和购买路径。
- 行为预测：预测用户在未来某个时间点的可能行为。
- 用户分群：根据用户的行为模式将用户分为不同的群体。
- 优化营销策略：通过分析用户行为转移概率，优化营销活动以提高转化率。
1.2 用户行为转移概率矩阵构建方法
（1） 数据收集
首先，你需要收集用户行为数据。这些数据通常来自于用户在网站、应用或其他平台上的互动记录。数据应包括用户ID、行为类型、行为发生的时间戳等信息。
（2）定义状态
确定你想要分析的用户行为，并将它们定义为状态。例如，状态可以是“浏览首页”、“浏览商品”、“添加到购物车”、“结算”、“完成购买”等。
（3）数据预处理
对收集到的数据进行预处理，确保其格式一致，时间顺序正确，并且只包含分析所需的用户行为。
（4）会话划分
根据时间间隔或其他逻辑将用户的行为序列划分为会话（session）。一个会话可以是用户的一次登录期间的所有行为，或者是一段时间内的连续行为。
（5）构建状态序列
对于每个会话，将用户的行为按时间顺序排列，形成状态序列。
（6）计算转移频率
统计所有会话中从状态i转移到状态j的频率。这可以通过遍历所有状态序列并计数来实现。
（7）构建转移概率矩阵
使用转移频率来构建转移概率矩阵。对于每个状态i，计算转移到每个可能状态j的概率，即：
以下是构建转移概率矩阵的具体步骤：
a. 初始化矩阵
创建一个矩阵，行和列对应于定义的状态。初始时，矩阵中的所有值设置为0。
b. 填充矩阵
遍历所有状态序列，对于序列中的每一对连续状态（i, j），增加矩阵中对应位置的计数。
c. 计算概率
对于矩阵中的每一行，将每个元素除以该行的总和，从而得到转移概率。
1.3 需求分析
需求：分析用户在每个会话内的行为序列的转移概率矩阵
假设有名为user_behavior_log的用户行为日志表，包含以下字段：
字段名 数据类型 描述
user_id INT 用户 ID
behavior STRING 用户行为，例如view, click, purchase
timestamp BIGINT 行为发生的时间戳
user_group STRING 用户分组，例如group_a, group_b示例数据，例如：
user_id behavior timestamp user_group
1 view 1678886400 group_a
1 click 1678886460 group_a
2 view 1678886520 group_b
1 purchase 1678886580 group_a
2 view 1678886640 group_b
2 click 1678886700 group_b
3 view 1678886760 group_a
3 click 1678886820 group_a
3 view 1678886880 group_a
1 view 1678887000 group_a
1 click 1678887060 group_a1 数据准备
CREATE TABLE user_behavior_log (
user_id INT,
behavior STRING,
timestamp BIGINT,
user_group STRING
); DROP TABLE user_behavior_log;
create TABLE user_behavior_log as
(SELECT stack(11,
1, 'view', 1678886400, 'group_a',
1, 'click', 1678886460, 'group_a',
2, 'view', 1678886520, 'group_b',
1, 'purchase', 1678886580, 'group_a',
2, 'view', 1678886640, 'group_b',
2, 'click', 1678886700, 'group_b',
3, 'view', 1678886760, 'group_a',
3, 'click', 1678886820, 'group_a',
3, 'view', 1678886880, 'group_a',
1, 'view', 1678888800, 'group_a',
1, 'click', 1678889060, 'group_a'
) AS (user_id, behavior, `timestamp`, user_group));2 问题分析
步骤1：会话划分
WITH user_sessions AS (select user_id,
behavior,
`timestamp`,
user_group,
sum(diff) over (partition by user_id order by `timestamp`) session_id
from (SELECT user_id,
behavior,
`timestamp`,
user_group,
CASE
WHEN (`timestamp` - LAG(`timestamp`, 1, 0)
OVER (PARTITION BY user_id ORDER BY `timestamp`)) >
1800 THEN 1
ELSE 0 END diff
FROM user_behavior_log) t
)
select *
from user_sessions;
步骤2：构建行为转移对
WITH user_sessions AS (select user_id,
behavior,
`timestamp`,
user_group,
sum(diff) over (partition by user_id order by `timestamp`) session_id
from (SELECT user_id,
behavior,
`timestamp`,
user_group,
CASE
WHEN (`timestamp` - LAG(`timestamp`, 1, 0)
OVER (PARTITION BY user_id ORDER BY `timestamp`)) >
1800 THEN 1
ELSE 0 END diff
FROM user_behavior_log) t
)
SELECT
user_id,
session_id,
behavior AS from_behavior,
LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behavior
FROM user_sessions
步骤3：计算转移对次数
WITH user_sessions AS (select user_id,
behavior,
`timestamp`,
user_group,
sum(diff) over (partition by user_id order by `timestamp`) session_id
from (SELECT user_id,
behavior,
`timestamp`,
user_group,
CASE
WHEN (`timestamp` - LAG(`timestamp`, 1, 0)
OVER (PARTITION BY user_id ORDER BY `timestamp`)) >
1800 THEN 1
ELSE 0 END diff
FROM user_behavior_log) t
)
, transition_behav_pairs as
(
SELECT
user_id,
session_id,
behavior AS from_behavior,
LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behavior
FROM user_sessions
)
select user_id
,from_behavior
,to_behavior
, count(*) transition_cnt
from transition_behav_pairs
WHERE to_behavior IS NOT NULL
group by user_id,from_behavior,to_behavior
步骤 4：计算转移概率
现在我们将计算转移概率，即每个行为对转移次数除以该用户该行为的总转移次数。
WITH user_sessions AS (select user_id,
behavior,
`timestamp`,
user_group,
sum(diff) over (partition by user_id order by `timestamp`) session_id
from (SELECT user_id,
behavior,
`timestamp`,
user_group,
CASE
WHEN (`timestamp` - LAG(`timestamp`, 1, `timestamp`)
OVER (PARTITION BY user_id ORDER BY `timestamp`)) >
1800 THEN 1
ELSE 0 END diff
FROM user_behavior_log) t
)
, transition_behav_pairs as
(
SELECT
user_id,
session_id,
behavior AS from_behavior,
LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behavior
FROM user_sessions
),
transition_behav_pairs_cnt as
(select user_id
, from_behavior
, to_behavior
, count(*) transition_cnt
from transition_behav_pairs
WHERE to_behavior IS NOT NULL
group by user_id, from_behavior, to_behavior
)
select user_id
, from_behavior
, to_behavior
, transition_cnt
, sum(transition_cnt) over(partition by user_id,from_behavior) from_total_transitions
, transition_cnt * 1.0 / sum(transition_cnt) over(partition by user_id,from_behavior) transition_probability
from transition_behav_pairs_cnt 
步骤5：构建转移概率矩阵
WITH user_sessions AS (select user_id,
behavior,
`timestamp`,
user_group,
sum(diff) over (partition by user_id order by `timestamp`) session_id
from (SELECT user_id,
behavior,
`timestamp`,
user_group,
CASE
WHEN (`timestamp` - LAG(`timestamp`, 1, `timestamp`)
OVER (PARTITION BY user_id ORDER BY `timestamp`)) >
1800 THEN 1
ELSE 0 END diff
FROM user_behavior_log) t
)
, transition_behav_pairs as
(
SELECT
user_id,
session_id,
behavior AS from_behavior,
LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behavior
FROM user_sessions
),
transition_behav_pairs_cnt as
(select user_id
, from_behavior
, to_behavior
, count(*) transition_cnt
from transition_behav_pairs
WHERE to_behavior IS NOT NULL
group by user_id, from_behavior, to_behavior
)
, transition_probabilities AS (
select user_id
, from_behavior
, to_behavior
, transition_cnt
, sum(transition_cnt) over (partition by user_id
, from_behavior) from_total_transitions
, cast(transition_cnt * 1.0 / sum(transition_cnt) over (partition by user_id
, from_behavior) as decimal(18,2)) transition_probability
from transition_behav_pairs_cnt
)
SELECT
user_id,
collect_list(named_struct('from_behavior', from_behavior, 'to_behavior', to_behavior, 'probability', transition_probability)) AS transition_matrix
FROM
transition_probabilities
GROUP BY
user_id#,user_id,transition_matrix
1,1,"[{""from_behavior"":""click"",""to_behavior"":""purchase"",""probability"":1},{""from_behavior"":""view"",""to_behavior"":""click"",""probability"":1}]"
2,2,"[{""from_behavior"":""view"",""to_behavior"":""click"",""probability"":0.5},{""from_behavior"":""view"",""to_behavior"":""view"",""probability"":0.5}]"
3,3,"[{""from_behavior"":""click"",""to_behavior"":""view"",""probability"":1},{""from_behavior"":""view"",""to_behavior"":""click"",""probability"":1}]"
3 小结
本文利用SQL语言分析了一种用户在会话内的行为转移概率矩阵问题，用户行为转移概率矩阵揭示了用户在一系列操作中的转换趋势，反映了不同行为之间的流转概率。它有助于理解用户行为模式，预测用户下一步行动，优化产品流程，提升用户体验，指导个性化推荐，以及评估用户留存和转化策略的有效性。
往期回顾
数仓建模：如何评估数仓模型的复用性？
数仓建模：如何构建主题宽表模型？
数仓建模：一种动态字段表模型设计方法与应用
~~SQL进阶实战技巧系列~~
SQL很简单，可你却写不好？每天一点点，收获不止一点点_sql断点-CSDN博客文章浏览阅读1.3k次，点赞54次，收藏19次。在写本文之前，我需要跟大家探讨以下几个话题。SQL进阶技巧：车辆班次问题分析SQL 进阶技巧：断点重分组应用求连续段的最后一个数及每段的个数【拼多多面试题】SQL进阶技巧-：字符串时间序列分析法应用之用户连续签到天数及历史最大连续签到天数问题【腾讯面试题】SQL进阶技巧：断点重分组算法应用之用户订单日期间隔异常问题分析SQL进阶技巧：如何对连续多条记录进行合并？【GroingIO 面试题】SQL进阶技巧：断点重分组算法应用之相邻时间间隔跳变问题分析。_sql断点https://flyingsql.blog.csdn.net/article/details/143609283
https://flyingsql.blog.csdn.net/article/details/143609283