已run的羚羊很纯真

分类

/ 注册

全部文章（共20篇）

题解 | 窗口函数sum，count，case when

写完要长脑子了。。。。。 select author,`date`,fans_growth_rate,sum(total)over(partition by author order by `date`) total_fans from (select distinct author,`date`...

2024-11-05

0 58

题解 | #实习广场投递简历分析(二)#

select job,date_format(`date`,'%Y-%m') mon,sum(num)cnt from resume_info where year(`date`)=2025 group by date_format(`date`,'%Y-%m'),job order by date...

2024-11-02

0 55

题解 | #牛客的课程订单分析(七)#

select (case when name is null then 'GroupBuy' else name end) source,count(*) cnt from order_info o left join client c on o.client_id=c.id where user_...

2024-11-02

0 56

题解 | #牛客的课程订单分析(五)#

select user_id,`date` first_buy_date,second_buy_date,cnt from (select user_id,`date`,r,lead(`date`,1)over(partition by user_id order by `date`) second...

2024-11-02

0 38

题解 | #考试分数(五)#笨办法

select id,job,score,r t_rank from( select g.id, g.job, g.score, row_number()over(partition by job order by score desc) r, start, `end` from grade g le...

2024-11-01

1 73

题解 | 使用case when语句算中位数范围

select job, (case when max(r)%2=1 then round((max(r)+1)/2,0) else round(max(r)/2,0) end) start, (case when max(r)%2=1 then round((max(r)+1)/2,0) else ...

2024-11-01

1 80

题解 | #考试分数(三)#

select id, name, score from (select g.id,name,score,dense_rank()over(partition by language_id order by score desc) r from grade g left join language l...

2024-11-01

1 55

题解 | #牛客每个人最近的登录日期(六)#

select name, l.date, sum(`number`)over(partition by l.user_id order by l.date) ps_num from login l join passing_number p on l.user_id = p.user_id an...

2024-11-01

1 43

题解 | 思路：当天登陆日期是否为第一次的登陆日期

# 笨办法 select t1.date, sum(if(t1.date = t2.date,1,0)) as new from login t1 left join (select user_id, min(`date`) `date` from login group by user_id) t...

2024-11-01

1 64

题解 | #牛客每个人最近的登录日期(三)#

select round(avg(if(datediff(t3.date,t2.date)=1,1,0)),3) p # step6：判断第一次和第二次登陆之间间隔的天数是否为1，为1则记为1，否则记为0，使用if函数不会忽略none值，直接avg求概率。 from (select * from...

2024-11-01

1 38