MySQL 窗口、日期函数

curtime

已于 2022-12-12 23:12:28 修改

阅读量338

点赞数

文章标签：数据库 mysql

于 2022-12-11 21:00:00 首次发布

本文链接：https://blog.csdn.net/m0_74162514/article/details/128264018

版权

窗口函数

#窗口函数的格式
function() over(partition by query_patition_clause order by order_by_clause window_clause)

函数funtion支持的类型

排名函数 row_number() 、rank()和 dense_rank()
错行函数：lead() 、lag()
取值函数：first_value() 、last_value()
分箱函数：ntile()
统计函数：常用的聚合函数 max() min() avg() sum() count()
窗口函数 over（）

窗口函数 over()

over()函数包含三个子句（分组子句，排序子句和窗口子句），可根据需求选择子句

分组：partition by query_patition_clause

排序：order by order_by_clause

窗口从句：window_clause

rows  between start_expr and end_expr #rows是物理范围

range between start_expr and end_expr #range是逻辑范围

start 有以下选项：

unbounded preceding：指明窗口开始于分组的第一行，以排序之后的第一行为起点

current row：以当前行为起点

n preceding：以当前行的前面第n行为起点

n following：以当前行的后面第n行为起点

end 有以下选项：

unbounded following：以排序之后的最后一行为终点

current row：以当前行为终点

n preceding：以当前行的前面第n行为终点

n following：以当前行的后面第n行为终点

row_number()、rank()、dense_rank()

#对相同cid科目分组，然后按成绩进行排序

#row_number是没有重复的一种排序，即使对于两行相同的数据，也会根据查询到的
顺序进行排名；而rank函数和dense_rank函数对相同的数据会有一个相同的次序；
rank函数的排序是可能不连续的，dense_rank函数的排序是连续的


select *,
row_number() over(partition by cid order by score desc) `row_number` ,
rank() over(partition by cid order by score desc) `rank` ,
dense_rank()() over(partition by cid order by score desc) `dense_rank`  
from sc;
+------+------+-------+------------+------+------------+
| SID  | CID  | score | row_number | rank | dense_rank |
+------+------+-------+------------+------+------------+
| 01   | 01   |  80.0 |          1 |    1 |          1 |
| 03   | 01   |  80.0 |          2 |    1 |          1 |
| 05   | 01   |  76.0 |          3 |    3 |          2 |
| 02   | 01   |  70.0 |          4 |    4 |          3 |
| 04   | 01   |  50.0 |          5 |    5 |          4 |
| 06   | 01   |  31.0 |          6 |    6 |          5 |
| 01   | 02   |  90.0 |          1 |    1 |          1 |
| 07   | 02   |  89.0 |          2 |    2 |          2 |
| 05   | 02   |  87.0 |          3 |    3 |          3 |
| 03   | 02   |  80.0 |          4 |    4 |          4 |
| 02   | 02   |  60.0 |          5 |    5 |          5 |
| 04   | 02   |  30.0 |          6 |    6 |          6 |
| 01   | 03   |  99.0 |          1 |    1 |          1 |
| 07   | 03   |  98.0 |          2 |    2 |          2 |
| 03   | 03   |  80.0 |          3 |    3 |          3 |
| 02   | 03   |  80.0 |          4 |    3 |          3 |
| 06   | 03   |  34.0 |          5 |    5 |          4 |
| 04   | 03   |  20.0 |          6 |    6 |          5 |
+------+------+-------+------------+------+------------+

LEAD()与LAG()

格式：

