专业网站改版,网站建设咨,网站建设歺首选金手指14,什么叫做关键词基本排序与分组问题
题目#xff1a;有学生成绩表tb_score#xff0c;包含字段SNO#xff08;学号#xff09;、SCLASS#xff08;班级#xff09;、CHINESE#xff08;语文成绩#xff09;、ENGLISH#xff08;英语成绩#xff09;、ARITH#xff08;数学成绩有学生成绩表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同时计算出每个产品全年的总销量。
