SQL66 牛客每个人最近的登录日期(一)
select user_id, max(date) as idfrom logingroup by user_idorder by user_id
select distinct user_id, first_value(date) over(partition by user_id order by date desc) as id from login order by user_id
解题思路：first_value() 窗口函数返回相对于窗口中第一行的指定列的值。
select t.user_id,t.datefrom( select user_id, date, row_number() over(partition by user_id order by date desc) as id from login) twhere t.id = 1order by t.user_id
解题思路：row_number() 窗口函数返回每行的行号，取每个分区的行号为1的数据。
SQL67 牛客每个人最近的登录日期(二)
select u.name,c.name,t.datefrom( select user_id,max(date) as date from login group by user_id order by date ) tjoin user u on t.user_id = u.idjoin login l on t.user_id = l.user_id and t.date = l.datejoin client c on l.client_id = c.idorder by u.name
select u.name,c.name,t.datefrom( select user_id,client_id,date,row_number() over(partition by user_id order by date desc)as date2 from login) tjoin user u on t.user_id = u.idjoin client c on t.client_id = c.idwhere t.date2 = 1order by u.name
解题思路：row_number() 窗口函数返回每行的行号，取每个分区的行号为1的数据。
SQL68 牛客每个人最近的登录日期(三)
select round(count(b.date) * 1.0 / count(*), 3) p from ( select user_id, min(date) as date from login group by user_id)aleft join login b on a.user_id = b.user_id and b.date = date_add(a.date, INTERVAL 1 DAY)
select round(count(distinct b.user_id) * 1.0 / count(distinct a.user_id), 3) pf rom login a left join login b on a.user_id = b.user_id and b.date = date_add(a.date, INTERVAL 1 DAY)
SQL69 牛客每个人最近的登录日期(四)
select distinct l.date,ifnull(b.new,0)from login lleft join ( select distinct date,count(date) as new from ( select user_id, min(date) date from login group by user_id ) a group by date order by date)b on l.date = b.date
解题思路：ifnull()判断是否为null
select a.date,sum(case when `rank` = 1 then 1 else 0 end) newfrom(select date, row_number() over(partition by user_id order by date) `rank`from login) agroup by a.date;
解题思路：窗口函数获取登录的次数，最早就是首次登录，然后分组对rank=1的求和即可。case when then else end可以理解为java的if-else if -else。
SQL70 牛客每个人最近的登录日期(五)
select date,ifnull(round(sum(tomm) * 1.0 / sum(new),3),0) as p from ( select user_id, date, min(date) over(partition by user_id), case when datediff(lead(date,1) over(partition by user_id),min(date) over(partition by user_id)) = 1 then 1 else 0 end as tomm, case when date = (min(date) over(partition by user_id)) then 1 else 0 end as new from login) as a group by date
解题思路：datediff()函数计算两个日期之间的天数，ag()与lead函数是跟偏移量相关的两个分析函数，过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤，该操作可代替表的自联接，且效率更高
