正文
一、每门课程问题
用一条 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 name FROM xuesheng WHERE 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 -- 查出了最大连续登陆，where>=7,即7天连续 FROM ( -- group by相同日期 SELECT user_id, symbol_date, COUNT(*) AS count_val FROM ( -- 日期减rank，连续登陆的话，会得到相同日期 SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date FROM ( -- 打上rank标识 SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn FROM user_logging_format ) c ) d GROUP 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(id=lead_id and datediff(dd,lead_dd)=1 ,id, null ) ) as '1 日留存' , count( if(id=lead_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
