牛客SQL练习小记

牛客SQL练习总结#

计算新用户的次日留存率#

• 太失败了！！一步一个坎，面对这个问题没有完整的思路，想到一半就无法继续了，只能看大佬们的sql获得启发
  

Copy
--思路
--这道题关键的两点，一个是标志出新用户，这个可以通过窗口函数min，根据uid分组，计算出首次登录时间
--另一个就是二次登陆日期，这个可以使用lead函数，根据uid分组，time排序
--还有一点需要注意的是题中有提到跨天用户，所以可以使用union将这个表拆成进入时间和离开时间重新拼在一起
--sql实现如下：
select time,round(sum(IF(DATEDIFF(nextime,time),1,0))/count(distinct uid),2) as rate
from
(select uid,time,min(time) over(partition by uid) as firstime,lead(time) over(partition by uid order by time) as nextime
from
(select uid,date(in_time) time
from tb_user_log
union
select uid,date(out_time) time
from tb_user_log)a)b
where time=firstime
group by time
order by time

• 补充一个函数中使用判断条件的知识点
  比如在sum()或者count()函数中计算的时候需要判断一下，可以用if(条件,T,F);条件会返回布尔值true or false,如果是true就取T值,反之取F
  这里如果我用sum的话,条件为真，就累加T值

统计活跃间隔对用户分级#

• 这道题做着还可以，一开始的时候没有什么思路，想着加很多附加列来标志，甚至想过使用窗口函数lead，
  后来在尝试的时候感觉这些都可以不用，只使用一个min或者max函数加上case的运用就可以解决。
  

Copy
--sql如下，这是我计算出正确结果后，重新整理思路精简后的结果
select yhdj as user_grade,round(count(*)/(select count(distinct uid) from tb_user_log),2) as ratio
from
(select uid,
case 
when count(*)>1 and min(dif)<7 then '忠实用户'
when count(*)=1 and min(dif)<7 then '新晋用户'
when min(dif)>=7 and min(dif)<30 then '沉睡用户'
when min(dif)>=30 then '流失用户'
end as yhdj
from
(select uid,datediff(today,intime) as dif
from
(select uid,date(in_time) as intime,(select max(in_time) from tb_user_log) as today
from tb_user_log) a)b
group by uid)c
group by yhdj
order by ratio desc

统计日活跃数以及新用户占比#

• 渐入佳境，随着做此类型的题目多了，就有思路了，可以套公式
  

Copy
--这里面用到了之前做题时的知识点，一个是将日志表根据进出时间一分为二然后用union连接去重，得到一个登录表
--另一个是聚合函数count()或者sum()带条件的用法，count(列名='**' or null),sum(if 列名='**'1,1,0)
--一般count用null，sum用0
--以下是sql实现
select time,count(*) as dau,round(count(rannk=1 or null)/count(*),2) as lv
from
(select uid,time,rank() over(partition by uid order by time ASC) as rannk
from
(select uid,date(in_time) as time 
from tb_user_log
union
select uid,date(out_time) as time 
from tb_user_log)a
order by time)b
group by time
order by time--默认升序ASC，DESC为降序

• 知识点:IFNULL(expression_1,expression_2); nvl(expression_1,expression_2)
  - 如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。 isnull适用于mysql,而nvl适用于oracle

Copy
---待注释
select uid,timem month,sum(jbs) as coin
from
(select uid,DATE_FORMAT(time,'%Y%m') as timem,
case 
when rt=3 then 3
when rt=0 then 7
else 1
end as jbs
from
(select uid,time,rank() over(partition by uid,raak order by time)%7 as rt
from
(select uid,time,diff,sum(diff) over(partition by uid order by time) as raak
from
(select uid,time,case when diff<2 then 0 else 1 end as diff
from
(select uid,time,case when datediff(time,nextday) is null then 0 else datediff(time,nextday) end as diff
from
(select uid,time,lag(time,1,null) over(partition by uid order by time) as nextday
from
(select uid,date(in_time) as time
from tb_user_log
where sign_in=1 and artical_id=0)a)b)c)d)e)f)g
group by uid,timem

Copy
select a.order_id as '订单号',
date(event_time) as '下单时间',
total_amount as '订单总额',
total_cnt as '订单数',
status,
b.product_id as '产品号',
price as '单价',
cnt as '数量',
shop_id as '店铺号',
in_price as '进价',
quantity as'进货数量',
release_time as '上架时间'
from tb_order_overall a
inner join tb_order_detail b on
a.order_id=b.order_id 
inner join tb_product_info c on
b.product_id=c.product_id
where date(event_time)>='2021-10-01'
and shop_id='901'

Copy
SELECT
	name 
FROM
	syscolumns 
WHERE
	ID = OBJECT_ID( '表名1' ) 
	AND name IN ( SELECT name FROM syscolumns WHERE ID = OBJECT_ID( '表名2' ) );
该语句可以查询表一中都有哪些字段存在于表二中