免费建立网站空间,重庆快建网站,住房和城乡建设部门投诉电话,wordpress聚合插件#x1f345; 简介#xff1a;CSDN博客专家#x1f3c6;、信息技术智库公号作者✌ 简历模板、PPT模板、学习资料、面试题库、技术互助【关注我#xff0c;都给你】 #x1f345; 欢迎点赞 #x1f44d; 收藏 ⭐留言 #x1f4dd; 一、每门课程问题
用一条 SQL 语句… 简介CSDN博客专家、信息技术智库公号作者✌ 简历模板、PPT模板、学习资料、面试题库、技术互助【关注我都给你】 欢迎点赞 收藏 ⭐留言 一、每门课程问题
用一条 SQL 语句查询 学生表每门课都大于 80 分的学生姓名。
解决办法一 having
思路如果最小的课程都大于80分那他所有的课程分数肯定都大于80分
代码实现
SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score) 80解决办法二not in
可以用反向思维先查询出表里面有小于 80 分的 name,然后用 not in 去除掉
代码实现
SELECT DISTINCT name
FROM xuesheng
WHERE name NOT IN(SELECT DISTINCT nameFROM xueshengWHERE score 80);二、topN 问题
案例查询各科成绩前两名的记录
代码实现
row_number() over( partition by 课程 order by 成绩 desc) as rank
...
where rank 2 -- 前两名三、连续问题7 天连续登陆
实现思路: 因为每天用户登录次数可能不止一次所以需要先将用户每天的登录日期去重。 再用row_number() over(partition by _ order by _)函数将用户id分组按照登陆时间进行排序。 计算登录日期减去第二步骤得到的结果值用户连续登陆情况下每次相减的结果都相同。 按照id和日期分组并求和筛选大于等于7的即为连续7天登陆的用户。
代码实现
SELECT user_id, MAX(count_val) AS max_count -- 查出了最大连续登陆where7,即7天连续
FROM (-- group by相同日期SELECT user_id, symbol_date, COUNT(*) AS count_valFROM (-- 日期减rank连续登陆的话会得到相同日期SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_dateFROM (-- 打上rank标识SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rnFROM user_logging_format) c) dGROUP BY user_id, symbol_date
) e
GROUP BY user_id;四、行转列问题
行转列可谓是经典中的经典了必须掌握了
6行变2行2列变3列 代码实现
SELECT SID,
MAX(case CID when 01 then score else 0 end) 01,
MAX(case CID when 02 then score else 0 end)02,
MAX(case CID when 03 then score else 0 end)03
FROM SC
GROUP BY SID五、留存问题
留存率是衡量用户质量的最重要指标之一因此计算用户留存率是用户数据分析中必须掌握的技能之一。同样也成为了面试经典sql之一。
留存率指标中通常需要关注次日留存、3日留存、7日留存和月留存。对新增用户而言需要关注更细颗粒度的数据也就是7日内每天的留存率。
代码实现
select
dd
, count( if(idlead_id and datediff(dd,lead_dd)1 ,id, null ) ) as 1 日留存
, count( if(idlead_id7 and datediff(dd,lead_dd7)7 ,id, null ) ) as 7 日留存
from
(
select
id, dd
, lead(dd,1) over(partition by id order by dd asc ) as lead_dd
, lead(id,1) over(partition by id order by dd asc ) as lead_id
, lead(dd,7) over(partition by id order by dd asc ) as lead_dd7
, lead(id,7) over(partition by id order by dd asc ) as lead_id7
from (
select slm as id, 2018-12-26 as dd
union all select slm as id, 2018-12-27 as dd
union all select slm as id, 2018-12-28 as dd
union all select hh as id, 2018-12-26 as dd
union all select hh as id, 2018-12-28 as dd ) aa
) bb添加文末公众号「信息技术智库」 硬核资料关注即可领取PPT模板、简历模板、行业经典书籍PDF。 技术互助技术群大佬指点迷津你的问题可能不是问题求资源在群里喊一声。 面试题库由技术群里的小伙伴们共同投稿热乎的大厂面试真题持续更新中。 知识体系含编程语言、算法、大数据生态圈组件Mysql、Hive、Spark、Flink、数据仓库、前端等。 技术交流、非诚勿扰