lead(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）
lag(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）

EXPR通常是直接是列名，也可以是从其他行返回的表达式；
OFFSET是默认为1，表示在当前分区内基于当前行的偏移行数；
DEFAULT是在OFFSET指定的偏移行数超出了分组的范围时(因为默认会返回null)，可以通过设置这个字段来返回一个默认值来替代null。

# lead()为每一行数据的下一行数据进行开窗，如果该行没有下一行数据，则显示为NULL

# lag()为每一行数据的上一行数据进行开窗，如果该行没有上一行数据，则显示为NULL

select *,
lead(age) over(partition by id order by age)`lead`,
lag(age) over(partition by id order by age)`lag` 
from person;
+------+------+------+------+------+
| id   | name | age  | lead | lag  |
+------+------+------+------+------+
|    1 | ls   |   25 |   28 | NULL |
|    1 | zs   |   28 | NULL |   25 |
|    2 | ww   |   29 |   30 | NULL |
|    2 | zq   |   30 |   35 |   29 |
|    2 | zl   |   35 | NULL |   30 |
+------+------+------+------+------+


#设置偏移量
select *,
lead(age,2) over(partition by id order by age)`lead`,
lag(age,2) over(partition by id order by age)`lag`  
from person;
+------+------+------+------+------+
| id   | name | age  | lead | lag  |
+------+------+------+------+------+
|    1 | ls   |   25 | NULL | NULL |
|    1 | zs   |   28 | NULL | NULL |
|    2 | ww   |   29 |   35 | NULL |
|    2 | zq   |   30 | NULL | NULL |
|    2 | zl   |   35 | NULL |   29 |
+------+------+------+------+------+

#设置默认值
select *,
lead(age,2,'无') over(partition by id order by age)`lead`,
lag(age,2,'empty') over(partition by id order by age)`lag` 
from person;
+------+------+------+------+-------+
| id   | name | age  | lead | lag   |
+------+------+------+------+-------+
|    1 | ls   |   25 | 无   | empty |
|    1 | zs   |   28 | 无   | empty |
|    2 | ww   |   29 | 35   | empty |
|    2 | zq   |   30 | 无   | empty |
|    2 | zl   |   35 | 无   | 29    |
+------+------+------+------+-------+

FIRST_VALUE()与LAST_VALUE()

first_value函数返回一组分组后的第一个值，last_value返回一组分组后的最后一个值

select *,
first_value(age) over(partition by id)rank1, 
last_value(age)over(partition by id) rank2 
from person;
+------+------+------+-------+-------+
| id   | name | age  | rank1 | rank2 |
+------+------+------+-------+-------+
|    1 | zs   |   28 |    28 |    25 |
|    1 | ls   |   25 |    28 |    25 |
|    2 | ww   |   29 |    29 |    30 |
|    2 | zl   |   35 |    29 |    30 |
|    2 | zq   |   30 |    29 |    30 |
+------+------+------+-------+-------+

在开窗函数over()中除了分组和排序，还有一个窗口的从句，在经过排序之后，使用last_value函数生效的范围是第一行至当前行，在上面的例子id=1分组中，每一行显示的所谓最后一个值last value来自第一行到当前行这个范围内的最后一个

select *,
first_value(age) over(partition by id order by age)rank1, 
last_value(age)over(partition by id order by age) rank2 
from person;
+------+------+------+-------+-------+
| id   | name | age  | rank1 | rank2 |
+------+------+------+-------+-------+
|    1 | ls   |   25 |    25 |    25 |
|    1 | zs   |   28 |    25 |    28 |
|    2 | ww   |   29 |    29 |    29 |
|    2 | zq   |   30 |    29 |    30 |
|    2 | zl   |   35 |    29 |    35 |
+------+------+------+-------+-------+

NTH_VALUE(expr, n)、NTILE(n)

其中NTH_VALUE(expr, n)中的第二个参数是指这个函数取排名第几的记录，返回窗口中第n个expr的值。expr可以是表达式，也可以是列名。

select s.sname,
sc.*,
nth_value(sc.score,3) over(partition by sc.cid order by sc.score)rank1  
from sc 
left join student s on sc.sid=s.sid;
+--------+------+------+-------+-------+
| sname  | SID  | CID  | score | rank1 |
+--------+------+------+-------+-------+
| 吴兰   | 06   | 01   |  31.0 |  NULL |
| 李云   | 04   | 01   |  50.0 |  NULL |
| 钱电   | 02   | 01   |  70.0 |  70.0 |
| 周梅   | 05   | 01   |  76.0 |  70.0 |
| 赵雷   | 01   | 01   |  80.0 |  70.0 |
| 孙风   | 03   | 01   |  80.0 |  70.0 |
| 李云   | 04   | 02   |  30.0 |  NULL |
| 钱电   | 02   | 02   |  60.0 |  NULL |
| 孙风   | 03   | 02   |  80.0 |  80.0 |
| 周梅   | 05   | 02   |  87.0 |  80.0 |
| 郑竹   | 07   | 02   |  89.0 |  80.0 |
| 赵雷   | 01   | 02   |  90.0 |  80.0 |
| 李云   | 04   | 03   |  20.0 |  NULL |
| 吴兰   | 06   | 03   |  34.0 |  NULL |
| 孙风   | 03   | 03   |  80.0 |  80.0 |
| 钱电   | 02   | 03   |  80.0 |  80.0 |
| 郑竹   | 07   | 03   |  98.0 |  80.0 |
| 赵雷   | 01   | 03   |  99.0 |  80.0 |
+--------+------+------+-------+-------+

NTILE(n) : n是一个整数，用于创建“桶”的数量，即分组的数量，不能小于等于0。其次需要注意的是，在over函数内，尽量要有排序ORDER BY子句

select s.sname,
sc.*,
ntile(3) over(partition by sc.cid  order by sc.score)rank1  
from sc 
left join student s on sc.sid=s.sid;
+--------+------+------+-------+-------+
| sname  | SID  | CID  | score | rank1 |
+--------+------+------+-------+-------+
| 吴兰   | 06   | 01   |  31.0 |     1 |
| 李云   | 04   | 01   |  50.0 |     1 |
| 钱电   | 02   | 01   |  70.0 |     2 |
| 周梅   | 05   | 01   |  76.0 |     2 |
| 赵雷   | 01   | 01   |  80.0 |     3 |
| 孙风   | 03   | 01   |  80.0 |     3 |
| 李云   | 04   | 02   |  30.0 |     1 |
| 钱电   | 02   | 02   |  60.0 |     1 |
| 孙风   | 03   | 02   |  80.0 |     2 |
| 周梅   | 05   | 02   |  87.0 |     2 |
| 郑竹   | 07   | 02   |  89.0 |     3 |
| 赵雷   | 01   | 02   |  90.0 |     3 |
| 李云   | 04   | 03   |  20.0 |     1 |
| 吴兰   | 06   | 03   |  34.0 |     1 |
| 孙风   | 03   | 03   |  80.0 |     2 |
| 钱电   | 02   | 03   |  80.0 |     2 |
| 郑竹   | 07   | 03   |  98.0 |     3 |
| 赵雷   | 01   | 03   |  99.0 |     3 |
+--------+------+------+-------+-------+

MAX()、MIN()、AVG()、SUM()与COUNT()

# 只有分组，没有排序，显示分组的行数
select s.sname,
sc.*,
count(score) over(partition by sc.cid)count  
from sc 
left join student s on sc.sid=s.sid;
+--------+------+------+-------+-------+
| sname  | SID  | CID  | score | count |
+--------+------+------+-------+-------+
| 赵雷   | 01   | 01   |  80.0 |     6 |
| 钱电   | 02   | 01   |  70.0 |     6 |
| 吴兰   | 06   | 01   |  31.0 |     6 |
| 孙风   | 03   | 01   |  80.0 |     6 |
| 周梅   | 05   | 01   |  76.0 |     6 |
| 李云   | 04   | 01   |  50.0 |     6 |
| 赵雷   | 01   | 02   |  90.0 |     6 |
| 郑竹   | 07   | 02   |  89.0 |     6 |
| 周梅   | 05   | 02   |  87.0 |     6 |
| 李云   | 04   | 02   |  30.0 |     6 |
| 孙风   | 03   | 02   |  80.0 |     6 |
| 钱电   | 02   | 02   |  60.0 |     6 |
| 孙风   | 03   | 03   |  80.0 |     6 |
| 李云   | 04   | 03   |  20.0 |     6 |
| 钱电   | 02   | 03   |  80.0 |     6 |
| 吴兰   | 06   | 03   |  34.0 |     6 |
| 赵雷   | 01   | 03   |  99.0 |     6 |
| 郑竹   | 07   | 03   |  98.0 |     6 |
+--------+------+------+-------+-------+

# 如果既有分组也有排序，那么排序之后的开窗函数是默认排序之后第一行数据到当前行（逻辑层面）的行数
select s.sname,
sc.*,
count(score) over(partition by sc.cid order by sc.score)count  
from sc 
left join student s on sc.sid=s.sid;
+--------+------+------+-------+-------+
| sname  | SID  | CID  | score | count |
+--------+------+------+-------+-------+
| 吴兰   | 06   | 01   |  31.0 |     1 |
| 李云   | 04   | 01   |  50.0 |     2 |
| 钱电   | 02   | 01   |  70.0 |     3 |
| 周梅   | 05   | 01   |  76.0 |     4 |
| 赵雷   | 01   | 01   |  80.0 |     6 |
| 孙风   | 03   | 01   |  80.0 |     6 |
| 李云   | 04   | 02   |  30.0 |     1 |
| 钱电   | 02   | 02   |  60.0 |     2 |
| 孙风   | 03   | 02   |  80.0 |     3 |
| 周梅   | 05   | 02   |  87.0 |     4 |
| 郑竹   | 07   | 02   |  89.0 |     5 |
| 赵雷   | 01   | 02   |  90.0 |     6 |
| 李云   | 04   | 03   |  20.0 |     1 |
| 吴兰   | 06   | 03   |  34.0 |     2 |
| 孙风   | 03   | 03   |  80.0 |     4 |
| 钱电   | 02   | 03   |  80.0 |     4 |
| 郑竹   | 07   | 03   |  98.0 |     5 |
| 赵雷   | 01   | 03   |  99.0 |     6 |
+--------+------+------+-------+-------+

Date 函数

返回当前日期：

CURDATE()

CURRENT_DATE()

CURRENT_DATE

select curdate(),current_date(),current_date;
+------------+----------------+--------------+
| curdate()  | current_date() | current_date |
+------------+----------------+--------------+
| 2022-12-11 | 2022-12-11     | 2022-12-11   |
+------------+----------------+--------------+

返回当前时间：

CURTIME()

CURRENT_TIME()

CURRENT_TIME

select curtime(),current_time(),current_time;
+-----------+----------------+--------------+
| curtime() | current_time() | current_time |
+-----------+----------------+--------------+
| 14:37:29  | 14:37:29       | 14:37:29     |
+-----------+----------------+--------------+

返回当前日期和时间：

CURRENT_TIMESTAMP()

CURRENT_TIMESTAMP

LOCALTIME()

LOCALTIME

LOCALTIMESTAMP()

LOCALTIMESTAMP

NOW()

SYSDATE()

select CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP,LOCALTIME(),LOCALTIME ,LOCALTIMESTAMP(),LOCALTIMESTAMP,NOW(),SYSDATE();
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| CURRENT_TIMESTAMP() | CURRENT_TIMESTAMP   | LOCALTIME()         | LOCALTIME           | LOCALTIMESTAMP()    | LOCALTIMESTAMP      | NOW()               | SYSDATE()           |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+
| 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 | 2022-12-11 14:42:01 |
+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+---------------------+

日期和时间比较

mysql> select '2022-02-02'>='2022-02-13';
+----------------------------+
| '2022-02-02'>='2022-02-13' |
+----------------------------+
|                          0 |
+----------------------------+

日期和时间运算

DATE_ADD()函数：日期时间增加减少

# 正值表示加，负值表示减
mysql> select date_add('2002-1-1',interval 1 year);
+--------------------------------------+
| date_add('2002-1-1',interval 1 year) |
+--------------------------------------+
| 2003-01-01                           |
+--------------------------------------+
1 row in set (0.01 sec)

mysql> select date_add(now(),interval 1 year);
+---------------------------------+
| date_add(now(),interval 1 year) |
+---------------------------------+
| 2023-12-11 14:59:01             |
+---------------------------------+

select date_add(now(),interval 1 quarter); #季度
+------------------------------------+
| date_add(now(),interval 1 quarter) |
+------------------------------------+
| 2023-03-11 15:00:17                |
+------------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 month);
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2023-01-11 15:00:43              |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 week);
+---------------------------------+
| date_add(now(),interval 1 week) |
+---------------------------------+
| 2022-12-18 15:00:53             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 day);
+--------------------------------+
| date_add(now(),interval 1 day) |
+--------------------------------+
| 2022-12-12 15:01:00            |
+--------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 hour);
+---------------------------------+
| date_add(now(),interval 1 hour) |
+---------------------------------+
| 2022-12-11 16:01:07             |
+---------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 minute);
+-----------------------------------+
| date_add(now(),interval 1 minute) |
+-----------------------------------+
| 2022-12-11 15:02:15               |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 second);
+-----------------------------------+
| date_add(now(),interval 1 second) |
+-----------------------------------+
| 2022-12-11 15:01:31               |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> select date_add(now(),interval 1 microsecond);
+----------------------------------------+
| date_add(now(),interval 1 microsecond) |
+----------------------------------------+
| 2022-12-11 15:01:44.000001             |
+----------------------------------------+

