DAY03 SQL训练

吃掉那只青蛙！

Q1：union all与union的区别

• 分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

# select device_id,gender,age,gpa
# from user_profile
# where university = '山东大学' or gender = 'male'

# SELECT 
#     device_id, gender, age, gpa
# FROM 
#     user_profile
# WHERE 
#     university = '山东大学'
#     OR (gender = 'male' AND university <> '山东大学');


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'

Q2: case函数
1.简单

CASE 测试表达式
WHEN 简单表达式1 THEN 结果表达式1
WHEN 简单表达式2 THEN 结果表达式2 …
WHEN 简单表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

2.搜索

SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

记录写这道题错误，没有认真思考。

# select  age >= 25 as age_cut
# from user_profile
# where 

# union all 

# select  age < 25

select 
    case 
        when age < 25 or is null then '25岁以下'
        when age >= 25 then '25岁及以上'
    end as age_cut,count(*) as number
from 
user_profile 
group by age_cut


# SELECT 
#     CASE 
#         WHEN age < 25 OR age IS NULL THEN '25岁以下' 
#         WHEN age >= 25 THEN '25岁及以上'
#     END AS age_cut,
#     COUNT(*) AS number
# FROM 
#     user_profile
# GROUP BY 
#     age_cut;

Q3: 参考大佬的总结，关于聚合函数sum，case……when的使用。

-- 创建表  学生表
CREATE TABLE `edu_student` (
    `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
    `stu_name` VARCHAR (20) NOT NULL COMMENT '学生姓名',
    PRIMARY KEY (`stu_id`)
) COMMENT = '学生表' ENGINE = INNODB;

-- 课程表 
CREATE TABLE `edu_courses` (
    `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
    `course_name` VARCHAR (100) NOT NULL COMMENT '课程名称',
    PRIMARY KEY (`course_no`)
) COMMENT = '课程表' ENGINE = INNODB;

-- 成绩表
CREATE TABLE `edu_score` (
    `stu_id` VARCHAR (16) NOT NULL COMMENT '学号',
    `course_no` VARCHAR (20) NOT NULL COMMENT '课程编号',
    `scores` FLOAT NULL DEFAULT NULL COMMENT '得分',
    PRIMARY KEY (`stu_id`, `course_no`)
) COMMENT = '成绩表' ENGINE = INNODB;

-- 插入数据

-- 学生表数据

INSERT INTO edu_student (stu_id, stu_name)
VALUES
    ('1001', '盲僧'),
    ('1002', '赵信'),
    ('1003', '皇子'),
    ('1004', '寒冰'),
    ('1005', '蛮王'),
    ('1006', '狐狸');

-- 课程表数据 
INSERT INTO edu_courses (course_no, course_name)
VALUES
    ('C001', '大学语文'),
    ('C002', '新视野英语'),
    ('C003', '离散数学'),
    (
        'C004',
        '概率论与数理统计'
    ),
    ('C005', '线性代数'),
    ('C006', '高等数学');

-- 成绩表数据
INSERT INTO edu_score (stu_id, course_no, scores)
VALUES
    ('1001', 'C001', 67),   ('1002', 'C001', 68),   ('1003', 'C001', 69),   ('1004', 'C001', 70),   ('1005', 'C001', 71),
    ('1006', 'C001', 72),   ('1001', 'C002', 87),   ('1002', 'C002', 88),   ('1003', 'C002', 89),   ('1004', 'C002', 90),
    ('1005', 'C002', 91),   ('1006', 'C002', 92),   ('1001', 'C003', 83),   ('1002', 'C003', 84),   ('1003', 'C003', 85),
    ('1004', 'C003', 86),   ('1005', 'C003', 87),   ('1006', 'C003', 88),   ('1001', 'C004', 88),   ('1002', 'C004', 89),
    ('1003', 'C004', 90),   ('1004', 'C004', 91),   ('1005', 'C004', 92),   ('1006', 'C004', 93),   ('1001', 'C005', 77),
    ('1002', 'C005', 78),   ('1003', 'C005', 79);









聚合函数 sum 配合 case when 的简单函数实现行转列
SELECT
    st.stu_id '学号',
    st.stu_name '姓名',
    sum(
        CASE co.course_name
        WHEN '大学语文' THEN
            sc.scores
        ELSE
            0
        END
    ) '大学语文',
    sum(
        CASE co.course_name
        WHEN '新视野英语' THEN
            sc.scores
        ELSE
            0
        END
    ) '新视野英语',
    sum(
        CASE co.course_name
        WHEN '离散数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '离散数学',
    sum(
        CASE co.course_name
        WHEN '概率论与数理统计' THEN
            sc.scores
        ELSE
            0
        END
    ) '概率论与数理统计',
    sum(
        CASE co.course_name
        WHEN '线性代数' THEN
            sc.scores
        ELSE
            0
        END
    ) '线性代数',
    sum(
        CASE co.course_name
        WHEN '高等数学' THEN
            sc.scores
        ELSE
            0
        END
    ) '高等数学'
FROM
    edu_student st
LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
    st.stu_id
ORDER BY
    NULL;

Q4：day()  month()  year()

select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where month(date)=8 and year(date)=2021
group by date

Q5:（难度高）

计算用户平均次日留存率

分析：

1. case……when……then……

2.if……else……

3.sum/SUM，sum()、 SUM（）

以上3条分析不符合解题思路。

参考：

1.次日；

2.平均留存率；

知识点补充：

01 DATE_ADD() 函数向日期添加指定的时间间隔。

date函数

DATE_ADD(date,INTERVAL expr type)

02 DATEDIFF() 函数返回两个日期之间的天数。

date函数

DATEDIFF(date1,date2)

03 lead用法

lead lag

法1：
select count(date2) / count(date1) as avg_ret
from (
    select
        distinct qpd.device_id,
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date




法2：
select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
) as id_last_next_date

共勉：

知行合一

日复一日行动，向前推进。