抱歉,您的浏览器无法访问本站

本页面需要浏览器支持(启用)JavaScript


了解详情 >
image
  1. SQL 的书写规则是什么?
  2. 如何指定查询条件?
  3. 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 如何查询关于【连续几天】的问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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;
select 列名 from table group by 列名 having count(列名) > n;

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

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

查询各学生的年龄(精确到月份)

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

2. SQL:如何查找第N高的数据?

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

知识: limit 1,n

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

left join
left join
image
1
2
3
4
5
6
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
left join

5. SQL:如何比较日期数据?

image
image

举一反三: Weather

1
2
3
4
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分的人数和人数占比

image
image
image
image
image
image

考点:

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

7. 拼夕夕:连续出现N次的内容?

方法1: 自连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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问题

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

11. 链家:如何分析留存率?

input:

如何分析留存率
如何分析留存率

指标定义:

  1. 某日活跃用户数,某日活跃的去重用户数。
  2. N日活跃用户数,某日活跃的用户数在之后的第N日活跃用户数。
  3. N日活跃留存率,N日留存用户数/某日活跃用户数

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

output
output

11.1 活跃用户数对应的日期

1
2
3
4
5
6
7
8
9
SELECT
登陆时间,
count( DISTINCT 用户id ) AS 活跃用户数
FROM
用户行为信息表
WHERE
应用名称 = '相机'
GROUP BY
登陆时间;
image

11.2 次日留存用户数

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

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

1
2
3
4
5
6
7
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;
次日留存用户数
次日留存用户数