interview
sql-web-scenarios
编写 SQL使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期

SQL 网站场景面试题, 编写 SQL,使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期

SQL 网站场景面试题, 编写 SQL,使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期

QA

Step 1

Q:: 使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期。

A:: 可以使用如下SQL语句：

 
SELECT user_id, page_path, visit_date
FROM (
  SELECT user_id, page_path, visit_date,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_date DESC) as rn
  FROM user_visits
) t
WHERE rn = 1;

该SQL语句通过开窗函数 ROW_NUMBER() 按照每个用户的访问日期进行排序，并选出每个用户最近的一次访问记录。

Step 2

Q:: 什么是开窗函数？在SQL中如何使用？

A:: 开窗函数是一种在结果集中进行计算的函数，但与普通的聚合函数不同，它不会将行合并为单个结果，而是在结果集的每一行中执行计算。常用的开窗函数有 ROW_NUMBER()``, RANK()``, DENSE_RANK()``, SUM()``, AVG() 等。使用方式如下：

 
SELECT column, 
       ROW_NUMBER() OVER (PARTITION BY column ORDER BY column) as row_num
FROM table_name;

Step 3

Q:: 如何优化包含开窗函数的SQL查询？

A:: 优化包含开窗函数的SQL查询的方法有： 1. 确保正确的索引存在，特别是用于 PARTITION BY 和 ORDER BY 列。 2. 尽量减少数据集的大小，通过子查询或CTE先过滤掉不必要的数据。 3. 使用合适的分区和排序策略，避免全表扫描。 4. 避免在开窗函数中使用复杂的计算，尽可能提前计算好结果。

用途

开窗函数是现代SQL查询中非常重要的工具，尤其在数据分析和报表生成方面。面试这个内容是为了评估候选人对高级SQL功能的理解和应用能力。在实际生产环境中，这类查询常用于数据分析、用户行为追踪、时间序列数据处理等场景。例如，在用户行为分析中，我们经常需要找到每个用户的最新活动记录或者计算用户在一段时间内的行为累计值。\n

相关问题

🦆
如何查询每个用户的访问次数?▷

可以使用如下SQL语句：

 
SELECT user_id, COUNT(*) as visit_count
FROM user_visits
GROUP BY user_id;
🦆
什么是CTE公用表表达式?如何使用?▷

CTE（公用表表达式）是一种可以在一个查询中多次引用的临时结果集。使用CTE可以使SQL查询更加清晰和易读。使用方式如下：

 
WITH CTE_name AS (
  SELECT ...
)
SELECT ...
FROM CTE_name;
🦆
什么是子查询?它们的作用和限制是什么?▷

子查询是嵌套在另一个查询中的查询，可以在 SELECT、FROM、WHERE 和 HAVING 子句中使用。它们用于生成中间结果集以供外层查询使用。子查询的限制包括：性能较低，特别是在处理大量数据时，以及某些数据库系统对嵌套层数的限制。

🦆
如何进行复杂的多表连接查询?▷

多表连接查询通过使用 JOIN 关键字将多个表的数据关联起来，常用的连接方式包括：内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全连接（FULL JOIN）等。一个简单的多表连接查询示例如下：

 
SELECT a.column1, b.column2
FROM table1 a
JOIN table2 b ON a.id = b.id;
🦆
在数据分析中,如何使用窗口函数进行累计求和?▷

可以使用窗口函数 SUM() 来进行累计求和，如下示例：

 
SELECT column,
       SUM(column) OVER (ORDER BY column) as cumulative_sum
FROM table_name;

SQL 进阶查询面试题, 编写 SQL,使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期

QA

Step 1

Q:: 编写 SQL，使用开窗函数查询每个用户最近一次访问的页面路径及其访问日期

A:: 可以使用ROW_NUMBER()窗口函数结合PARTITION BY和ORDER BY来实现。假设有一个表名为'user_logs'，包含字段'user_id'（用户ID）、'page_path'（页面路径）和'visit_date'（访问日期），SQL查询语句如下：

 
SELECT user_id, page_path, visit_date
FROM (
    SELECT user_id, page_path, visit_date,
           ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY visit_date DESC) AS rn
    FROM user_logs
) t
WHERE rn = 1;

