GuaMiao

分类

/ 注册

全部文章（共89篇）

题解 | #截取出年龄#

select substring_index(substring_index(profile, ',', 3), ',', -1) as age, count(device_id) as number from user_submit group by age;

2023-07-31

1 329

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

select device_id, substring_index(blog_url, '/', -1) as user_name from user_submit;

2023-07-31

1 244

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

select gender, count(device_id) as number from ( select device_id, substring_index(profile, ',', -1) as gender from user_submit ) as b group b...

2023-07-31

1 277

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

select DAY(date), count(question_id) as question_cnt from question_practice_detail where date between '2021-08-01' and '2021-08-31' group by date;

2023-07-31

1 259

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

select device_id, gender, '20岁以下' as age_cut from user_profile where age < 20 union all select device_id, gender, '20-24岁' as age_cut from user_pro...

2023-07-31

1 289

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

select '25岁以下' as age_cnt, count(*)-sum(age >= 25) as number from user_profile union all select '25岁及以上' as age_cnt, count(device_id) as number fro...

2023-07-31

1 386

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

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

2023-07-31

1 255

题解 | #统计每个学校的答过题的用户的平均答题数#

select university, round(question_count/student_count, 4) as avg_answer_cnt from ( select count(distinct a.device_id) as student_count, count(a.q...

2023-07-31

1 232

题解 | #浙江大学用户题目回答情况#

select device_id, question_id, result from question_practice_detail where device_id in ( select device_id from user_profile where universi...

2023-07-30

1 187