玉树叶

分类

未归档(1) 题解(71)

/ 注册

TA的专栏 42篇文章 0人订阅

一天一个Mysql

42篇文章 2861人学习

全部文章（共66篇）

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量# 来自专栏

SELECT author, month, round(diff / play,3) fans_growth_rate, sum(diff) over( partition by author order by month ) ...

Mysql

2021-12-14

1 824

题解 | #每类视频近一个月的转发量/率# 来自专栏

SELECT b.tag, SUM(if_retweet) retweet_cnt, ROUND(SUM(if_retweet)/COUNT(*), 3) retweet_rate FROM tb_user_video_log a LEFT JOIN ...

Mysql

2021-12-13

7 525

题解 | #各个视频的平均完播率# 来自专栏

SELECT u.video_id, round( (sum( if((TIMESTAMPDIFF(SECOND,start_time,end_time) >= duration),1,0) ) / COUNT(*...

Mysql

2021-12-11

1 390

题解 | #大小写混乱时的筛选统计# 来自专栏

通过代码 select a.tag tag, b.answer_cnt answer_cnt from( select tag, count(start_time) answer_cnt from exam_record e_r join ...

Mysql

2021-12-10

0 332

题解 | #各用户等级的不同得分表现占比# 来自专栏

通过代码1 WITH t as ( SELECT u_i.uid, level, register_time, tag, job, submit_time, score, max(e_r.score)over( part...

Mysql

2021-12-09

0 413

题解 | #根据指定记录是否存在输出不同情况# 来自专栏

通过代码 SELECT level, score_grade, round(lei/tol,3) ratio FROM (SELECT level, score_grade, count(*)over( partition by lev...

Mysql

2021-12-08

0 475

题解 | #根据指定记录是否存在输出不同情况# 来自专栏

通过代码1 with t as ( SELECT u_i.uid, level, tag, if(u_i.uid not in (SELECT uid FROM exam_record),0,incomplete_cnt) incomplete_cnt, ro...

Mysql

2021-12-07

3 575

题解 | #筛选昵称规则和试卷规则的作答记录# 来自专栏

通过代码 SELECT e_r.uid, e_r.exam_id, ROUND(avg(score),0) avg_score FROM user_info u_i LEFT JOIN exam_record e_r ON u_i.uid = e_r....

Mysql

2021-12-06

0 386

题解 | #筛选限定昵称成就值活跃日期的用户# 来自专栏

通过代码 SELECT u_i.uid, nick_name, achievement FROM exam_record e_r RIGHT JOIN user_info u_i ON e_r.uid = u_i.uid LEFT JOIN p...

Mysql

2021-12-05

4 1325

题解 | #0级用户高难度试卷的平均用时和平均得分# 来自专栏

通过代码 SELECT exam_record.uid, round(avg(IF(score is NULL,0,score)),0) avg_score, ROUND((sum(TIMESTAMPDIFF(MINUTE,start_time,submit_time)) ...

Mysql

2021-12-04

0 453