这个查询语句会返回每个用户最近一次访问的页面路径及其访问日期。

Step 2

Q:: 为什么开窗函数对查询操作非常重要？

A:: 开窗函数（如ROW_NUMBER, RANK, DENSE_RANK, SUM, AVG等）允许我们在SQL查询中计算一些累积值、排名、或其他基于某些分组的数据操作，而不必进行复杂的子查询或使用临时表。它使得数据分析更加高效和简洁，是处理复杂查询需求的一个有力工具。

Step 3

Q:: ROW_NUMBER()和RANK()的区别是什么？

A:: ROW_NUMBER()为每一行分配一个唯一的序号，按排序顺序递增，即使有相同的值也会赋予不同的序号。而RANK()对于相同的排序值会赋予相同的排名，之后的排名会跳过。例如，如果有两个值为第一名，下一个排名会是第三名。

Step 4

Q:: PARTITION BY子句在开窗函数中的作用是什么？

A:: PARTITION BY子句用于将查询结果集划分成多个分区，然后在每个分区内应用窗口函数。如果省略PARTITION BY子句，则窗口函数会应用于整个结果集。这在处理类似'每个用户'、'每个分类'这样需要对数据分组并在组内进行计算的场景中特别有用。

Step 5

Q:: 什么时候使用开窗函数而不是GROUP BY？

A:: 当你需要在不减少数据行的情况下计算聚合结果或累积值时，应该使用开窗函数。GROUP BY会将结果集按分组列聚合，而开窗函数则允许你在分组的基础上保留原始的详细数据。比如，你可能想要在每行数据上增加一个总和或排名列，而不丢失其他数据，这时开窗函数是最合适的。

用途

面试中会考察开窗函数的使用，主要是因为这些函数在处理复杂查询时非常高效且简洁，特别是在需要对数据进行分组并在组内进行计算的场景中。例如，获取每个用户最近一次访问记录、计算分组内的排名或累积和、在不丢失详细数据的情况下进行聚合计算等。实际生产环境中，通常在进行报表、数据分析、用户行为跟踪、业务KPI计算等场景下，会频繁用到开窗函数。\n

相关问题

🦆
如何查询每个用户的访问次数?▷

可以使用GROUP BY来分组并统计每个用户的访问次数，SQL查询语句如下：

 
SELECT user_id, COUNT(*) AS visit_count
FROM user_logs
GROUP BY user_id;
🦆
如何查询访问次数最多的页面?▷

可以使用GROUP BY与ORDER BY结合查询出访问次数最多的页面，SQL查询语句如下：

 
SELECT page_path, COUNT(*) AS visit_count
FROM user_logs
GROUP BY page_path
ORDER BY visit_count DESC
LIMIT 1;
🦆
如何查找访问时间在某一段时间内的所有记录?▷

可以通过WHERE子句结合时间范围条件来查询，SQL查询语句如下：

 
SELECT *
FROM user_logs
WHERE visit_date BETWEEN '2023-01-01' AND '2023-01-31';
🦆
如何用SQL查询出每个用户的首次和最后一次访问时间?▷

可以通过GROUP BY结合MIN()和MAX()函数来实现，SQL查询语句如下：

 
SELECT user_id, MIN(visit_date) AS first_visit, MAX(visit_date) AS last_visit
FROM user_logs
GROUP BY user_id;
🦆
如何计算访问时间间隔如天数并查询出来?▷

可以使用DATEDIFF函数计算时间差，例如查询每个用户的首次和最近一次访问的间隔天数，SQL查询语句如下：

 
SELECT user_id, DATEDIFF(MAX(visit_date), MIN(visit_date)) AS days_interval
FROM user_logs
GROUP BY user_id;
编写 SQL查询访问记录表中所有页面的页面 ID 和访问日期编写 SQL查询用户表中注册日期在 2024 年 1 月 1 日之后的用户名和注册日期