2021-08-30

2.小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的id 是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

注意：如果学生人数是奇数，则不需要改变最后一个同学的座位。

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

写一段 SQL 语句查出 2013 年 10 月 1 日至 2013 年 10 月 3 日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

5.秋招A

1. 各部门工资最高的员工（难度：中等）

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

+----+-------+--------+--------------+

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

+----+-------+--------+--------------+

创建Department 表，包含公司所有部门的信息。

+----+----------+

| Id | Name |

+----+----------+

| 1 | IT |

| 2 | Sales |

+----+----------+

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

SELECT t1.name AS Department, t3.name AS Salary, t2.Salary

FROM department AS t1 INNER JOIN

(SELECT departmentid, MAX(salary) AS Salary FROM employee

GROUP BY departmentid) AS t2

ON t1.id=t2.departmentid

INNER JOIN employee AS t3

ON (t2.departmentid=t3.departmentid

AND t2.Salary=t3.salary);

2. 换座位（难度：中等）

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的id是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

请创建如下所示seat表：

示例：

+---------+---------+

| id | student |

+---------+---------+

| 1 | Abbot |

| 2 | Doris |

| 3 | Emerson |

| 4 | Green |

| 5 | Jeames |

+---------+---------+

假如数据输入的是上表，则输出结果如下：

+---------+---------+

| id | student |

+---------+---------+

| 1 | Doris |

| 2 | Abbot |

| 3 | Green |

| 4 | Emerson |

| 5 | Jeames |

+---------+---------+

注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT

(CASE WHEN MOD(id,2)!=0 AND a.num!=id THEN id+1

WHEN MOD(id,2)!=0 AND a.num=id THEN id

ELSE id-1 END) AS new_id, student

FROM seat, (SELECT COUNT(*) AS num FROM seat) AS a

ORDER BY new_id;

-->

3. 分数排名（难度：中等）

假设在某次期末考试中，二年级四个班的平均成绩分别是 93、93、93、91，请问可以实现几种排序结果？分别使用了什么函数？排序结果是怎样的？

+-------+-----------+

| class | score_avg |

+-------+-----------+

| 1 | 93 |

| 2 | 93 |

| 3 | 93 |

| 4 | 91 |

+-------+-----------+

空缺

4. 连续出现的数字（难度：中等）

编写一个 SQL 查询，查找所有至少连续出现三次的数字。

+----+-----+

| Id | Num |

+----+-----+

| 1 | 1 |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 1 |

| 6 | 2 |

| 7 | 2 |

+----+-----+

例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。

+-----------------+

| ConsecutiveNums |

+-----------------+

| 1 |

+-----------------+

SELECT a.Id, a.Num

FROM Logs AS a, Logs AS b, Logs AS c

WHERE 1 = a.Id-b.Id AND 1=b.Id-c.Id

AND 0 = a.Num-b.Num AND 0 = b.Num-c.Num

5. 树节点（难度：中等）

对于tree表，id是树节点的标识，p_id是其父节点的id。

+----+------+

| id | p_id |

+----+------+

| 1 | null |

| 2 | 1 |

| 3 | 1 |

| 4 | 2 |

| 5 | 2 |

+----+------+

每个节点都是以下三种类型中的一种：

Root: 如果节点是根节点。

Leaf: 如果节点是叶子节点。

Inner: 如果节点既不是根节点也不是叶子节点。

写一条查询语句打印节点id及对应的节点类型。按照节点id排序。上面例子的对应结果为：

+----+------+

| id | Type |

+----+------+

| 1 | Root |

| 2 | Inner|

| 3 | Leaf |

| 4 | Leaf |

| 5 | Leaf |

+----+------+

说明

节点’1’是根节点，因为它的父节点为NULL，有’2’和’3’两个子节点。

节点’2’是内部节点，因为它的父节点是’1’，有子节点’4’和’5’。

节点’3’，‘4’，'5’是叶子节点，因为它们有父节点但没有子节点。

下面是树的图形：

/ \

2 3

/ \

4 5

注意

如果一个树只有一个节点，只需要输出根节点属性。

-- 新增子节点，子节点为NULL，则为Leaf，父节点为NULL，则为Root

SELECT DISTINCT id,

(CASE WHEN p_id IS NULL THEN 'Root'

WHEN c_id IS NULL THEN 'Leaf'

ELSE 'Inner' END) AS Type

FROM

(SELECT t1.id, t1.p_id, t2.id AS c_id

FROM tree t1 LEFT JOIN tree t2

ON t1.id=t2.p_id) AS t;

6. 至少有5名直接下属的经理（难度：中等）

Employee表包含所有员工及其上级的信息。每位员工都有一个Id，并且还有一个对应主管的Id（ManagerId）。

