牛客SQL刷题Day2

SQL27 查看不同年龄段的用户明细

select
    device_id,
    gender,
    case
        when age>=25 then '25岁及以上'
        when age>=20 then '20-24岁'
        when age<20 then '20岁以下'
        else '其他'


    end age_cut
from user_profile

 SQL28 计算用户8月每天的练题数量

方法1：

select date_format(re.date,'%d' ) as day ,count(*) as question_cnt

from question_practice_detail re where date_format(re.date,'%Y-%m')='2021-08' group by re.date;

注：

        (1)此方法需要掌握date_format(date,格式)函数。

        (2)此方法中对表进行简写，是一种优化行为。

方法2：

select day(date) day,count(date) question_cnt
from question_practice_detail where date>='2021-08-01' and date<='2021-08-31'
group by date





select day(date) day,count(date) question_cnt
from question_practice_detail where year(date)='2021' and month(date)=8
group by date



select
    day (date) day,
    count(date) question_cnt
from
    question_practice_detail
where
    date like "%2021-08%"
group by
    date

注：

      (1) 此方法需要掌握day(date)函数、month(date)函数、year(date)函数。

      (2)date like "%2021-08%"

方法3：

select
    substr(date,9,2) day,
    count(date) question_cnt
from
    question_practice_detail
where
    substr(date,1,7)="2021-08"
group by
    date

 注：

        substr(str,开始位置，截几个），截取包括开始位置。

方法4：

select day,count(question_id) as question_cnt
 
from(

select *,day(date) as day,month(date) as month 

from question_practice_detail
) as a 

where month=08
group by day;

        from里面得到的结果如下：

 注：

        month=8或者08都行。

SQL29 计算用户的平均次日留存率

 方法1：

        使用表连接的方式，做出此道题。

select   count(date1)/count(date_sum) as avr_ret
from(
(select distinct device_id,date as date_sum
from question_practice_detail ) dd
left join 
(select distinct device_id  ,date as date1
from question_practice_detail ) ff
on  dd.device_id=ff.device_id  and date_add(ff.date1,interval 1 day)=dd.date_sum
)

   

此题需要注意如下：

        （1）由于date计算是以天为单位，所以需要做去重。

        （2）正是基于(1)的条件，所以可以用date_add(ff.date1,interval 1 day)函数。

           

         （3）左连接 。

方法2（开窗函数）：

select avg(if (datediff(date2,date1)=1,1,0)) as avg_ret
from
  
(
select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
  
         from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
)  as ff

select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2

         from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date



#2138|2021-05-03|None
#2315|2021-08-13|2021-08-14
#2315|2021-08-14|2021-08-15
#2315|2021-08-15|None
#3214|2021-05-09|2021-06-15
#3214|2021-06-15|2021-08-15
#3214|2021-08-15|2021-08-16
#3214|2021-08-16|2021-08-18
#3214|2021-08-18|None
#6543|2021-08-13|None

踩坑1：

        distinct device_id date 和distinct device_id是不一样的。

踩坑2：

        lead(date)默认为排序后的前一个值，lead(date) over (partition by device_id order by date) as date2   date2是这个窗口函数的字段名。

踩坑3：

        sql中  if函数要加（）

踩坑4：

        多表连接产生一个新表，要加别名。

踩坑4：

        datediff()用法和lead()用法。

        lead()默认为前一个。