SQL进阶实战技巧：如何构建用户行为转移概率矩阵，深入洞察会话内活动流转？

news/2025/1/30 1:28:21/

目录

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. 状态：在用户行为转移概率矩阵中，每个状态代表用户的一种行为，例如浏览网页、添加商品到购物车、下订单、评论产品等。

  2. 转移概率：矩阵中的元素表示从一个状态转移到另一个状态的概率。例如，如果用户当前正在浏览网页，那么转移到“添加商品到购物车”的概率是多少。

  3. 行和列：矩阵的每一行代表当前状态，每一列代表下一个可能的状态。矩阵的主对角线上的元素通常表示用户保持在同一状态的概率。

  4. 行和为1：由于用户必须转移到某个状态，所以矩阵每一行的元素之和必须等于1。

以下是一个简化的用户行为转移概率矩阵的例子：

浏览首页浏览商品加入购物车结算完成 purchase
浏览首页0.70.20.100
浏览商品0.30.50.200
加入购物车00.10.60.30
结算0000.70.3
完成 purchase00001

在这个例子中，如果一个用户当前正在浏览商品（第二行），那么有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 需求分析

需求：分析用户在每个会话内的行为序列的转移概率矩阵

sql">假设有名为user_behavior_log的用户行为日志表，包含以下字段：字段名	数据类型	描述
user_id	INT	用户 ID
behavior	STRING	用户行为，例如view, click, purchase
timestamp	BIGINT	行为发生的时间戳
user_group	STRING	用户分组，例如group_a, group_b
sql">示例数据，例如：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_a

1 数据准备

sql">CREATE TABLE user_behavior_log (user_id INT,behavior STRING,timestamp BIGINT,user_group STRING
);
sql"> 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：会话划分

sql">WITH user_sessions AS (select user_id,behavior,`timestamp`,user_group,sum(diff) over (partition by user_id order by `timestamp`) session_idfrom (SELECT user_id,behavior,`timestamp`,user_group,CASEWHEN (`timestamp` - LAG(`timestamp`, 1, 0)OVER (PARTITION BY user_id ORDER BY `timestamp`)) >1800 THEN 1ELSE 0 END diffFROM user_behavior_log) t)
select *
from user_sessions;

步骤2：构建行为转移对

sql">WITH user_sessions AS (select user_id,behavior,`timestamp`,user_group,sum(diff) over (partition by user_id order by `timestamp`) session_idfrom (SELECT user_id,behavior,`timestamp`,user_group,CASEWHEN (`timestamp` - LAG(`timestamp`, 1, 0)OVER (PARTITION BY user_id ORDER BY `timestamp`)) >1800 THEN 1ELSE 0 END diffFROM user_behavior_log) t)SELECTuser_id,session_id,behavior AS from_behavior,LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behaviorFROM user_sessions

 步骤3：计算转移对次数

