SQL非技术快速入门39题

※食用指南：文章内容为牛客网《非技术快速入门》39道题重点笔记，用于重复思考错题，加深印象。

练习传送门：SQL非技术快速入门39题

目录：

SQL13 Where in 和Not in

SQL19 分组过滤练习题

SQL20 分组排序练习题

SQL22 统计每个学校的答过题的用户的平均答题数

SQL24 统计每个用户的平均刷题数

SQL25 查找山东大学或者性别为男生的信息

SQL27 查看不同年龄段的用户明细

SQL29 计算用户的平均次日留存率

SQL30 统计每种性别的人数

SQL32 截取出年龄

SQL33 找出每个学校GPA最低的同学

SQL34 统计复旦用户8月练题情况

---

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 university

SQL20 分组排序练习题

关键词“每”、“各”,可以判断结果集是需要进行分组

※谨记子句的顺序，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_cnt

SQL22 统计每个学校的答过题的用户的平均答题数

❓每个学校答过题的用户平均答题数量情况

平均答题数量：总答题数量/总人数

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 university

SQL24 统计每个用户的平均刷题数

❓参加了答题的山东大学的用户在不同难度下的平均答题题目数

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_level

SQL25 查找山东大学或者性别为男生的信息

❓分别查看学校为山东大学或者性别为男性的用户的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_profile

SQL29 计算用户的平均次日留存率

❓用户在某天刷题后第二天还会再来刷题的平均概率

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 gender

SQL32 截取出年龄

❓统计每个年龄的用户分别有多少参赛者

数值函数：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 age

SQL33 找出每个学校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