荔枝吱吱吱

分类

/ 注册

全部文章（共35篇）

题解|分组求和注意|每个创作者每月的涨粉率及截止当前总粉丝量

## 涨粉率 = (加粉量 - 掉粉量)/播放量 SELECT author, DATE_FORMAT(start_time,'%Y-%m') AS month, ROUND((SUM(CASE WHEN if_follow = 1 THEN 1 WHEN if_follo...

2023-08-21

0 338

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

# SELECT tb_user_video_log # ## uid，video_id,start_time,end_time, if_follow, # start_time, release_time, duration,tag,author,video_id ## 最近一个月：首先倒序排列...

2023-08-21

1 465

题解|百分比转换|#平均播放进度大于60%的视频类别#

# SELECT a.video_id,start_time,end_time,tag, # ROUND(AVG(TIMESTAMPDIFF(SECOND,start_time,end_time)/duration *100%), 2) AS avg_play_progress # FROM tb_...

2023-08-18

0 345

题解|窗口函数WHERE的位置|#各个视频的平均完播率#

## 先增加一列作为是否完播,然后计数：完播的数量/总数量 ## 结束时间-开始时间：TIMESTAMPDIFF(second,start_time,end_time) ## duration >= TIMESTAMPDIFF(second,start_time,end_time) ## IF...

2023-08-18

0 244

题解|拆分| #修复串列了的记录#

SELECT exam_id, substring_index(tag, ',', 1) AS tag, substring_index(substring_index(tag, ',', 2), ',', -1) AS difficulty, CAST( substring...

2023-08-18

0 208

题解|RLIKE正则表达式|筛选昵称规则和试卷规则的作答记录

## 用户信息表user_info,信息表examination_info,作答记录表exam_record ### SELECT * FROM user_info, examination_info, exam_record, avg_score, nick_name ## 昵称是'^牛客[0-9...

2023-08-18

0 215

题解|LEAD 时间差函数|#连续两次作答试卷的最大时间窗#

## lead窗口函数的用法： ## LEAD(字段名，n) OVER() 取值向后偏移n行，即向前推n位，后面有n位的位置空出 ## LAG(字段名，n) OVER() 取值向前偏移n行，即向后推n位，前面有n位的空出 ## LAG(字段名，n，x) OVER()取值向前偏移n行，并将空值填充为数...

2023-08-18

0 259

题解|第二快慢之差的求法 #第二快/慢之差大于试卷时长一半#

SELECT DISTINCT exam_id,duration,release_time FROM( SELECT exam_id,duration,release_time, SUM(CASE WHEN rank1 = 2 THEN time WHEN rank2 = 2 THE...

2023-08-17

0 268

题解|对表UNION后#筛选限定昵称成就值活跃日期的用户#

SELECT uid,nick_name,achievement FROM user_info WHERE achievement BETWEEN 1200 AND 2500 AND nick_name LIKE '牛客%号' AND uid IN ( SELECT uid FROM...

2023-08-17

0 185

题解|表的连接|#0级用户高难度试卷的平均用时和平均得分#

## 首先判断是否完成： ## 记录未完成和完成的用时：IF(submit_time IS NULL, duration, timestampdiff(minute,start_time,submit_time)) AS new_time ## 记录未完成和完成的分数：IF(score IS NUL...

2023-08-17

0 263