【MySQL窗口函数的使用】

SQL语句的执行顺序

1.from
2.where
3.group by...sum().. having...
4.select
5.order by

计算时间差值的函数

1)datediff(end_time,start_time)

计算两个date型之间的天数差
注意：结果是前-后，如果想得到正数差，截止时间放前面

2)timestampdiff(unit,start_time,end_time)

计算两日期时间之间相差的天数，秒数，分钟数，周数，小时数，这里主要分享的是通过MySql内置的函数 TimeStampDiff() 实现。
注意：如果是计算粗略天数，需要先使用date_format将'%Y-%m-%d %H:%i:%S'格式的日期转化为‘%Y-%m-%d’格式

返回日期或日期时间表达式datetime_expr1 和datetime_expr2the 之间的整数差。其中unit单位有如下几种，分别是：FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR 。该参数具体释义如下：

FRAC_SECOND   表示间隔是毫秒
SECOND   秒
MINUTE   分钟
HOUR   小时
DAY   天
WEEK   星期
MONTH   月
QUARTER   季度
YEAR   年

例如：

#计算两日期之间相差多少周

select timestampdiff(week,'2011-09-30','2015-05-04');
#计算两日期之间相差多少天

select timestampdiff(day,'2011-09-30','2015-05-04');

其余日期函数

date_add(日期，INTERVAL n 时间单位)

返回加上n个时间单位后的日期,单位可以是month/week/day/hour/minute等等

date_sub(日期，INTERVAL n 时间单位 )

返回减去n个时间单位后的日期

date_format(时间，‘%Y-%m-%d’)

强制转换时间为所需要的格式,如：'%Y-%m-%d %H:%i:%S'
'%Y%m'
'%Y/%m'
'%Y-%m'

1）lead、lag的窗口函数用法

lead(字段名，n) over () :取值向后偏移n行（空间的理解就是直接将一列数据往前推n个位置，后面的位置就空出来了，具体配合图片理解）;
lag(字段名，n) over () :取值向前偏移n行（空间的理解就是直接将一列数据往前后n个位置，前面的位置就空出来了，具体配合图片理解）;
lag(字段名，n，x) over () :取值向前偏移n行，并将空值填充为数字x（空间的理解就是直接将一列数据往前后n个位置，前面的空出来的位置用X填充上，具体配合图片理解） 。

SELECT id,score,Lead(score,2) over(order by id) lead_score,-- score数列向前推动2位，后面就腾空了2个位置
      Lag(score,2) over(order by id) lag_score, -- score数列向后推2位，腾空2个位置
      lag(score,2,666) over(order by id) lag_score_3 -- score数列向后推动2位，空值被填充为666
FROM exam_record;

2）first_value() 函数概述

FIRST_VALUE()是一个窗口函数，允许您选择窗口框架，分区或结果集的第一行。

FIRST_VALUE(expression) OVER (
        [partition_clause]
        [order_clause]
        [frame_clause]
)

3) nth_value()函数

NTH_VALUE()是一个窗口函数，允许您从有序行集中的第N行获取值
NTH_VALUE()函数返回expression窗口框架第N行的值。如果第N行不存在，则函数返回NULL。N必须是正整数，例如1,2和3。
可以结合order by 排序来使用

NTH_VALUE(expression, N)
FROM FIRST
OVER (
    partition_clause
    order_clause
    frame_clause
)

4) rank()相关函数

percent_rank()

计算分区或结果集中行的百分位数排名
PERCENT_RANK()函数返回一个从0到1的数字。
PERCENT_RANK()对于分区或结果集中的第一行，函数始终返回零。重复的列值将接收相同的PERCENT_RANK()值。
对于指定的行，PERCENT_RANK()计算行的等级减1，除以评估的分区或查询结果集中的行数减1：

(rank - 1) / (total_rows - 1)

dense_rank()

为分区或结果集中的每一行分配排名，而排名值没有间隙。
如果分区具有两个或更多具有相同排名值的行，则将为这些行中的每一行分配相同的排名。
与RANK()函数不同，DENSE_RANK()函数始终返回连续的排名值。

row_number()

为从1开始应用的每一行分配一个序号,连续且不重复