社畜张二狗
社畜张二狗
全部文章
题解
归档
标签
去牛客网
登录
/
注册
社畜张二狗的博客
新人练习中,请多指教
全部文章
/ 题解
(共18篇)
题解 | #连续签到领金币#
select uid,date_format(t,'%Y%m') as month, sum(case rn%7 when 3 then 3 when 0 then 7 else 1 end) as coin from(select uid,t,dt,row_number() over(partit...
Mysql
2022-02-11
0
267
题解 | #牛客直播开始时各直播间在线人数#
select a.course_id,a.course_name,count(distinct b.user_id) as online_pv from course_tb a left join attend_tb b on a.course_id=b.course_id where time(b...
Mysql
2022-01-18
0
240
题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
select user_id,max(cnt) from(select user_id, date_add(sales_date,interval -rn day) as t, count(*) as cnt from(select distinct user_id...
Mysql
2022-01-18
0
243
题解 | #大小写混乱时的筛选统计#
select c.tag,d.cnt from( select a.tag,count(b.start_time) as cnt from examination_info a,exam_record b where a.exam_id=b.exam_id group by 1) c, ( sel...
Mysql
2022-01-17
0
238
题解 | #各城市最大同时等车人数#
select city,max(uv) as max_wait_uv from(select city,t, sum(rn) over(partition by city,date(t) order by t,rn desc) as uv from(select city,event...
Mysql
2022-01-17
1
341
题解 | #根据指定记录是否存在输出不同情况#
select uid,level,cnt_incom_0,cnt_incom,rate_incom,cnt_t, max(cnt_incom_0) over() as max_cnt_incom_0 from( select distinct a.uid,a.level, count(if(a.le...
Mysql
2022-01-16
0
245
题解 | #筛选昵称规则和试卷规则的作答记录#
select a.uid,b.exam_id,round(avg(b.score),0) as avg_score from user_info a,exam_record b,examination_info c where a.uid=b.uid and b.exam_id=c.exam_id...
Mysql
2022-01-16
0
250
题解 | #每月及截止当月的答题情况#
select distinct m,m_uv,new_m_uv, max(new_m_uv) over(order by m asc) as max_new_m_uv, sum(new_m_uv) over(order by m asc) as cum_m_uv from( select disti...
Mysql
2022-01-16
0
228
题解 | #每份试卷每月作答数和截止当月的作答总数。#
select distinct exam_id,date_format(start_time,'%Y%m') as m, count(id) over(partition by exam_id, date_format(start_time,'%Y%m') ) as cnt_m, count(id...
Mysql
2022-01-16
0
237
题解 | #对试卷得分做min-max归一化#
select uid,exam_id,round(avg(new_score),0) as avg_new_score from( select *, case when cnt_score=1 then score else (score - min_score)/(max_score - min...
Mysql
2022-01-16
0
218
首页
上一页
1
2
下一页
末页