念_长征

分类

/ 注册

全部文章（共166篇）

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

# 使用平均值函数来计算正确率 select difficult_level, avg(result='right') correct_rate from user_profile u join question_practice_detail q on u.device_id=q.device_i...

2023-12-19

0 169

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

# 先提取性别并作为性别标记列【注意不要用male来相似，因为female里面也具有male】 select *, case when profile like '%female' then 'female' else 'male' end as gender from user_submit # ...

2023-12-19

0 160

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

select substr(date, 9, 2) day, count(*) question_cnt from question_practice_detail where date like '2021-08%' group by date;

2023-12-19

0 174

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

# 添加年龄分组列 select *, case when age<20 then '20岁以下' when age between 20 and 24 then '20-24岁' when age>=25 then '25岁及以上' else '其他' end as age_cut f...

2023-12-19

0 150

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

# 子查询--在user_profile添加分组列，为各行添加年龄分组标记 select *, case when age<25 or age is null then '25岁以下' else '25岁及以上' end as age_cut from user_profile # 完整查询...

2023-12-19

0 150

题解 | #统计每个用户的平均刷题数#

# 查询参与了答题的山东大学在不同难度下的答题总数和答题用户数 select university, difficult_level, round(count(*)/count(distinct u.device_id),4) avg_answer_cnt from user_profile u ...

2023-12-19

0 163

题解 | #统计每个学校各难度的用户平均刷题数#

# 分组查询每个学校每种题目难度下的答题用户数量、答题数量并计算刷题率 select university, difficult_level, round(count(*)/count(distinct u.device_id),4) from user_profile u join questio...

2023-12-19

0 156

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

select university, count(*)/count(distinct u.device_id) from user_profile u join question_practice_detail q on u.device_id=q.device_id group by univer...

2023-12-19

0 152

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

select q.device_id, question_id, result from question_practice_detail q join user_profile u on q.device_id=u.device_id where u.university='浙江大学' order...

2023-12-19

0 169

题解 | #分组排序练习题#

select university, avg(question_cnt) avg_question_cnt from user_profile group by university order by avg_question_cnt;

2023-12-17

0 163