那星阿伦

分类

/ 注册

全部文章（共15篇）

题解 | #找出每个学校GPA最低的同学#

# # with子句 + 一步步拆解/ # with # temp as( # #对每个学校的gpa排序 # select distinct # device_id, # university, # ...

2024-12-02

0 37

题解 | #截取出年龄#

# select substring_index(substring_index(profile,',',-2),',',1) as age, # count(distinct device_id) as number # from user_submit # group by age; age升序...

2024-12-02

0 27

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

# select device_id,trim('http:/url/' from blog_url) as user_name # from user_submit; select -- 替换法 replace(string, '被替换部分','替换后的结果') -- device_id, ...

2024-12-02

0 41

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

select ( case when profile like '%,male' then 'male' else 'female' end ) as gender,count(device_id) from user_submit group by gender; # 如果cou...

2024-11-29

0 35

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

# select count(distinct q2.device_id)/count(distinct q1.device_id) as avg_ret # from question_practice_detail q1 # left join question_practice_detail ...

2024-11-29

0 33

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

select day(date) as day, count(question_id) as question_cnt from question_practice_detail where date like '%2021-08%' group by day; day(date)...

2024-11-28

0 33

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

select device_id,gender, ( case when age<20 then '20岁以下' when age<=24 then '20-24岁' when age>=25 then '25岁及以上' ...

2024-11-28

0 37

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

# select if(age>=25,'25岁及以上','25岁以下') as age_cnt, count(*) as number # from user_profile # group by age_cnt; select (case when age>=25...

2024-11-28

0 34

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

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

2024-11-28

0 24

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

select university, difficult_level, count(difficult_level)/count(distinct t1.device_id) as avg_answer_cnt from question_practi...

2024-11-21

0 35