sql典型例题及知识点

1.问题

解法

SELECT name, "english" AS subject, english AS score
FROM a1
UNION
SELECT name, "maths" AS subject, maths AS score
FROM a1
UNION
SELECT name, "music" AS subject, music AS score
FROM a1
ORDER BY name;

涉及知识点
1 添加列和值
Select ‘值‘ as 标题名
select ’该列下的的是值‘ as 该列的标题名
2.union
SQL UNION 操作符
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

#SQL UNION 语法
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

3.留存率问题
牛客网
牛客每个人最近的登录日期(三)
login表

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网

第4行表示user_id为3的用户在2020-10-12使用了客户端id为2的设备登录了牛客网

最后1行表示user_id为1的用户在2020-10-14使用了客户端id为2的设备登录了牛客网

请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

首先选出新登录用户

select user_id,min(date) date from login group by user_id;

一共4个 以及他们的登录日期

select * from
 (select user_id,min(date) date from login group by user_id)a
left join login b on a.user_id=b.user_id

用left join on a.user_id=b.user_id 就会是 左边 uid 和最小日期 右边就是 uid和所有日期 这时候用case when +datediff()=1函数 就可以求出次日登录的记录 （datediff可以等与其他值求七天 十五天的留存率）

select count(case when datediff(b.date,a.date)=1 then a.user_id else null end)/count(distinct a.user_id) 次日留存率
from (select user_id,min(date) date from login group by user_id)a 
left join login b on a.user_id=b.user_id

count(distinct a.user_id) 求出所有新用户 count(case when datediff(b.date,a.date)=1 then a.user_id else null end) 求出第二天留存的新用户 相除可得次日留存率

牛客每个人最近的登录日期(四)
login表

请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序
登录的新用户和登录日期

select user_id,min(date) date from login group by user_id

由于要考虑每个日期下的新用户个数
可以考虑用login原表作为左表 left join 新用户

select *
from login a left join (select user_id,min(date) date from login group by user_id)b 
on a.user_id=b.user_Id and 
a.date=b.date  

下一步就比较简单了 count + case when 判断即可 由于求的是升序 默认本身就是升序 也不需要order by

select a.date,count(case when a.user_id=b.user_id then 1 else null end ) 该日登录新用户数
from login a left join (select user_id,min(date) date from login group by user_id)b 
on a.user_id=b.user_Id and 
a.date=b.date  group by a.date

第二种解法 窗口函数

1.给所有登录打上标签

select  date,row_number()over(partition by user_id order by date ) rank_ from login 

通过group by date 然后选出标签为1的用户 - case when +count判断即可

select a.date,count(case when rank_=1 then 1 else null end) 该日登录新用户数  from(
select date,row_number()over(partition by user_id order by date ) rank_ from login)a
group by a.date