※食用指南：文章内容为牛客网《非技术快速入门》39道题重点笔记，用于重复思考错题，加深印象。
练习传送门：SQL非技术快速入门39题
目录：
SQL13 Where in 和Not in
❓找到学校为北大、复旦和山大的同学进行调研
非用运算符也可以，但太麻烦
SELECT device_id,gender,age,university,gpa
FROM user_profile
WHERE university IN ('北京大学','复旦大学','山东大学')
WHERE university='北京大学' OR university='复旦大学' OR university='山东大学SQL19 分组过滤练习题
平均发贴数低于5的学校或平均回帖数小于20的学校数值函数：ROUND取整
数值函数：ROUND取整：
聚合函数：使用HAVING子句，可以在分组行之后筛选数据
※WHERE在GROUP BY前筛选数据，HAVING在GROUP BY后筛选数据
SELECT university,
COUNT(question_id) /
COUNT(DISTINCT q.device_id) AS avg_answer_cnt
FROM question_practice_detail q
JOIN user_profile u ON q.device_id = u.device_id
GROUP BY universitySQL20 分组排序练习题
关键词“每”、“各”,可以判断结果集是需要进行分组
※谨记子句的顺序，GROUP BY永远在SELECTF、FROM后面，ORDER BY前面
SELECT university,
AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY university
ORDER BY avg_question_cntSQL22 统计每个学校的答过题的用户的平均答题数
❓每个学校答过题的用户平均答题数量情况
平均答题数量：总答题数量/总人数
DISTINCT：去除重复答题的用户
SELECT university,
COUNT(question_id) /
COUNT(DISTINCT q.device_id) AS avg_answer_cnt
FROM question_practice_detail q
JOIN user_profile u ON q.device_id = u.device_id
GROUP BY universitySQL24 统计每个用户的平均刷题数
❓参加了答题的山东大学的用户在不同难度下的平均答题题目数
INNER JOIN——多表检索数据
SELECT university,
difficult_level,
ROUND(COUNT(qpd.question_id)/
COUNT(DISTINCT qpd.device_id),4) AS avg_answer_cnt
FROM user_profile u
INNER JOIN question_practice_detail qpd ON u.device_id = qpd.device_id
INNER JOIN question_detail qd ON qd.question_id = qpd.question_id
WHERE u.university='山东大学'
GROUP BY qd.difficult_levelSQL25 查找山东大学或者性别为男生的信息
❓分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，结果不去重
结果不去重 UNION ALL，去重UNION
SELECT device_id,
gender,
age,
gpa
FROM user_profile
WHERE university='山东大学'
UNION ALL
SELECT device_id,
gender,
age,
gpa
FROM user_profile
WHERE gender='male'SQL27 查看不同年龄段的用户明细
❓将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况
IF函数：只允许单一的测试表达式
CASE函数：可以针对每个测试表达式返回不同值
可以选择性加上ELSE子句，如果以上的条件没有一个真，则返回后面输入的条件Future
最后需要用END关键字关闭CASE语句块
SELECT device_id,
gender,
CASE WHEN age<20 THEN '20岁以下'
WHEN age BETWEEN 20 AND 24 THEN '20-24岁'
WHEN age >= 25 THEN '25岁及以上'
WHEN age IS NULL THEN '其他'
#ELSE '其他'
END AS age_cut
FROM user_profileSQL29 计算用户的平均次日留存率
❓用户在某天刷题后第二天还会再来刷题的平均概率
DATE_ADD：给日期时间值添加日期成分
在当前日期时间上增加一天
第一个参数传递当前日期时间，第二个参数写一段表达式（INTERVAL 1 DAY）
次日留存率可以这样表示：
次日留存率=去重的数据表中符合次日留存的条目数目/
去重的数据表中所有条目数目
SELECT
COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret
FROM
(SELECT DISTINCT device_id, date
FROM question_practice_detail)as q1
LEFT JOIN
(SELECT DISTINCT device_id, date
FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND
q2.date = DATE_ADD(q1.date, INTERVAL 1 DAY)SQL30 统计每种性别的人数
❓统计每个性别的用户分别有多少参赛者
SELECT CASE WHEN profile LIKE '%female' THEN 'female'
ELSE 'male'
END AS gender,
COUNT(*) number
FROM user_submit
GROUP BY genderSQL32 截取出年龄
❓统计每个年龄的用户分别有多少参赛者
数值函数：SUBSTR (SUBSTRING，字符截取函数)：一个字符串中任何位置的字符
第二个参数时起始位置，第三个参数是长度
第三个参数如果不写，返回起始位置算到字符串最后的所有字符
SUBSTRING_INDEX(（）函数：用来截取字符串
例子：165cm,45kg,26,female
①SUBSTRING_INDEX(profile,',',3)
正数，从左往右算第三个逗号，获取到165cm,45kg,26
②SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',3),',',-1)
负数，从右往左算第一个逗号，获取到26
SELECT SUBSTRING_INDEX(
SUBSTRING_INDEX(profile,',',3),',',-1) AS age,
COUNT(*) AS number
FROM user_submit
GROUP BY ageSQL33 找出每个学校GPA最低的同学
❓找到每个学校gpa最低的同学
⚠gpa最低，看似MIN(gpa)，但是是每个学校里的最低，不是全部最低
MIN(gpa)无法对应device_id，也就无法获取到最低gpa对应device_id
窗口函数：
RANK：用来记录排序的函数
PARTITION BY：设定排序的对象范围（根据什么分组）
ORDER BY：指定哪一列、何种顺序排序（默认升序，降序DECS）
例如：根据不同种类（product_type），按照销售单价（sale_price）从低到高排序
PARTITION BY横向对表进行分组；ORDER BY决定纵向排序的规则
RANK() OVER (PARTITION BY university ORDER BY gpa) AS ranking
找最大最小值对应数据的其他字段信息，联系窗口函数取where up.ranking=1
SELECT device_id,
university,
gpa
FROM (SELECT device_id,
university,
gpa,
RANK() OVER (PARTITION BY university ORDER BY gpa) AS ranking
FROM user_profile) up
WHERE up.ranking=1;SQL34 统计复旦用户8月练题情况
❓复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，对于在8月份没有练习过的用户，答题数结果返回0
COUNT计数非空个数，SUM只求和；
如果这里要用COUNT：COUNT(IF(qpd.result='right', 1, NULL))
USING只能用于列名称完全一致的数据
SELECT device_id,
university,
COUNT(question_id) AS question_cnt,
SUM(IF(result='right',1,0)) AS right_question_cnt
FROM user_profile u
LEFT JOIN question_practice_detail q USING (device_id)
WHERE university ='复旦大学' AND
(MONTH(date)=8 or date IS NULL)
GROUP BY device_id
SQL35 浙大不同难度题目的正确率
❓浙江大学的用户在不同难度题目下答题的正确率情况
AVG、SUM、COUNT三种方法都可获得正确率
SELECT difficult_level,
AVG(IF(result='right',1,0)) AS correct_rate
#SUM(IF(q.result='right', 1, 0)) /
COUNT(q.question_id) AS correct_rate
#COUNT(IF(qpd.result='right', 1, NULL)) /
COUNT(q.question_id) AS correct_rate
FROM user_profile u
INNER JOIN question_practice_detail q USING(device_id)
INNER JOIN question_detail qd USING(question_id)
WHERE u.university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate
————END