SQL第二天：

窗口函数：

链接：通俗易懂的学会：SQL窗口函数

几个面试题链接：五大常考SQL面试题_找出连续7天登陆,连续30天登陆的用户_Begin to change的博客-CSDN博客

一：多表查询

二:窗口函数

1.排序函数
rank()函数，如果有并列情况，会占用下一个名次的位置，比如，成绩为100的学生有三个并列第一，那么99分的学生是第二名，通过rank()函数，名次是：1,1,1,4；
dense()函数，如果有并列的情况，不会占用下一个名词，同用上个例子，名次是：1,1,1,2；
row_number()函数，会忽略并列的情况，同用上述例子，名次是：1,2,3,4;

2.聚合函数
count() over(partition by ... order by ...)：求分组后的总数；
max() over(partition by ... order by ...)：求分组后的最大值；
min() over(partition by ... order by ...)：求分组后的最小值；
avg() over(partition by ... order by ...)：求分组后的平均值；

3.比较函数
lag() over(partition by ... order by ...)：取出向前第n行数据。　　
lead() over(partition by ... order by ...)：取出向后第n行数据。

链接：

1.SQL136 每类试窗口函数OVER(PARTITION BY)详细用法——语法+函数+开窗范围ROWS和RANGE_Ahuuua的博客-CSDN博客卷得分前3名

1）窗口函数：有三种排序方式

• rank() over() 1 2 2 4 4 6  (计数排名，跳过相同的几个，eg.没有3没有5)
• row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
• dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）聚合函数：通常查找最大值最小值的时候，首先会想到使用聚合函数。

a.group by的常见搭配：常和以下聚合函数搭配 

•     avg（）-- 求平均值
•     count（）-- 计数
•     sum（）-- 求和
•     max（） -- 最大值
•     min（）-- 最小值

b.group by 的进阶用法，和with rollup一起使用。 

3）左右连接

左连接：表1 left join 表2 on 表1.字段=表2.字段  （以表1为准，表2进行匹配）

右连接：表1 right join 表2 on 表1.字段=表2.字段  （以表2为准，表1进行匹配）

全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）

内连接：表1 inner join 表2（取表1和表2相交部分）

外连接：表1 full outer join 表2 （取表1和表2不相交的部分）

ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

链接：每类试卷得分前3名_牛客题霸_牛客网

2.SQL137 第二快/慢用时之差大于试卷时长一半的试卷

这个嵌套了很多层，难度较高

 链接：第二快慢用时之差大于试卷时长一半的试卷_牛客题霸_牛客网
3.SQL138 连续两次作答试卷的最大时间窗

SELECT DATEDIFF('2018-05-09 00:00:00','2018-05-08 23:59:59') AS DiffDate; //结果 1；

DATEDIFF(end_time, start_time) //结果：end_time-start_time

• 生成下次作答时间，按用户分区按作答时间升序：
  • lead(start_time) over(PARTITION BY uid ORDER BY start_time) as next_start_time

链接：datediff()函数 与 timestampdiff()函数的区别 及使用。_liguangix的博客-CSDN博客

链接：连续两次作答试卷的最大时间窗_牛客题霸_牛客网

4.SQL139 近三个月未完成试卷数为0的用户完成情况

链接：近三个月未完成试卷数为0的用户完成情况_牛客题霸_牛客网

group by的用法：（转）SQL中group by详解_sql group by_周五见的博客-CSDN博客

5.SQL140 未完成率较高的50%用户近三个月答卷情况

链接：未完成率较高的50%用户近三个月答卷情况_牛客题霸_牛客网

5个常用的排序函数

• rank() over() 1 2 2 4 4 6  (计数排名，跳过相同的几个，eg.没有3没有5)\
• row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
• dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

• percent_rank() over() 按照数字所在的位置进行百分位分段
• ntile(n)over() 将数字按照大小平均分成n段
• lead(字段名，n)over()把字段数据向前移n个单元格
• lag(字段名，n)over()把字段数据向后移n个单元格

6.添加索引

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

7.修改表格

1. 修改表名
    alter table 表名 rename to 新的表名;
2. 修改表的字符集
    alter table 表名 character set 字符集名称;
