数据分析师 MySQL 笔记汇总【一】

数据分析师 MySQL面试题笔试题Leetcode题 笔记汇总

1，连续数 Leetcode 180题

这段代码可以直接记。遇到具体问题把Num改成你要计算的值就可以。

select distinct Num as ConsecutiveNums
from (
  select Num, 
    case 
      when @prev = Num then @count := @count + 1
      when (@prev := Num) is not null then @count := 1
    end as CNT
  from Logs, (select @prev := null,@count := null) as t
) as temp
where temp.CNT >= 3

2，Leetcode 185 部门工资前三员工<判断并列>

-- 第一步 rank() over() as ranking
-- 第二步 取出第三名的salary
-- 第三步 只取大于第三名的salary的
-- 第四步 join获取department.name

with temp as (
    select s.*, rank() over(partition by departmentid order by s.salary desc) as ranking
    from(
        select distinct salary, departmentid
        from employee
    ) as s
)

select d.name as Department, e1.name as Employee, e1.salary as Salary
from department as d
join (
    select e.id, e.name, e.salary, e.departmentid, a.top3_salary
    from employee as e
    join (
        select temp.departmentid, min(temp.salary) as top3_salary
        from temp
        where temp.ranking <= 3
        group by temp.departmentid
    ) as a
    on e.departmentid = a.departmentid
) as e1
on d.id = e1.departmentid
where e1.salary >= e1.top3_salary

3，topN

不要求分类的话，就活用limit N,1

要求分类的话，

select *
from (
   select *, 
          row_number() over (partition by 要分组的列名
                       order by 要排序的列名 desc) as ranking
   from 表名) as a
where ranking <= N

4，连续登录

先用这个day - ranking(由row_number()算出来的)列

CREATE TABLE IF NOT EXISTS `loadrecord` (
  `uid` int(6) unsigned NOT NULL,
  `loadtime` varchar(200) NOT NULL,
  PRIMARY KEY (`uid`,`loadtime`)
) DEFAULT CHARSET=utf8;
INSERT INTO `loadrecord` (`uid`, `loadtime`) VALUES
  ('201', '2017/1/1'),
  ('201', '2017/1/2'),
  ('202', '2017/1/2'),
  ('202', '2017/1/3'),
  ('203', '2017/1/3'),
  ('201', '2017/1/4'),
  ('202', '2017/1/4'),
  ('201', '2017/1/5'),
  ('202', '2017/1/5'),
  ('201', '2017/1/6'),
  ('203', '2017/1/6'),
  ('203', '2017/1/7');

select uid, max(cnt) as cnt
from (
select uid, dd, count(dd) as cnt
from(
	select *,day(loadtime) - row_number() over(PARTITION by uid order by uid,loadtime) as dd
	from loadrecord
) as temp
group by temp.uid,temp.dd
) as cnt
GROUP BY uid

5，留存率

select *,
concat(round(100*次日留存用户/日活跃用户数,2),'%')  次日留存率,
concat(round(100*三日留存用户/日活跃用户数,2),'%')  三日留存率,
concat(round(100*七日留存用户/日活跃用户数,2),'%')  七日留存率
from (
select
        a.log_day 日期,
count(distinct user_id)  日活跃用户数,
count(distinct concat(b.device_id,b.app_id))  次日留存用户,
count(distinct concat(c.device_id,c.app_id))  三日留存用户,
count(distinct concat(d.device_id,d.app_id))  七日留存用户
from user_log a 
left join user_log b on concat(a.device_id,a.app_id) = concat(b.device_id,b.app_id) and b.log_day = a.log_day + 1
left join user_log c on concat(a.device_id,a.app_id) = concat(c.device_id,c.app_id) and c.log_day = a.log_day + 3
left join user_log d on concat(a.device_id,a.app_id) = concat(d.device_id,d.app_id) and d.log_day = a.log_day + 7
group by a.log_day
) p;

select *,
concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,
concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,
concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率 
from
(  
select
  c.log_day 日期,
count(distinct concat(c.device_id,c.app_id))  日新增用户数,
count(distinct concat(d.device_id,d.app_id))  次日留存用户,
count(distinct concat(e.device_id,e.app_id))  三日留存用户,
count(distinct concat(f.device_id,f.app_id))  七日留存用户
from
  (
select a.*
from user_log a 
left join user_log b on concat(a.device_id,a.app_id) = concat(b.device_id,b.app_id) and b.log_day < a.log_day
where b.log_day is null
  ) c
left join user_log d on concat(c.device_id,c.app_id) = concat(d.device_id,d.app_id) and  d.log_day = c.log_day + 1
left join user_log e on concat(c.device_id,c.app_id) = concat(e.device_id,e.app_id) and  e.log_day = c.log_day + 3
left join user_log f on concat(c.device_id,c.app_id) = concat(f.device_id,f.app_id) and  f.log_day = c.log_day + 7
group by c.log_day
) p;

6，Leetcode 197题 上升的气温

select a.id
from weather a
left join weather b
on DATEDIFF(a.recorddate, b.recorddate) = 1
where a.temperature > b.temperature

这种类型的题，如何连接才是关键，left join的关键。