细雨噜噜噜

题解

/ 注册

全部文章 / 题解（共96篇）

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

SELECT tag, SUM(if_retweet), ROUND(SUM(if_retweet)/ COUNT(*),3) retweet_rate FROM tb_user_video_log,tb_video_info WHERE tb_user_video_log.video_id=tb...

Mysql

2021-12-03

23 1532

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

RLIKE后面可以跟正则表达式。正则表达式"^[0-9]+$"的意思： 1、字符^ 意义：表示匹配的字符必须在最前边。例如：^A不匹配“an A”中的‘A’，但匹配“An A”中最前面的‘A’。 2、字符$ 意义：与^类似，匹配最末的字符。例如：t$不匹配“eater”中的‘t’，但匹配“e...

Mysql

2021-12-02

65 1601

题解 | #大小写混乱时的筛选统计#

思路是把试卷作答数小于3的信息表t1和完整的信息表t2做连接。 SELECT t1.tag, t2.answer_cnt FROM (SELECT exam_record.exam_id, tag, COUNT(start_time) answer_cnt FROM examination_inf...

Mysql

2021-11-24

0 443

题解 | #每份试卷每月作答数和截止当月的作答总数。#

SELECT exam_id, DATE_FORMAT(start_time,'%Y%m') start_month, COUNT(DATE_FORMAT(start_time,'%Y%m')) month_cnt, SUM(COUNT(DATE_FORMAT(start_time,'%Y%m'...

Mysql

2021-11-24

0 443

题解 | #对试卷得分做min-max归一化#

法一： SELECT uid, exam_id, ROUND(AVG(norm_score),0) avg_new_score FROM (SELECT uid, exam_record.exam_id, IF(num=1, score, (score-min_score)/(max_score...

Mysql

2021-11-23

0 391

题解 | #每个6/7级用户活跃情况#

SELECT user_info.uid, COUNT(DISTINCT DATE_FORMAT(act_time,'%Y-%m')) act_month_total, COUNT(DISTINCT IF(YEAR(act_time)=2021, DATE_FOR...

Mysql

2021-11-22

0 409

题解 | #满足条件的用户的试卷完成数和题目练习数#

SELECT exam_record.uid, COUNT(DISTINCT IF(YEAR(exam_record.submit_time)=2021,exam_record.id,NULL)) exam_cnt, COUNT(DISTINCT IF(YEAR(practice_record.su...

Mysql

2021-11-22

0 376

题解 | #每个题目和每份试卷被作答的人数和次数#

法一： SELECT * FROM (SELECT exam_id AS tid, COUNT(DISTINCT exam_record.uid) uv, COUNT(*) pv FROM exam_record GROUP BY exam_id ORDER BY uv DESC, pv DESC...

Mysql

2021-11-21

98 3857

题解 | #试卷发布当天作答人数和平均分#

法一：直观地把三个表连起来，把条件写上去。 SELECT examination_info.exam_id, COUNT(DISTINCT exam_record.uid) uv, ROUND(AVG(score),1) avg_score FROM user_info, examination_...

Mysql

2021-11-19

0 443

题解 | #月均完成试卷数不小于3的用户爱作答的类别#

SELECT tag, COUNT(tag) tag_cnt FROM exam_record,examination_info WHERE exam_record.exam_id=examination_info.exam_id AND uid IN (SELECT uid FROM exam_r...

Mysql

2021-11-19

0 367