SQL开窗函数相关的面试题和答案

基本排序与分组问题

• 题目：有学生成绩表tb_score，包含字段SNO（学号）、SCLASS（班级）、CHINESE（语文成绩）、ENGLISH（英语成绩）、ARITH（数学成绩）。要求查询每个班级内，按照语文成绩降序、英语成绩升序、数学成绩降序排序，展示每个学生的信息及在班级内的排名。
• 示例代码：

  
SELECT sno, sclass, chinese, english, arith,RANK() OVER(PARTITION BY sclass ORDER BY chinese DESC) rn1_chinese,RANK() OVER(PARTITION BY sclass ORDER BY english ASC) rn2_english,RANK() OVER(PARTITION BY sclass ORDER BY arith DESC) rn3_arith
FROM tb_score;

TopN问题

• 题目：有一张“学生成绩表”，包含4个字段：班级id、学生id、课程id、成绩。查询每个班级中成绩排名前三的学生信息。
• 示例代码：

  
SELECT *
FROM (SELECT *,DENSE_RANK() OVER(PARTITION BY 班级id ORDER BY 成绩 DESC) AS 顺序FROM 学生成绩表
) t1
WHERE 顺序 <= 3;

聚合计算问题

• 题目：有用户存款表tb_user，包含字段name（姓名）、month（月份）、amt（存款金额）。计算每个用户的累计存款金额以及在每个月的存款金额占该用户总存款金额的比例。
• 示例代码：

  
SELECT s.*,SUM(s.amt) OVER(PARTITION BY s.name ORDER BY s.month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 累计存款金额,ROUND(s.amt / SUM(s.amt) OVER(PARTITION BY s.name), 2) AS 占比
FROM tb_user s;

数据偏移与连续判断问题

• 题目：有用户登陆表user_login_table，包含字段user_name（用户名）、date（登陆时间）。找出连续5天都登陆平台的用户。
• 示例代码：

  
SELECT b.user_name
FROM (SELECT user_name,date,LEAD(date, 5) OVER(PARTITION BY user_name ORDER BY date DESC) AS date_5FROM user_login_table
) b
WHERE b.date IS NOT NULLAND DATE_SUB(CAST(b.date AS DATE), INTERVAL 5 DAY) = CAST(b.date_5 AS DATE);

分组统计与对比问题

• 题目：有员工信息表，包含字段员工ID、部门ID、薪资。找出每个部门中薪资高于该部门平均薪资的员工信息。
• 示例代码：

  
SELECT *
FROM (SELECT *,AVG(薪资) OVER(PARTITION BY 部门ID) AS 部门平均薪资FROM 员工信息表
) t
WHERE 薪资 > 部门平均薪资;

数据分布与排名问题

• 题目：有商品销售表sales，含字段product_id（商品ID）、category（商品类别）、sales_amount（销售金额）。查询每个商品类别中，销售金额处于前20%的商品信息。
• 思路：用NTILE函数将每个类别内的商品按销售金额排名分桶，选出位于第一桶（前20%）的商品。

SELECT product_id, category, sales_amount
FROM (SELECT product_id, category, sales_amount,NTILE(5) OVER(PARTITION BY category ORDER BY sales_amount DESC) AS percentileFROM sales
) AS subquery
WHERE percentile = 1;

移动平均与趋势分析问题

• 题目：有股票交易表stock_trades，含字段trade_date（交易日期）、stock_symbol（股票代码）、closing_price（收盘价）。计算每只股票近5个交易日的移动平均收盘价。
• 思路：用AVG函数结合开窗，对每只股票按交易日期排序，计算当前行及前4行的收盘价平均值。

SELECT trade_date, stock_symbol, closing_price,AVG(closing_price) OVER(PARTITION BY stock_symbol ORDER BY trade_dateROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg_5day
FROM stock_trades;

分组累计与占比问题

• 题目：有订单表orders，含字段order_date（订单日期）、customer_id（客户ID）、order_amount（订单金额）。查询每个客户每月的累计订单金额及该月订单金额占当年总订单金额的比例。
• 思路：先按客户和订单日期分区，用SUM函数计算累计订单金额，再用每月订单金额除以当年总订单金额得出占比。

SELECT customer_id, order_date, order_amount,SUM(order_amount) OVER(PARTITION BY customer_id, YEAR(order_date)ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS monthly_cumulative_amount,order_amount / SUM(order_amount) OVER(PARTITION BY customer_id, YEAR(order_date)) AS monthly_percentage
FROM orders;

数据筛选与条件判断问题

• 题目：有学生考试成绩表exam_scores，含字段student_id（学生ID）、exam_date（考试日期）、subject（科目）、score（成绩）。找出每个学生至少连续3次考试成绩都在80分以上的科目。
• 思路：用LAG或LEAD函数获取前后行成绩，结合窗口函数判断是否连续3次成绩大于80分，再筛选出满足条件的科目。

SELECT student_id, subject
FROM (SELECT student_id, subject,-- 标记连续三次成绩大于80分CASEWHEN score > 80 ANDLAG(score, 1) OVER(PARTITION BY student_id, subject ORDER BY exam_date) > 80 ANDLAG(score, 2) OVER(PARTITION BY student_id, subject ORDER BY exam_date) > 80THEN 1ELSE 0END AS consecutive_80_plusFROM exam_scores
) AS subquery
WHERE consecutive_80_plus = 1
GROUP BY student_id, subject;

行列转换与统计问题

• 题目：有销售记录表sales_records，含字段product_name（产品名称）、month（月份）、sales_volume（销量）。将每个月的销量数据转换为列，即每行是一个产品，每列是对应月份的销量，并计算每个产品的全年总销量。
• 思路：用CASE语句结合开窗函数，按产品名称分区，将不同月份的销量值分配到对应的列，再用SUM函数计算全年总销量。

SELECT product_name,-- 为每个月创建一列，统计销量MAX(CASE WHEN month = '一月' THEN sales_volume END) AS 一月销量,MAX(CASE WHEN month = '二月' THEN sales_volume END) AS 二月销量,-- 以此类推...SUM(sales_volume) AS 全年总销量
FROM sales_records
GROUP BY product_name;

在不同的数据库中， PIVOT 函数的语法略有不同，以下以常见的关系型数据库（如SQL Server）为例来解决这个问题：

-- 使用PIVOT函数
SELECT product_name,-- 1月销量ISNULL([1], 0) AS "January",ISNULL([2], 0) AS "February",ISNULL([3], 0) AS "March",ISNULL([4], 0) AS "April",ISNULL([5], 0) AS "May",ISNULL([6], 0) AS "June",ISNULL([7], 0) AS "July",ISNULL([8], 0) AS "August",ISNULL([9], 0) AS "September",ISNULL([10], 0) AS "October",ISNULL([11], 0) AS "November",ISNULL([12], 0) AS "December",-- 计算全年总销量ISNULL([1], 0) + ISNULL([2], 0) + ISNULL([3], 0) + ISNULL([4], 0) + ISNULL([5], 0) + ISNULL([6], 0) + ISNULL([7], 0) + ISNULL([8], 0) + ISNULL([9], 0) + ISNULL([10], 0) + ISNULL([11], 0) + ISNULL([12], 0) AS total_sales_volume
FROM 
(SELECT product_name, month, sales_volumeFROM sales_records
) AS SourceTable
PIVOT
(SUM(sales_volume)FOR month IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
) AS PivotTable;

这段代码做了以下几件事：

首先在子查询中，选取了 product_name 、 month 和 sales_volume 字段，形成一个中间数据集 SourceTable 。
然后使用 PIVOT 函数，对 SourceTable 中的数据进行行列转换。以 month 字段作为列转行的依据，将每个月对应的 sales_volume 进行聚合求和 。
在外层查询中，用 ISNULL 函数处理那些某个月没有销售数据的情况，将其置为0，同时计算出每个产品全年的总销量。