荆天明201908291552707
荆天明201908291552707
全部文章
分类
题解(31)
归档
标签
去牛客网
登录
/
注册
荆天明201908291552707的博客
全部文章
(共33篇)
题解 | #SQL类别高难度试卷得分的截断平均值#
select ei.tag, ei.difficulty, round( (sum(er.score) - min(er.score) - max(er.score)) / (count(*) - 2), 1 ) from exam_record er inn...
Mysql
2022-06-18
0
165
题解 | #统计活跃间隔对用户分级结果#
SELECT M.us user_grade, round( count( M.us ) / ( SELECT count( DISTINCT uid ) FROM tb_user_log ), 2 ) ratio FROM ( SELECT ( CASE WHEN...
Mysql
2022-06-17
0
157
题解 | #国庆期间每类视频点赞量和转发量#
窗口函数+rows preceing操作 select M.t0, M.t1, M.t2, M.t3 from ( select N.b t0, N.a t1, sum(N.c) OVER( PARTITION BY N...
Mysql
2022-06-16
0
161
题解 | #工作日各时段叫车量、等待接单时间和调度时间#
case when select N.name, count(N.name), round( sum(TIMESTAMPDIFF(second, N.event_time, N.order_time)) / count(N.name) / 60, 1 ), rou...
Mysql
2022-06-15
0
206
题解 | #牛客直播各科目出勤率#
分步查询然后相除即可 select ct.course_id, ct.course_name, round(M.mn * 100 / N.sn, 2) from course_tb ct left join ( select bt.course_id, ...
Mysql
2022-06-14
0
159
题解 | #某宝店铺动销率与售罄率#
select pt.style_id, round(sum(N.sm) * 100 / (sum(pt.inventory) - sum(N.sm)),2), round(sum(N.p) * 100 / sum(pt.tag_price * pt.inventory), 2) from...
Mysql
2022-06-13
0
167
题解 | #某宝店铺连续2天及以上购物的用户及其对应的天数#
连续天数技巧dense_rank()加date_sub(N.sales_date, INTERVAL inv DAY)嘿嘿 select N.user_id, count(N.user_id) from ( select st.sales_date, st...
Mysql
2022-06-12
0
200
题解 | #某宝店铺折扣率#
基础的sql聚合函数 select round( sum(st.sales_price) * 100 / sum(pt.tag_price * st.sales_num) , 2 ) discount_rate from sales_tb st left join p...
Mysql
2022-06-12
0
197
题解 | #每天的日活数及新用户占比#
select NN.tm, NN.n, round(if(TT.mtm is null, 0, TT.t / NN.n), 2) from ( select T.mtm, count(T.uid) t from ( ...
Mysql
2022-06-11
0
197
题解 | #每天的日活数及新用户占比#
分两步第一步求出每天的新用户,第二步求出每天的活跃用户,两个临时表合并操作即可 select NN.tm,NN.n,round(if(TT.mtm is null,0,TT.t / NN.n), 2) from (select T.mtm,count(T.uid) t from (select ui...
Mysql
2022-06-11
0
170
首页
上一页
1
2
3
4
下一页
末页