DATEDIFF()函数：计算两个日期差

select datediff(now(),'1994-11-08');
+------------------------------+
| datediff(now(),'1994-11-08') |
+------------------------------+
|                        10260 |
+------------------------------+

日期和时间的格式化

DATE_FORMAT(date,format)函数：

常用：DATE_FORMAT(SYSDATE(),'%Y-%m-%d %H:%i:%s')

%p：PM 或AM；

%r：HH:mm:ss PM形式时间；

%W：周，周名，如Tuesday；

%w：周，0-6，0为周日；

%j：当前日期所在一年中的天数，3位（001-366）

select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s');
+----------------------------------------+
| DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%s') |
+----------------------------------------+
| 2022-12-11 15:12:21                    |
+----------------------------------------+

DATE_FORMAT(date,format)和STR_TO_DATE(str,format)函数

DATE_FORMAT(date,format)：将数据库中的date数据格式化为String类型

STR_TO_DATE(str,format)：将指定的时间格式的字符串按照格式转换为DATETIME类型的值。str要与format的格式保持一致，否则会报错。

select date_format(now(),'%Y-%m-%d'),str_to_date(now(),'%Y-%m-%d');
+-------------------------------+-------------------------------+
| date_format(now(),'%Y-%m-%d') | str_to_date(now(),'%Y-%m-%d') |
+-------------------------------+-------------------------------+
| 2022-12-11                    | 2022-12-11                    |
+-------------------------------+-------------------------------+

YEAR()函数：取日期时间的年份

select year(now());
+-------------+
| year(now()) |
+-------------+
|        2022 |
+-------------+