sql">WITH user_sessions AS (select user_id,behavior,`timestamp`,user_group,sum(diff) over (partition by user_id order by `timestamp`) session_idfrom (SELECT user_id,behavior,`timestamp`,user_group,CASEWHEN (`timestamp` - LAG(`timestamp`, 1, 0)OVER (PARTITION BY user_id ORDER BY `timestamp`)) >1800 THEN 1ELSE 0 END diffFROM user_behavior_log) t)
,  transition_behav_pairs as
(
SELECTuser_id,session_id,behavior AS from_behavior,LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behaviorFROM 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：计算转移概率

现在我们将计算转移概率，即每个行为对转移次数除以该用户该行为的总转移次数。

sql">WITH user_sessions AS (select user_id,behavior,`timestamp`,user_group,sum(diff) over (partition by user_id order by `timestamp`) session_idfrom (SELECT user_id,behavior,`timestamp`,user_group,CASEWHEN (`timestamp` - LAG(`timestamp`, 1, `timestamp`)OVER (PARTITION BY user_id ORDER BY `timestamp`)) >1800 THEN 1ELSE 0 END diffFROM user_behavior_log) t)
,  transition_behav_pairs as
(
SELECTuser_id,session_id,behavior AS from_behavior,LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behaviorFROM user_sessions
),
transition_behav_pairs_cnt as(select user_id, from_behavior, to_behavior, count(*) transition_cntfrom transition_behav_pairsWHERE to_behavior IS NOT NULLgroup 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：构建转移概率矩阵

sql">WITH user_sessions AS (select user_id,behavior,`timestamp`,user_group,sum(diff) over (partition by user_id order by `timestamp`) session_idfrom (SELECT user_id,behavior,`timestamp`,user_group,CASEWHEN (`timestamp` - LAG(`timestamp`, 1, `timestamp`)OVER (PARTITION BY user_id ORDER BY `timestamp`)) >1800 THEN 1ELSE 0 END diffFROM user_behavior_log) t)
,  transition_behav_pairs as
(
SELECTuser_id,session_id,behavior AS from_behavior,LEAD(behavior) OVER (PARTITION BY user_id, session_id ORDER BY `timestamp`) AS to_behaviorFROM user_sessions
),
transition_behav_pairs_cnt as(select user_id, from_behavior, to_behavior, count(*) transition_cntfrom transition_behav_pairsWHERE to_behavior IS NOT NULLgroup 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_probabilityfrom transition_behav_pairs_cnt)SELECTuser_id,collect_list(named_struct('from_behavior', from_behavior, 'to_behavior', to_behavior, 'probability', transition_probability)) AS transition_matrixFROMtransition_probabilitiesGROUP BYuser_id
sql">#,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/143609283https://flyingsql.blog.csdn.net/article/details/143609283


http://www.ppmy.cn/news/1567765.html

相关文章

HarmonyOS简介：高效开发与测试

HarmonyOS简介：高效开发与测试

ArkTS语言 ArkTS是HarmonyOS优选的主力应用开发语言。 在TypeScript生态的基础上做了进一步拓展，保持了其基本风格，同时通过规范定义，强化开发期静态检查和分析，提升程序执行稳定性和性能。 如图所示代码示例 UI界面会显示一段…
阅读更多...
利用ue5制作CG动画笔记

利用ue5制作CG动画笔记

tips： 按住鼠标中键可以拖动枢轴点 在曲线编辑器中按住shift可以使曲线编辑保持在x轴 专业术语： CGI：计算机生成图象（computer-generated imagery）真实的不算，计算机生成的 Compositing：合…
阅读更多...
代码随想录算法【Day34】

代码随想录算法【Day34】

Day34 62.不同路径 思路 第一种&#xff1a;深搜 -> 超时 第二种&#xff1a;动态规划 第三种&#xff1a;数论 动态规划代码如下&#xff1a; class Solution { public:int uniquePaths(int m, int n) {vector<vector<int>> dp(m, vector<int>(n,…
阅读更多...
http3网站的设置(AI不会配，得人工配)

http3网站的设置(AI不会配，得人工配)

堡塔PHP项目中配置nginx1.26.0设置http3协议 # 文件所在服务器中的路径 /www/server/nginx/conf/nginx.confuser www www; worker_processes auto; error_log /www/wwwlogs/nginx_error.log crit; pid /www/server/nginx/logs/nginx.pid; worker_rlimit_nofile 512…
阅读更多...
Hive详细讲解-调优分区表速通

Hive详细讲解-调优分区表速通

文章目录 1.分区表和分桶表1分区表2.二级分区3.动态分区4.动态分区测试 2.分桶表1.基本语法2.分桶表导入数据3.分桶排序表 3.文件格式压缩4.hive文件格式4.1 text file&#xff08;默认文件格式&#xff09;4.2 orc文件 &#xff08;常用&#xff09;4.3 orc存储使用列存储&…
阅读更多...
【数据分析】基础篇

【数据分析】基础篇

不定期更新&#xff0c;建议关注、收藏、点赞&#xff01; 流程 明确目标 业务流程数据监控描述、市场宏观环境分析、竞品分析、数据探索挖掘获取数据 来源&#xff1a;数据库和日志、埋点需求前端埋点特殊数据、业务人员Excel表格、爬虫采集数据、公开数据集、商业平台导出的…
阅读更多...
DeepseekMath：超强开源数学模型（论文详解）

DeepseekMath：超强开源数学模型（论文详解）

摘要 近日&#xff0c;中国团队Deepseek推出了一款名为DeepSeekMath的7B开源数学模型&#xff0c;这个模型在数学方面的表现令人惊叹。DeepSeekMath 7 B在不依赖外部工具包和投票技术的情况下&#xff0c;在竞赛级MATH基准测试中获得了51.7%的分数&#xff0c;接近Gemini-Ultr…
阅读更多...
解锁.NET Standard库：从0到1的创建与打包秘籍

解锁.NET Standard库：从0到1的创建与打包秘籍

一、引言 在当今的软件开发领域&#xff0c;跨平台开发已成为一种趋势。随着不同操作系统和设备的多样化&#xff0c;开发人员需要确保他们的代码能够在多个平台上运行&#xff0c;以满足更广泛的用户需求。.NET Standard 库应运而生&#xff0c;它定义了一组公共 API&#xf…
阅读更多...
最新文章

Copyright @ 2022~2023