校招算法笔面试 | SQL笔试面试编程题-提取博客URL中的用户名

最新推荐文章于 2025-12-15 12:49:44 发布

原创最新推荐文章于 2025-12-15 12:49:44 发布 · 248 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#面试 #sql #职场和发展 #算法 #SQL #算法SQL

题目## 题目

题目链接

题目描述

运营团队希望将用户申请参与比赛的个人博客用户名从 blog_url 字段中提取出来，并将其单独记录为一个新的字段 user_name。blog_url 字段的格式为 http:/url/<username>，其中 <username> 为用户的个人博客用户名。需要从 user_submit 表中提取 device_id 和 user_name，并按照指定格式输出结果。

解题思路

首先，理解数据结构，blog_url 的格式为 http:/url/<username>，需要从中提取 <username> 部分。
接着，使用 MySQL 的 SUBSTRING_INDEX 函数，通过 SUBSTRING_INDEX(blog_url, '/', -1) 获取 blog_url 中最后一个 / 之后的部分，即用户名。
最后，选择 device_id 和提取出的 user_name，并按 device_id 升序排列，以确保输出顺序一致。

SQL 查询

-- 从 blog_url 中提取用户名，并选择 device_id 和 user_name

SELECT 
    device_id, 
    SUBSTRING_INDEX(blog_url, '/', -1) AS user_name  -- 提取 blog_url 中最后一个 '/' 后的字符串作为 user_name
FROM 
    user_submit
ORDER BY 
    device_id;  -- 按 device_id 升序排列结果

代码解释

SUBSTRING_INDEX(blog_url, '/', -1) AS user_name：
- SUBSTRING_INDEX 函数用于根据指定的分隔符 / 截取字符串。
- 参数说明：
  - blog_url：要处理的字符串字段。
  - '/'：分隔符。
  - -1：表示从字符串的右侧开始截取，第一个出现的分隔符后面的所有内容。
- 该表达式提取出 blog_url 字段中最后一个 / 后的部分，即用户名，并将其命名为 user_name。

题目链接

这道题目要求我们计算推荐内容准确的用户的平均评分。下面是这个SQL查询的思路和实现步骤。

1. 确定总体问题

我们需要找出推荐内容准确的用户，并计算这些用户的平均评分。准确的定义是推荐的内容标签与用户喜好标签一致。我们需要从两个表中提取数据，计算符合条件的用户的平均评分，并保留三位小数。

2. 分析关键问题

识别准确推荐：找出推荐内容标签与用户喜好标签一致的用户。
计算平均评分：对这些用户的评分求平均值，并保留三位小数。

3. 解决每个关键问题的代码及讲解

步骤1：识别准确推荐

我们使用子查询来找出推荐内容标签与用户喜好标签一致的用户：

SELECT DISTINCT rec.rec_user
FROM recommend_tb rec
JOIN user_action_tb ua_inner ON rec.rec_user = ua_inner.user_id
WHERE rec.rec_info_l = ua_inner.hobby_l

JOIN user_action_tb ua_inner ON rec.rec_user = ua_inner.user_id：通过用户ID连接两个表。
WHERE rec.rec_info_l = ua_inner.hobby_l：过滤出推荐内容标签与用户喜好标签一致的记录。
SELECT DISTINCT rec.rec_user：选择符合条件的用户ID，并去重。

步骤2：计算平均评分

我们使用主查询来计算这些用户的平均评分：

SELECT 
    ROUND(AVG(ua.score), 3) AS avg_score
FROM 
    user_action_tb ua
WHERE 
    ua.user_id IN (
        -- 子查询
    )

AVG(ua.score)：计算符合条件用户的平均评分。
ROUND(..., 3)：将结果四舍五入保留三位小数。
WHERE ua.user_id IN (...)：使用子查询结果过滤出符合条件的用户。

完整代码

SELECT 
    ROUND(AVG(ua.score), 3) AS avg_score
FROM 
    user_action_tb ua
WHERE 
    ua.user_id IN (
        SELECT DISTINCT rec.rec_user
        FROM recommend_tb rec
        JOIN user_action_tb ua_inner ON rec.rec_user = ua_inner.user_id
        WHERE rec.rec_info_l = ua_inner.hobby_l
    );