sql小题笔记

题目一：查询“6”课程比“2”课程成绩高的所有学生的学号。

-- 建表：
CREATE TABLE `sc` (
  `Sid` int(20) DEFAULT NULL,
  `Cid` int(20) DEFAULT NULL,
  `score` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL
);
-- 插入记录：
INSERT INTO `sc` VALUES ('1', '2', '80');
INSERT INTO `sc` VALUES ('2', '4', '69');
INSERT INTO `sc` VALUES ('3', '1', '90');
INSERT INTO `sc` VALUES ('4', '5', '89');
INSERT INTO `sc` VALUES ('5', '3', '67');
INSERT INTO `sc` VALUES ('1', '6', '95');

select * from sc;

查询结果：

-- 解题思路：将某一学生课程2和课程6的成绩拼接成一行，然后再判断两个课程成绩的高低
select distinct a.Sid from 
(select * from sc where Cid = 6) a
join  
(select * from sc where Cid = 2) b
on a.Sid = b.Sid
where a.score > b.score

题目二(使用hivesql进行操作)：

用户升级表src_user_upgrade，有三个字段:USER(string)用户名、Gamelevel(int)游戏等级、data_num(string)时间点，触发条件为用户首次进入游戏以及用户升级的时刻，计算用户经历每个等级段的平均时间(自然时间)，d_data格式为时间戳(2341234562)。
例如,aaa用户进入游戏为12点23秒，升到2级的时候已经是25分23秒了，经历25分钟;则经历等级1的平均时间是25分钟。最后输出单位为秒，没有顺序输出要求。

–样例输出结果:1(等级段)1500(平均时间/s)

-- 建表语句
CREATE TABLE `src_user_upgrade` (
  `user` string,
  `game_level` string,
  `date_num` string) ;

-- ----------------------------
INSERT INTO `src_user_upgrade` VALUES ('aaa', '1', '1571475261');
INSERT INTO `src_user_upgrade` VALUES ('aaa', '2', '1571478261');
INSERT INTO `src_user_upgrade` VALUES ('bbb', '1', '1571473261');
INSERT INTO `src_user_upgrade` VALUES ('aaa', '3', '1571488261');
INSERT INTO `src_user_upgrade` VALUES ('bbb', '2', '1571476261');
INSERT INTO `src_user_upgrade` VALUES ('bbb', '3', '1571493261');
INSERT INTO `src_user_upgrade` VALUES ('bbb', '4', '1571573261');

-- 解题思路：先使用窗口函数将下一级的时间取出来，然后再做差
select game_level,avg(s_every) from ( 
select `user`,game_level,date_num,time2,(time2-date_num) as s_every from 
(
select `user`,game_level,date_num,lead(date_num,1,date_num) over(partition by `user` order by game_level) as time2
from src_user_upgrade
) a
) b
group by game_level;

笔记：该表中的时间是秒级，所以如果要算两个时间戳之间差多少秒，直接相减即可；如果要计算相差多少分钟，则除以60即可；依次类推。