Database / SQL
MySQL 与 SQL 基础（九）
记录 MySQL 启动方式、SQL 分类、DDL、DML、DQL、DCL 和表结构操作。
时间日期类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 bytes | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 bytes | -838:59:59 至 838:59:59 | HH-MM-SS | 时间值或持续时间 |
| YEAR | 1 bytes | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 bytes | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH-MM-SS | 混合日期和时间值 |
| TIMESTAMP | 4 bytes | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH-MM-SS | 混合日期和时间值，时间戳 |
窗口函数
介绍
MySQL 8.0后支持
窗口即为多个行的集合
语法
select
column
window_function() over()
from table;与GROUP BY将一类数据合并为一行不同，窗口函数会为每一行返回一个基于其所属窗口的计算结果
over()子句，是窗口函数的核心，定义以什么方式将数据划分为窗口，并执行计算
over()子句的组成部分：
window_function() over (
[partition by 分组字段]
[order by 排序字段 [ASC|DESC]]
[frame_clause]
)partition by将结果集划分为多个分区，类似于group by的分组，若省略，整个结果集将被作为一个区
order by定义分区内的排序顺序
frame_clause定义窗口框架（决定窗口函数计算时使用哪些行）：
没有指定时默认为range between unbounded preceding and current row，即从分区的第一行开始到当前行结束，但排名函数会忽略这个框架，它们总是针对整个分区进行计算
确定一个相对于当前行的计算范围
通常在over()子句包含order by时使用
rows between ... and ...基于物理行位置（由order by决定）
unbounded preceding：分区开始
n preceding：当前行之后的n行
current row：当前行
n following：当前行之后的n行
unbounded following：分区结束
可用于计算与上一行的差值，移动平均，累计计算
range between ... and ...基于排序键的值范围
unbounded preceding / following：含义与rows相同
n preceding：排序键值小于等于当前行键值 - n的所有行
current row：排序键值等于当前行的所有行
n following：排序键值大于等于当前行键值 + n的所有行
常用于时间间隔（如最近n天），数值范围等
窗口函数执行具体计算
常用窗口函数
- 排名函数
| 函数 | 描述 | 特点 |
|---|---|---|
| row_number() | 分配唯一序号 | 无并列排名 |
| rank() | 允许并列排名 | 留出空位（1,2,2,4） |
| dense_rank() | 允许并列排名 | 不留空位（1,2,2,3） |
| ntile(n) | 将数据分为n组 | 用于分位数计算 |
- 分析函数
| 函数 | 描述 |
|---|---|
| lag(column, n) | 获取前n行值 |
| lead(column, n) | 获取后n行值 |
| first_value(column) | 分区第一个值 |
| last_value(column) | 分区最后一个值 |
| nth_value(column, n) | 分区第n个值 |
- 聚合函数
| 函数 | 描述 | 特点 |
|---|---|---|
| sum() | 窗口内求和 | 支持框架定义 |
| avg() | 窗口内平均 | 自动忽略NULL |
| count() | 窗口内计数 | DISTINCT可用 |
| min() / max() | 窗口内极值 | 性能优化 |