+------+----------+-----------+----------+

+------+----------+-----------+----------+

|101 |John |A |null |

|102 |Dan |A |101 |

|103 |James |A |101 |

|104 |Amy |A |101 |

|105 |Anne |A |101 |

|106 |Ron |B |101 |

+------+----------+-----------+----------+

针对Employee表，写一条SQL语句找出有5个下属的主管。对于上面的表，结果应输出：

+-------+

| Name |

+-------+

| John |

+-------+

注意: 没有人向自己汇报。

SELECT e.name FROM employee AS e JOIN

(SELECT manager_id,

COUNT(*) AS num FROM employee GROUP BY manager_id HAVING num>=5) AS a

ON e.id=a.manager_id;

7. 查询回答率最高的问题（难度：中等）

求出survey_log表中回答率最高的问题，表格的字段有：uid, action, question_id, answer_id, q_num, timestamp。

uid是用户id；action的值为：“show”， “answer”， “skip”；当action是"answer"时，answer_id不为空，相反，当action是"show"和"skip"时为空（null）；q_num是问题的数字序号。

写一条sql语句找出回答率最高的问题。

举例：

输入

uidactionquestion_idanswer_idq_numtimestamp

5show285null1123

5answer2851241241124

5show369null2125

5skip369null2126

输出

survey_log

285

说明

问题285的回答率为1/1，然而问题369的回答率是0/1，所以输出是285。

注意：

最高回答率的意思是：同一个问题出现的次数中回答的比例。

SELECT question_id FROM

(SELECT question_id,

SUM(CASE WHEN action='answer' THEN 1 ELSE 0 END)/SUM(CASE WHEN action='show' THEN 1 ELSE 0 END) as ans_per

FROM survey_log

GROUP BY question_id

ORDER BY ans_per DESC) AS a

limit 1;

8. 各部门前3高工资的员工（难度：中等）

将项目7中的employee表清空，重新插入以下数据（其实是多插入5,6两行）：

+----+-------+--------+--------------+

+----+-------+--------+--------------+

| 1 | Joe | 70000 | 1 |

| 2 | Henry | 80000 | 2 |

| 3 | Sam | 60000 | 2 |

| 4 | Max | 90000 | 1 |

| 5 | Janet | 69000 | 1 |

| 6 | Randy | 85000 | 1 |

+----+-------+--------+--------------+

编写一个 SQL 查询，找出每个部门工资前三高的员工。例如，根据上述给定的表格，查询结果应返回：

+------------+----------+--------+

| Department | Employee | Salary |

+------------+----------+--------+

| IT | Max | 90000 |

| IT | Randy | 85000 |

| IT | Joe | 70000 |

| Sales | Henry | 80000 |

| Sales | Sam | 60000 |

+------------+----------+--------+

此外，请考虑实现各部门前N高工资的员工功能。

SELECT department, employee, salary FROM

(SELECT d.deparment, e.employee, e.salary,

ROW_NUMBER() OVER(PARTITION BY d.department ORDER BY e.salary DESC) as ranking

FROM department AS d INNER JOIN employee AS e

ON d.departmentid=e.departmentID) AS a

WHERE ranking<=3;

9. 平面上最近距离（难度：困难）

point_2d表包含一个平面内一些点（超过两个）的坐标值（x，y）。

写一条查询语句求出这些点中的最短距离并保留2位小数。

|x | y |

|----|----|

| -1 | -1 |

| 0 | 0 |

| -1 | -2 |

最短距离是1，从点（-1，-1）到点（-1，-2）。所以输出结果为：

| shortest |

1.00

+--------+

|shortest|

+--------+

|1.00 |

+--------+

注意：所有点的最大距离小于10000。

SELECT MIN(ROUND(SQRT(POWER(t1.x-t2.x, 2)+POWER(t1.y-t2.y, 2)),2)) AS shortest

FROM point_2d AS t1 INNER JOIN point_2d AS t2

ON (t1.x, t1.y) != (t2.x, t2.y);

POWER(function, n)

ON (t1.x, t1.y)!=(t2.x, t2.y) ON可以接双元素条件

10. 行程和用户（难度：困难）

IdClient_IdDriver_IdCity_IdStatusRequest_at

11101completed2013-10-1

22111cancelled_by_driver2013-10-1

33126completed2013-10-1

44136cancelled_by_client2013-10-1

51101completed2013-10-2

62116completed2013-10-2

73126completed2013-10-2

821212completed2013-10-3

931012completed2013-10-3

1041312cancelled_by_driver2013-10-3

Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

+----------+--------+--------+

| Users_Id | Banned | Role |

+----------+--------+--------+

| 1 | No | client |

| 2 | Yes | client |

| 3 | No | client |

