2024-11 京东 简单

假设你是京东电商平台美妆频道的运营团队成员,为了优化用户的搜索体验和提升销售转化率,你需要对用户的搜索词、点击行为和购买行为进行分析。

本专题的具体任务包括但不限于但不限于:

  1. 热门搜索关键词分析:统计最常被搜索的关键词,了解用户的搜索习惯和需求,优化商品标题和描述中的关键词。

  2. 热门点击页面分析:统计用户最常点击的页面,优化页面布局和推荐算法,提高用户点击率。

  3. 热门购买商品分析:统计最常被购买的商品,了解用户的购买偏好,优化促销活动和推荐策略。

  4. 搜索到点击的转化率分析:计算从搜索到点击的转化率,评估搜索建议和搜索结果的相关性,优化搜索体验。

为了达到上述目标,我们收集了以下四个表的数据:jx_query_rcd(用户搜索词记录表)、jx_click_rcd(用户页面点击表)、jx_pchs_rcd(用户购买记录表)、jx_gd_pg_map(页面和商品映射表),其中很重要的一个概念是session_id。

做数分不能只学SQL吧,这些概念你也得懂。

session_id 是一个唯一标识符,用于表示用户的一次会话。一次会话通常指用户在一段时间内与网站或应用的连续交互。例如,用户打开浏览器,进行一系列操作(如搜索、点击、购买),然后关闭浏览器,这整个过程可以视为一个会话。

  1. 行为追踪:通过 session_id,我们可以追踪用户在一个会话中的所有行为。例如,用户在一个会话中可能进行了多次搜索、点击了多个页面、最终购买了一件商品,这些行为都可以通过同一个 session_id 关联起来。

  2. 行为分析:使用 session_id 可以帮助我们分析用户的完整行为路径。例如,我们可以看到用户从搜索某个关键词开始,点击了哪些页面,最后是否进行了购买。这种分析有助于优化用户体验和营销策略。

  3. 数据聚合:通过 session_id,我们可以将分散在多个表中的用户行为数据聚合在一起,形成完整的用户行为记录。 

任务

jx_query_rcd 表中,统计最常被搜索的关键词。

结果应包括关键词和搜索次数,并按搜索次数降序排列,取前5名。

考点

  1. 聚合函数:使用 COUNT 统计每个关键词的搜索次数。

  2. 分组:使用 GROUP BY 按关键词分组。

  3. 排序:使用 ORDER BY 按搜索次数降序排列。

  4. 限制结果:使用 LIMIT 取前5名。

电商搜索优化 聚合函数 group by count

jx_query_rcd,用户搜索词记录表

usr_id search_tm key_word session_id
884 2021-02-25 12:45:51 控油水乳 1038847
867 2021-02-07 01:25:47 sk2 1028674
754 2021-09-09 01:48:32 补水保湿水 1117549
850 2021-05-29 05:50:54 日本化妆品 进口 1158501
152 2021-04-21 22:23:05 补水保湿水 1041522

jx_click_rcd,用户页面点击表

usr_id click_page_id session_id
814 h344 1118146
638 h343 1186382
879 h251 1068799
181 h232 1051818
025 h231 1120259

 jx_pchs_rcd,用户购买记录表

usr_id good_id session_id
141 2568 1121415
159 2345 1041597
283 2356 1092831
292 2356 1142922
071 2567 1080711

 jx_gd_page_map,页面和商品映射表

brnd_nm gd_id gd_nm page_id
雅史兰黛 2345 特润修护小zong瓶精华50ml面部精华液滋润保湿淡化细纹温和不刺护肤品礼盒 h231
雅史兰黛 2356 小zong瓶特润修护精华眼霜15ml熬夜眼霜淡黑眼圈鱼尾纹温和不刺激护肤品礼盒 h232
欧来雅 2567 男士全新早晚控油补水氨基酸洁面套装(炭爽净油+水能保湿)洗面奶男 氨基酸 h251
欧来雅 2568 复颜视黄醇精粹抗皱紧致水乳护肤套装化妆品礼盒礼物送女友(水+乳液) h253
滋生堂 3467 滋润套装(薇水乳 礼盒内赠红腰子10ml*3+洁面15ml*2+精粹液30ml*2+悦薇眼膜6对) h342

 成为会员,解锁300道大厂数分SQL真题

ID104 按照车类统计行程次数
入门 group by count 聚合函数 运力调度评估 滴滴出行
ID233 按终点统计行程次数  
入门 group by count 聚合函数 运力调度评估 哈啰出行
ID245 统计每个作者发布的AI配音视频数量  
入门 group by count 聚合函数 短视频指标体系 快手
ID310 计算每个用户的购买频率-次数(F)  
简单 group by count 聚合函数 RFM模型 美团
ID332 统计每个用户领取了多少张优惠券  
简单 group by count 聚合函数 口径探查 优惠券使用场景 拼多多

MySQL 8.0

00:00