枫枫将近

分类

/ 注册

全部文章（共10篇）

题解 | #浙大不同难度题目的正确率#

select qd.difficult_level, sum(if(qpd.result='right',1,0))/count(qpd.question_id) as correct_rate from question_practice_detail qpd left join user_pr...

2024-07-14

0 118

题解 | #统计复旦用户8月练题情况#

SELECT up.device_id,'复旦大学' as university, count(question_id) as question_cnt, sum(if(qpb.result='right',1,0)) as right_question_cnt from user_profile ...

2024-07-14

0 127

题解 | #截取出年龄#

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1) AS age,count(1) as number FROM user_submit GROUP BY age 需求分析：截取字符串，但要截取两次，才能搞到对应的字符串（这次不...

2024-07-14

0 111

题解 | #提取博客URL中的用户名#

SELECT device_id,SUBSTRING_INDEX(blog_url,'/',-1) as user_name FROM user_submit 需求分析：截取字符串中指定内容解题思路:使用SUBSTRING_INDEX函数

2024-07-14

0 108

题解 | #统计每种性别的人数#

SELECT SUBSTRING_INDEX(profile,',',-1) as gender,count(1) as number FROM user_submit GROUP BY gender 需求分析:给出的表信息中，不满足第一范式，需要对信息进行拆分。从profile列中截取相应字符串。...

2024-07-14

0 115

题解 | #计算用户的平均次日留存率#

SELECT COUNT(q2.device_id)/COUNT(q1.device_id) avg_ret FROM (SELECT distinct device_id,date from question_practice_detail) as q1 LEFT JOIN (SELECT ...

2024-07-13

0 97

题解 | #计算用户8月每天的练题数量#

SELECT day(date) day,count(1) question_cnt from question_practice_detail where year(date)=2021 and month(date)=8 group by day(date) 需求分析:求日期及该日期下联系的题目...

2024-07-13

0 136

题解 | #查看不同年龄段的用户明细#

SELECT device_id,gender, CASE WHEN age < 20 THEN '20岁以下' WHEN age BETWEEN 20 AND 24 THEN '20-24岁' WHEN age >= 25 THEN '25岁及以上' ...

2024-07-13

0 107

题解 | #计算25岁以上和以下的用户数量#

select CASE WHEN age < 25 or age is null THEN '25岁以下' WHEN age >= 25 THEN '25岁及以上' end age_cut,count(1) AS number FROM user_profile group by age...

2024-07-13

0 147

题解 | #查找山东大学或者性别为男生的信息#

select device_id,gender,age,gpa from user_profile where university='山东大学' union all select device_id,gender,age,gpa from user_profile where gender='ma...

2024-07-13

0 132