荆天明201908291552707

分类

题解(31)

/ 注册

全部文章（共31篇）

题解 | #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 165

题解 | #国庆期间每类视频点赞量和转发量#

窗口函数+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 215

题解 | #牛客直播各科目出勤率#

分步查询然后相除即可 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 166

题解 | #某宝店铺动销率与售罄率#

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 171

题解 | #某宝店铺连续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 204

题解 | #某宝店铺折扣率#

基础的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 202

题解 | #每天的日活数及新用户占比#

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 199

题解 | #每天的日活数及新用户占比#

分两步第一步求出每天的新用户，第二步求出每天的活跃用户，两个临时表合并操作即可 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 171