3. 添加一列
    alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
    alter table 表名 change 列名 新列别 新数据类型;
    alter table 表名 modify 列名 新数据类型;
5. 删除列
    alter table 表名 drop 列名;

8.创建外键

ALTER TABLE <表名>

ADD CONSTRAINT FOREIGN KEY (<列名>)

REFERENCES <关联表>（关联列）

9.concat(A,"",B)

RTrim():去除字符串右端空格
LTrim():去除字符串左端空格
Trim():去除字符串两端的空格

10.REPLACE

REPLACE(要替换的字段, "替换之前", "目标替换值")

11.聚合函数group_concat

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。

12.limit fenye 分页查询

LIMIT 语句结构： LIMIT X,Y 

• Y ：返回几条记录
• X：从第几条记录开始返回（第一条记录序号为0，默认为0）

13.IN和EXISTS的区别

https://blog.csdn.net/wqc19920906/article/details/79800374

14.保留到小数点后几位数字

ROUND(聚合函数，精确到小数点后几位)

ROUND(聚合函数) 默认结果为整数

15.报错问题：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

only_full_group_by：使用这个就是使用和oracle一样的group 规则, select的列都要在group中,或者本身是聚合列(SUM,AVG,MAX,MIN) 才行，其实这个配置目前个人感觉和distinct差不多的，所以去掉就好

查询mysql 1055错误码发现问题为在mysql的配置中如果设置了sql_mode包含ONLY_FULL_GROUP_BY值得话，在进行查询时需要将select的字段都包含在group by 中。
即 select x,y from xxx group by x,y
原文链接：https://blog.csdn.net/lxw1844912514/article/details/100975571

16.取整问题

ceiling函数向上取整（返回不小于该数的最小整数值），round(数，0)四舍五入取整

1. ceil() / ceiling（） 向上取整；示例： ceil（1.2） = 2

2. floor() 向下取整；示例： floor（1.2） = 1

3. round() 四舍五入

17.考试题目

这道看一下：https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512?tpId=82&tqId=35496&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3FjudgeStatus%3D3%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%25E7%25AF%2587%26topicId%3D82&difficulty=undefined&judgeStatus=3&tags=&title=

18.ifnull函数

IFNULL函数是MySQL控制流函数之一，它接受2个参数，如果不是NULL，则返回第一个参数。否则，IFNULL返回第二个参数。
两个参数可以是文字值或者表达式。
以下语句说明IFNULL函数的使用方法：
如果expression_1不为NUll，IFNULL函数返回expression_1，否则返回expression_2的结果。IFNULL函数返回字符串或数字。

19.这个题很难 重点看看

牛客每个人最近的登录日期(五)_牛客题霸_牛客网

20. 统计salary的累计和running_total_牛客题霸_牛客网

如果想计算累计，直接使用窗口函数中的聚合函数   SUM(salary) OVER(ORDER BY emp_no ASC)

sum() over (order by ) 开窗函数，sum(a) over (order by b) 的含义是:
例如
a  b
1 2
3 4
5 6
按照b列排序，将a依次相加，得到结果，如下:
a  b  sum(a) over (order by b)：
1 2    1
3 4    1+3
5 6    1+3+5

21.牛客的课程订单分析(三)_牛客题霸_牛客网

with moreThan1 as
(
    select user_id
    from order_info
    where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status ="completed"
    group by user_id
    having count(id)>1
)
 
select *
from order_info
where datediff(date,"2025-10-15")>0
      and product_name in ("C++","Java","Python")
      and status="completed"
      and user_id in (select * from moreThan1)
order by id;

select t.id,t.user_id,t.product_name,t.status,t.client_id,t.date
from (select *,count(user_id) over(partition by user_id) as number from order_info
where datediff(date,'2025-10-15')>0
and product_name in("C++","Java","Python")
and status='completed') as t
where t.number>1
order by t.id

22.日期转换函数

日期函数格式转换

1	DATE_FORMAT(date,format)

DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据。

date 参数是合法的日期。format 规定日期/时间的输出格式。

可以使用的格式有：

常用格式	对应描述
%Y	--年，4 位
%m	--月，数值（00-12）
%M	--月名
%k	--小时（0-23）

日期函数详解 转载自：MySQL DATE_FORMAT() 函数 | 菜鸟教程