大数据sql面试题整理

connygpt 2024-12-05 12:32 14 浏览

1. 有一个录取学生人数表，记录的是每年录取学生人数和入学学生的学制

以下是表结构：

CREATE TABLE `admit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`year` int(255) DEFAULT NULL COMMENT '入学年度',
`num` int(255) DEFAULT NULL COMMENT '录取学生人数',
`stu_len` varchar(255) DEFAULT NULL COMMENT '学生学制',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='录取人数';

year表示学生入学年度

num表示对应年度录取学生人数

stu_len表示录取学生的学制

说明：

例如录取年度2018学制3，表示该批学生在校年份为2018~2019、2019~2020、2020~2021

以下是示例数据：

id	year	num	stu_len
1	2018	2000	3
2	2019	2000	3
3	2020	1000	4
4	2020	2000	3

根据以上示例计算出每年在校人数,写出SQL语句：

SQL 思路：
-- 每年在校人数= 当前学生数+新增人数
-- 需要过滤出学生人数并累计（sum），过滤条件为（>=当前年数 and<=当前年数+学制）（case when）

SELECT
t1.year,
SUM(CASE WHEN t1.year>=t.year AND t1.year <= (t.year + CAST(t.stu_len AS UNSIGNED INTEGER))
THEN t.num ELSE 0 END
) AS '每年在校人数'
FROM
admit t,
(
SELECT
year
FROM
admit
GROUP BY
year
)t1
GROUP BY
t1.year;

计算结果截图：

2. 有一个分数表id 是该表的主键。该表的每一行都包含了一场考试的分数。Score 是一个有两位小数点的浮点值。

以下是表结构和数据：

Create table Scores (

id int(11) NOT NULL AUTO_INCREMENT,

score DECIMAL(3,2),

PRIMARY KEY (`id`)

);

查询并对分数进行排序。排名按以下规则计算:

分数应按从高到低排列。

如果两个分数相等，那么两个分数的排名应该相同。

在排名相同的分数后，排名数应该是一个连续的整数。

分数表:

id	score
1	3.5
2	3.65
3	4.0
4	3.85
5	4.00
6	3.65

根据以上示例数据计算出排名,写出SQL语句不能使用dense_rank和over开窗函数：

SQL：
SET @rank = 0;
SET @prev_score = NULL;
SELECT
id,
score,
@rank := IF(@prev_score = score, @rank, @rank + 1) AS rank,
@prev_score := score
FROM Scores
ORDER BY score DESC;

计算结果截图：

sqlcomment

上一篇：SQL增删改查语句
下一篇：mysql创建表时如何给字段添加注释?其实就用的这个关键字

大数据sql面试题整理

相关推荐

在.net core中使用nginx做负载均衡

React 18 超全升级指南

LTUI v1.7 发布，一个基于 Lua 的跨平台字符终端 UI 界面库

生成对抗网络(GAN)的半监督学习

《若依ruoyi》第二十五章:Spring boot 上传下载封装详解二

QT进阶之路 : 布局详解

如何在 Element UI 中使用栅格布局实现响应式设计?

IDEA 中 Jetty 的配置操作手册 idea jfinal

如何在Dify平台上创建智能Agent:一步步教你实现超级智能体搭建

k8s自动化运维三