| 4 | No | client |

| 10 | No | driver |

| 11 | No | driver |

| 12 | No | driver |

| 13 | No | driver |

+----------+--------+--------+

写一段 SQL 语句查出2013年10月1日至2013年10月3日期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

+------------+-------------------+

| Day | Cancellation Rate |

+------------+-------------------+

| 2013-10-01 | 0.33 |

| 2013-10-02 | 0.00 |

| 2013-10-03 | 0.50 |

+------------+-------------------+

SELECT t.request_at,

ROUND(SUM(CASE WHEN status!='completed' THEN 1 ELSE 0 END)/COUNT(*),2) AS cancel_rate

FROM trips AS t INNER JOIN

(SELECT users_id FROM users WHERE banned='No' AND role='client') AS u

ON t.client_id=u.users_id

GROUP BY t.request_at;

秋招B\C

1. 行转列

假设 A B C 三位小朋友期末考试成绩如下所示：

+-----+-----------+------|

| name| subject |score |

+-----+-----------+------|

| A | chinese | 99 |

| A | math | 98 |

| A | english | 97 |

| B | chinese | 92 |

| B | math | 91 |

| B | english | 90 |

| C | chinese | 88 |

| C | math | 87 |

| C | english | 86 |

+-----+-----------+------|

请使用 SQL 代码将以上成绩转换为如下格式：

+-----+-----------+------|---------|

+-----+-----------+------|---------|

| A | 99 | 98 | 97 |

| B | 92 | 91 | 90 |

| C | 88 | 87 | 86 |

+-----+-----------+------|---------|

SELECT name,

MAX(CASE WHEN subject='chinese' THEN score ELSE 0 END) AS 'chinese',

MAX(CASE WHEN subject='math' THEN score ELSE 0 END) AS 'math',

MAX(CASE WHEN subject='english' THEN score ELSE 0 END) AS 'english'

FROM score

GROUP BY name;

2. 列转行

假设 A B C 三位小朋友期末考试成绩如下所示：

+-----+-----------+------|---------|

+-----+-----------+------|---------|

| A | 99 | 98 | 97 |

| B | 92 | 91 | 90 |

| C | 88 | 87 | 86 |

+-----+-----------+------|---------|

请使用 SQL 代码将以上成绩转换为如下格式：

+-----+-----------+------|

| name| subject |score |

+-----+-----------+------|

| A | chinese | 99 |

| A | math | 98 |

| A | english | 97 |

| B | chinese | 92 |

| B | math | 91 |

| B | english | 90 |

| C | chinese | 88 |

| C | math | 87 |

| C | english | 86 |

+-----+-----------+------|

SELECT name, chinese AS subject, score FROM score

UNION ALL

SELECT name, math AS subject, score FROM score

UNION ALL

SELECT name, english AS subject, score FROM score

ORDER BY name, subject;

3. 带货主播

假设，某平台2021年主播带货销售额日统计数据如下：

表名 anchor_sales

+-------------+------------+---------|

| anchor_name | date | sales |

+-------------+------------+---------|

| A | 20210101 | 40000 |

| B | 20210101 | 80000 |

| A | 20210102 | 10000 |

| C | 20210102 | 90000 |

| A | 20210103 | 7500 |

| C | 20210103 | 80000 |

+-------------+------------+---------|

定义：如果某主播的某日销售额占比达到该平台当日销售总额的 90% 及以上，则称该主播为明星主播，当天也称为明星主播日。

请使用 SQL 完成如下计算：

a. 2021年有多少个明星主播日？

b. 2021年有多少个明星主播？

SELECT COUNT(date) AS day_num, COUNT(DISTINCT anchor_name) AS anchor_num FROM

(SELECT date, anchor_name,

sales/SUM(sales) OVER(PARTITION BY date) AS sale_rate

FROM anchor_sales) AS a

WHERE sale_rate>=0.9;

4. 连续登陆

有用户表行为记录表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期）

计算2021年每个月，每个用户连续登录的最多天数

计算2021年每个月，连续2天都有登录的用户名单

计算2021年每个月，连续5天都有登录的用户数

空缺

5. hive 数据倾斜的产生原因及优化策略？

空缺

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 202,056评论 5赞 474
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 84,842评论 2赞 378
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 148,938评论 0赞 335
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,296评论 1赞 272
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,292评论 5赞 363
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,413评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 37,824评论 3赞 393
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,493评论 0赞 256
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,686评论 1赞 295
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,502评论 2赞 318
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,553评论 1赞 329
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,281评论 4赞 318
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,820评论 3赞 305
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,873评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,109评论 1赞 258
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 42,699评论 2赞 348
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,257评论 2赞 341

2021-08-30

推荐阅读更多精彩内容