猴子的图解SQL 学习笔记

blairchen

Publish：Feb 1, 2021

SQL 的书写规则是什么？
如何指定查询条件？
SQL 是如何运行的？

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

RAND() Function 0 <= ret < 1

1~100 -> SELECT FLOOR(1 + (RAND() * 100)) LIMIT 10;

IF(expr1,expr2,expr3)

SELECT CustomerName, CONCAT(“H1”, " H2 ", RAND()),
CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM Customers;

#SQL 如何查询关于【连续几天】的问题

SELECT
	id,
	created_at,
	CURDATE( ), -- 2021-03-18
	DATE(created_at), -- 2019-11-21
	DATE(created_at) - 1  -- 20191120
FROM
	users 
	LIMIT 5;

SELECT
    user_id,
    MAX( count_date_on ) 
FROM
    (
        (
        SELECT
            user_id,
            count( date_on ) count_date_on 
        FROM
            (
            SELECT
                user_id,
                date,
                row_number ( ) over ( PARTITION BY USER ORDER BY date DESC ) rnk,
                date - ( MAX( date ) - rnk ) date_on 
            FROM
                TB 
            GROUP BY
                user_id 
            ) A 
        GROUP BY
            user_id,
            date_on 
        ) 
    ) B 
GROUP BY
    user_id

1. SQL：查找重复数据？

知识: group by 列名 having count(列名) > n

select 列名 from table group by 列名 having count(列名) > n;

举一反三: 查询平均成绩大于60分的学生的学号和平均成绩

1
2
3

select 学号 ,avg(成绩) from score 
group by 学号  
having avg(成绩 ) >60

查询各学生的年龄（精确到月份）

/*
【知识点】时间格式转化 timestampdiff
*/
select 学号 ,timestampdiff(month ,出生日期 ,now())/12 
from student ;

2. SQL：如何查找第N高的数据？

select 
    ifNull(
        (select distinct salary from Employee order by Salary Desc limit 1,1),
        null
    ) as SecondHighestSalary;

知识: limit 1,n

3. SQL：查找不在表里的数据

left join

select 
    a.Name as Customers
from 
    Customers as a left join Orders as b
on a.Id=b.CustomerId
where b.CustomerId is null;

4. SQL：你有多久没涨过工资了？

left join

5. SQL：如何比较日期数据？

举一反三： Weather

select a.ID, a.date
from weather as a cross join weather as b 
     on timestampdiff(day, a.date, b.date) = -1
where a.temp > b.temp;

6. DiDi: 如何找出最小的N个数？

1.筛选出2017年入学的“计算机”专业年龄最小的3位同学名单（姓名、年龄）
2.统计每个班同学各科成绩平均分大于80分的人数和人数占比

考点:

1.使用逻辑树分析方法将复杂问题变成简单问题的能力
2.当遇到“每个”问题的时候，要想到用分组汇总
3.查询最小n个数据的问题：先排序（order by），然后使用limit取出前n行数据
4.遇到有筛选条件的统计数量问题时，使用case表达式筛选出符合条件的行为1，否则为0。然后用汇总函数（sum）对case表达式输出列求和。

7. 拼夕夕：连续出现N次的内容？

方法1：自连接

SELECT
    *
FROM
    Score AS a,
    Score AS b,
    Score AS c
WHERE
    a.s_id = b.s_id - 1 
    AND b.s_id = c.s_id - 1 
    AND a.s_score = b.s_score 
    AND b.s_score = c.s_score; 


SELECT
    DISTINCT a.s_score as 最终答案
FROM
    Score AS a,
    Score AS b,
    Score AS c
WHERE
    a.s_id = b.s_id - 1 
    AND b.s_id = c.s_id - 1 
    AND a.s_score = b.s_score 
    AND b.s_score = c.s_score;

方法2： window function

SELECT DISTINCT
    球员姓名 
FROM
    (
    SELECT
        球员姓名,
        lead ( 球员姓名, 1 ) over ( PARTITION BY 球队 ORDER BY 得分时间 ) AS 姓名1,
        lead ( 球员姓名, 2 ) over ( PARTITION BY 球队 ORDER BY 得分时间 ) AS 姓名2 
    FROM
        分数表 
    ) AS a 
WHERE
    (
        a.球员姓名 = a.姓名1
    AND a.球员姓名 = a.姓名2
    );

10. SQL：经典topN问题

select *
from (
   select *, 
          row_number() over (partition by 姓名
                       order by 成绩 desc) as ranking
   from 成绩表) as a
where ranking <=2

11. 链家：如何分析留存率？

input:

如何分析留存率

指标定义：

某日活跃用户数，某日活跃的去重用户数。
N日活跃用户数，某日活跃的用户数在之后的第N日活跃用户数。
N日活跃留存率，N日留存用户数/某日活跃用户数

例：登陆时间（20180501日）去重用户数10000，这批用户在20180503日仍有7000人活跃，则3日活跃留存率为7000/10000=70%

output

11.1 活跃用户数对应的日期

SELECT
    登陆时间,
    count( DISTINCT 用户id ) AS 活跃用户数 
FROM
    用户行为信息表 
WHERE
    应用名称 = '相机' 
GROUP BY
    登陆时间;

11.2 次日留存用户数

次日留存用户数：在今日登录，明天也有登录的用户数。也就是时间间隔=1

一个表如果涉及到时间间隔，就需要用到自联结，也就是将两个相同的表进行联结

SELECT
    *,
    count(DISTINCT CASE WHEN 时间间隔 = 1 THEN 用户id ELSE NULL END) AS 次日留存数 
    FROM
        (SELECT *, timestampdiff(DAY, a_t, b_t ) AS 时间间隔 FROM c) 
GROUP BY
    a_t;

次日留存用户数

猴子的图解SQL 学习笔记

方法1： 自连接

方法2： window function

方法1：自连接