Hjwwwww_

分类

/ 注册

全部文章（共42篇）

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

substring_index(profile,",",-1) 截取最后一个逗号后字符串 count * 也可以需要记得分组 SELECT substring_index(profile,",",-1) gender, COUNT(device_id) number FROM user_submi...

2023-01-06

0 185

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

DATE_ADD(q1.date, interval 1 day)计算出“次日” SELECT count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret FROM question_pra...

2023-01-06

0 210

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

方法一：substr函数，从第1位开始取7个数 SELECT day(date) day, count(question_id) question_cnt FROM question_practice_detail where substr(date,1,7) = "2021-08" GROUP...

2023-01-06

0 213

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

when..then..要注意不要加标点符号 select device_id,gender, case when age<20 then "20岁以下" when age between 20 and 24 then "20-24岁" when age > 24 then "25岁及以...

2023-01-06

0 222

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

if用法 SELECT if(age is null or age <25,"25岁以下", "25岁及以上") age_cut, count(device_id) number from user_profile group by age_cut

2023-01-06

0 205

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

case 用法 SELECT case when age is null or age <25 then "25岁以下" when age >= 25 then "25岁及以上" end as age_cut, count(device_id) number from user_...

2023-01-06

0 243

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

方法一 SELECT university, difficult_level, COUNT(q_p.question_id) / COUNT(DISTINCT q_p.device_id) avg_answer_cnt FROM user_profile u, qu...

2023-01-06

0 224

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

方法①SELECT university, difficult_level, COUNT(q_p.question_id) / COUNT(DISTINCT q_...

2023-01-06

7 960

题解 | #分组计算练习题#

select gender,university, count(device_id) as user_num, round(avg(active_days_within_30),1) as avg_active_day, round(avg(question_cnt),1) as avg_quest...

2023-01-06

0 176

题解 | #操作符混合运用#

select device_id,gender,age,university,gpa from user_profile where university = "山东大学" AND gpa >3.5 OR university = "复旦大学" AND gpa >3.8

2023-01-05

13 2617