SQL 网站场景面试题, 编写 SQL,查询在 2024 年 7 月 1 日访问了 home 页面但没有访问 contact 页面的用户的用户名去重

QA

Step 1

Q:: 编写 SQL 查询，查询在 2024 年 7 月 1 日访问了 '/home' 页面但没有访问 '/contact' 页面的用户的用户名（去重）

A:: 可以使用子查询来实现，具体的 SQL 语句如下：

 
SELECT DISTINCT u.username
FROM users u
JOIN page_visits pv ON u.user_id = pv.user_id
WHERE pv.page_url = '/home'
AND pv.visit_date = '2024-07-01'
AND u.user_id NOT IN (
  SELECT user_id
  FROM page_visits
  WHERE page_url = '/contact'
);

该查询首先选取所有在 2024 年 7 月 1 日访问 '/home' 页面的用户，然后排除所有访问过 '/contact' 页面的用户。

用途

这个内容之所以在面试中被提问，是因为它涉及多个关键的 SQL 技能，例如子查询、连接和日期过滤。这些技能在实际生产环境中非常重要，尤其是在用户行为分析、日志分析和数据报表生成等场景下。通过这个问题，可以考察候选人对 SQL 的熟练程度以及解决实际业务问题的能力。\n

相关问题

🦆

编写 SQL 查询,找出在特定日期范围内访问次数最多的用户及其访问次数▷

 
SELECT u.username, COUNT(pv.page_url) as visit_count
FROM users u
JOIN page_visits pv ON u.user_id = pv.user_id
WHERE pv.visit_date BETWEEN '2024-07-01' AND '2024-07-31'
GROUP BY u.username
ORDER BY visit_count DESC
LIMIT 1;

这段 SQL 语句通过连接用户表和页面访问记录表，统计每个用户在指定日期范围内的访问次数，并按访问次数降序排列，最终返回访问次数最多的用户及其访问次数。

🦆

编写 SQL 查询,统计每个页面的访问次数▷

 
SELECT pv.page_url, COUNT(pv.page_url) as visit_count
FROM page_visits pv
GROUP BY pv.page_url
ORDER BY visit_count DESC;

这段 SQL 语句统计每个页面的访问次数，并按访问次数降序排列。通过这种查询，可以了解哪些页面的访问量较大，从而帮助优化网站内容和结构。

🦆

编写 SQL 查询,找出最近一个月新增用户的数量▷

 
SELECT COUNT(*) as new_users
FROM users
WHERE signup_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 1 MONTH) AND CURDATE();

这段 SQL 语句统计最近一个月内注册的新用户数量。通过这类查询，可以评估用户增长情况和市场推广效果。

🦆

编写 SQL 查询,找出访问 home 页面次数最多的前 10 个用户及其访问次数▷

 
SELECT u.username, COUNT(pv.page_url) as visit_count
FROM users u
JOIN page_visits pv ON u.user_id = pv.user_id
WHERE pv.page_url = '/home'
GROUP BY u.username
ORDER BY visit_count DESC
LIMIT 10;

这段 SQL 语句统计访问 '/home' 页面次数最多的前 10 个用户及其访问次数。通过这种查询，可以分析特定页面的活跃用户，从而针对性地优化用户体验。

SQL 进阶查询面试题, 编写 SQL,查询在 2024 年 7 月 1 日访问了 home 页面但没有访问 contact 页面的用户的用户名去重

QA

Step 1

Q:: 编写 SQL，查询在 2024 年 7 月 1 日访问了 '/home' 页面但没有访问 '/contact' 页面的用户的用户名（去重）

A::

 
SELECT DISTINCT username
FROM user_visits
WHERE visit_date = '2024-07-01'
AND page = '/home'
AND username NOT IN (
    SELECT username
    FROM user_visits
    WHERE visit_date = '2024-07-01' AND page = '/contact'
);

此查询使用了子查询（subquery）来过滤出那些访问了 '/contact' 页面但不应包含在最终结果中的用户。子查询返回访问了 '/contact' 页面用户的用户名列表，然后主查询通过 NOT IN 操作符排除这些用户，最终获取在指定日期访问了 '/home' 页面但没有访问 '/contact' 页面的唯一用户名。

Step 2

Q:: 在实际生产环境中，为什么需要对用户名去重？如何实现去重？

A:: 去重操作在处理用户数据时非常常见，尤其是在分析用户行为或生成报表时。重复的数据会导致统计信息失真，比如用户访问频次或独立访客数等指标的错误计算。在 SQL 中，实现去重通常使用 DISTINCT 关键字，它会筛选出结果集中所有唯一的记录。如果查询的目标列中有重复值，DISTINCT 会确保每个值只出现一次。

Step 3

Q:: 在子查询中，NOT IN 与 LEFT JOIN + WHERE IS NULL 有什么区别？

A:: NOT IN 和 LEFT JOIN + WHERE IS NULL 都可以用于排除子查询中的结果，但它们的性能和适用场景略有不同。NOT IN 在处理子查询结果时，如果子查询结果中有 NULL，可能导致不一致的结果，因为 NULL 值会使条件判断失败。而使用 LEFT JOIN + WHERE IS NULL 时，通过连接主表和子查询结果，并在连接失败时过滤数据，能够避免 NULL 引发的问题，通常在处理大数据量时也有更好的性能表现。

Step 4

Q:: 什么是索引(Index)？在这个查询中会涉及到哪些索引，为什么？

A:: 索引是数据库的一种数据结构，用于加速数据检索操作。通过索引，数据库能够更快地定位记录，减少全表扫描的发生。在这个查询中，访问时间 (``visit_date``)、页面路径 (``page``) 以及用户名 (``username``) 都可能涉及索引。如果 user_visits 表对这些列建立了合适的索引，查询速度将会显著提高，尤其是当数据量非常大的情况下。

Step 5

Q:: 如果访问日志数据量非常大，这个查询的性能可能会出现哪些问题？如何优化？

A:: 随着数据量的增加，查询性能可能会下降，尤其是涉及子查询或 NOT IN 这样的操作时。可能遇到的问题包括：全表扫描导致的查询延迟、内存不足导致的查询失败等。可以考虑以下优化措施： 1. 使用索引加速检索。 2. 将子查询改为 LEFT JOIN + WHERE IS NULL。 3. 通过分区表（Partitioned Table）来管理不同时间段的数据。 4. 预计算某些中间结果，将其存储在中间表中。

用途

面试这些内容是为了考察应聘者在实际项目中处理复杂 SQL 查询的能力。能够编写高效的 SQL 查询，尤其是在数据量大的场景下，是数据工程师和数据库管理员的核心技能。这个内容通常会在需要分析用户行为、生成用户访问报表，或者在处理数据清洗和整合任务时用到。在实际生产环境中，了解如何对数据去重、优化查询性能和理解索引的应用场景，可以极大提高系统的响应速度和稳定性。\n