Oracle窗口函数

在Oracle数据库中，窗口函数（Window Functions）是一类强大的分析函数，它能够在**不聚合数据行**的情况下，对一组相关的行（称为"窗口"）进行计算。与聚合函数（如`SUM`、`AVG`）不同，窗口函数不会将多行合并为一行，而是为**每一行**都返回一个计算结果，同时保留原始行的所有细节。

## 一、窗口函数

窗口是由`OVER()`子句定义的一组与当前行相关的数据行。可以将窗口想象成一个"滑动窗口"，函数在这个窗口范围内进行计算。

#### 1、基本语法

```sql
窗口函数名([参数]) OVER (
  [PARTITION BY 分区字段]  -- 将数据分成多个组/分区
  [ORDER BY 排序字段]      -- 在分区内排序
  [窗口框架子句]           -- 定义窗口的具体范围
)
```

### 2、PARTITION BY - 分区

```sql
PARTITION BY dept_id        -- 按部门分区
PARTITION BY dept_id, year  -- 多列分区
```

### 3、ORDER BY - 排序

```sql
ORDER BY hire_date          -- 按入职日期排序
ORDER BY salary DESC        -- 按工资降序
```

### 4、窗口框架

```sql
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    -- 从开始到当前行
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING           -- 前2行到后1行
ROWS BETWEEN 5 PRECEDING AND CURRENT ROW           -- 最近6行（含当前）
RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW -- 最近7天
```

## 二、聚合类窗口函数

### 1、SUM() - 求和

```sql
-- 累计求和
SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) as 累计工资

-- 分区求和
SUM(salary) OVER (PARTITION BY dept_id) as 部门工资总额
```

##### 累计求和与分区求和的区别

假设部门A有3个员工数据：

| emp_id | dept_id | hire_date  | salary |
| ------ | ------- | ---------- | ------ |
| 101    | A       | 2020-01-01 | 5000   |
| 102    | A       | 2020-02-01 | 6000   |
| 103    | A       | 2020-03-01 | 7000   |

**累计求和**

```sql
SELECT 
    emp_id, dept_id, hire_date, salary,
    SUM(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date
    ) as 累计工资
FROM employees;
```

**结果**：

| emp_id | dept_id | hire_date  | salary | 累计工资                   |
| ------ | ------- | ---------- | ------ | -------------------------- |
| 101    | A       | 2020-01-01 | 5000   | **5000**                   |
| 102    | A       | 2020-02-01 | 6000   | **11000** (5000+6000)      |
| 103    | A       | 2020-03-01 | 7000   | **18000** (5000+6000+7000) |

**分区求和**

```sql
SELECT 
    emp_id, dept_id, hire_date, salary,
    SUM(salary) OVER (PARTITION BY dept_id) as 部门工资总额
FROM employees;
```

**结果**：

| emp_id | dept_id | hire_date  | salary | 部门工资总额 |
| ------ | ------- | ---------- | ------ | ------------ |
| 101    | A       | 2020-01-01 | 5000   | **18000**    |
| 102    | A       | 2020-02-01 | 6000   | **18000**    |
| 103    | A       | 2020-03-01 | 7000   | **18000**    |

##### 等价写法

**累计求和**

```sql
-- 这三种写法完全等价
SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date)
SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS UNBOUNDED PRECEDING)
SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
```

**分区求和**

```sql
-- 这两种写法完全等价
SUM(salary) OVER (PARTITION BY dept_id)
SUM(salary) OVER (PARTITION BY dept_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```

##### 更复杂的窗口框架示例

```sql
-- 最近3条记录的移动平均（含当前行）
AVG(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as 移动平均工资

-- 从当前行到分区结束的累计
SUM(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as 反向累计
```

### 2、AVG() - 平均值

```sql
-- 移动平均（最近3条记录）
AVG(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as 近期平均工资

-- 部门平均工资
AVG(salary) OVER (PARTITION BY dept_id) as 部门平均工资
```

### 3、COUNT() - 计数

```sql
-- 累计计数
COUNT(*) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
) as 部门累计人数

-- 分区计数
COUNT(*) OVER (PARTITION BY dept_id) as 部门总人数
```

### 4、MIN() / MAX() - 最小/最大值

```sql
-- 部门内最高和最低工资
MAX(salary) OVER (PARTITION BY dept_id) as 部门最高工资,
MIN(salary) OVER (PARTITION BY dept_id) as 部门最低工资,

-- 历史最高值
MAX(salary) OVER (
    PARTITION BY emp_id 
    ORDER BY effective_date
) as 历史最高工资
```

## 三、 排名类窗口函数

### 1、ROW_NUMBER() - 行号

```sql
-- 为每个部门的员工按工资排序
ROW_NUMBER() OVER (
    PARTITION BY dept_id 
    ORDER BY salary DESC
) as 工资排名
```

### 2、RANK() / DENSE_RANK() - 排名

```sql
-- 排名（允许并列，可能有间隔）
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 排名,

-- 密集排名（允许并列，无间隔）
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 密集排名
```

### 3、NTILE() - 分桶

```sql
-- 将每个部门的员工分成4个等级
NTILE(4) OVER (PARTITION BY dept_id ORDER BY salary DESC) as 工资等级
```

## 四、位移类窗口函数

### 1、LAG() / LEAD() - 前一行/后一行

```sql
-- 与前一个月比较
LAG(sales, 1) OVER (PARTITION BY product_id ORDER BY month) as 上月销售额,
LEAD(sales, 1) OVER (PARTITION BY product_id ORDER BY month) as 下月销售额,

-- 带默认值
LAG(sales, 1, 0) OVER (PARTITION BY product_id ORDER BY month) as 上月销售额
```

### 2、FIRST_VALUE() / LAST_VALUE() - 首行/末行

```sql
-- 部门内第一个和最后一个员工的工资
FIRST_VALUE(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
) as 最早员工工资,

LAST_VALUE(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as 最晚员工工资
```

## 五、统计分析类窗口函数

### 1、STDDEV() / VARIANCE() - 标准差/方差

```sql
-- 部门工资的标准差和方差
STDDEV(salary) OVER (PARTITION BY dept_id) as 工资标准差,
VARIANCE(salary) OVER (PARTITION BY dept_id) as 工资方差
```

### 2、CORR() - 相关系数

```sql
-- 销售额与广告费用的相关系数
CORR(sales, ad_cost) OVER (PARTITION BY region) as 相关性
```

## 六、分布类窗口函数

### 1、PERCENT_RANK() - 百分比排名

```sql
-- 工资在部门中的百分比排名
PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary) as 工资百分比排名
```

### 2、CUME_DIST() - 累积分布

```sql
-- 工资的累积分布
CUME_DIST() OVER (PARTITION BY dept_id ORDER BY salary) as 工资累积分布
```

## 七、使用示例

### 1、销售分析

```sql
SELECT 
    salesperson,
    sale_date,
    amount,
    -- 月度累计
    SUM(amount) OVER (
        PARTITION BY salesperson, EXTRACT(YEAR FROM sale_date), EXTRACT(MONTH FROM sale_date)
        ORDER BY sale_date
    ) as 月累计销售额,
  
    -- 移动平均（近7天）
    AVG(amount) OVER (
        PARTITION BY salesperson
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as 7日移动平均,
  
    -- 排名
    RANK() OVER (PARTITION BY EXTRACT(YEAR FROM sale_date) ORDER BY amount DESC) as 年度排名
FROM sales_data;
```

### 2、员工工资分析

```sql
SELECT 
    emp_name,
    dept_name,
    salary,
    hire_date,
    -- 部门统计
    AVG(salary) OVER (PARTITION BY dept_id) as 部门平均工资,
    MAX(salary) OVER (PARTITION BY dept_id) as 部门最高工资,
  
    -- 与平均值的差距
    salary - AVG(salary) OVER (PARTITION BY dept_id) as 与部门平均差,
  
    -- 百分比排名
    ROUND(PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary) * 100, 2) as 部门百分比排名,
  
    -- 累计工资
    SUM(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as 部门累计工资支出
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;
```

### 3、金融数据时间序列分析

```sql
SELECT 
    trade_date,
    stock_code,
    close_price,
    -- 技术指标
    AVG(close_price) OVER (
        PARTITION BY stock_code 
        ORDER BY trade_date 
        ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
    ) as MA5,  -- 5日均线
  
    AVG(close_price) OVER (
        PARTITION BY stock_code 
        ORDER BY trade_date 
        ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
    ) as MA10, -- 10日均线
  
    -- 涨跌幅
    ROUND((close_price - LAG(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date)) 
          / LAG(close_price, 1) OVER (PARTITION BY stock_code ORDER BY trade_date) * 100, 2) as 日涨跌幅,
  
    -- 历史最高价
    MAX(close_price) OVER (
        PARTITION BY stock_code 
        ORDER BY trade_date
    ) as 历史最高价
FROM stock_prices;
```

## 一、窗口函数

窗口是由`OVER()`子句定义的一组与当前行相关的数据行。可以将窗口想象成一个"滑动窗口"，函数在这个窗口范围内进行计算。

#### 1、基本语法

### 2、PARTITION BY - 分区

```sql
PARTITION BY dept_id        -- 按部门分区
PARTITION BY dept_id, year  -- 多列分区
```

### 3、ORDER BY - 排序

```sql
ORDER BY hire_date          -- 按入职日期排序
ORDER BY salary DESC        -- 按工资降序
```

### 4、窗口框架

## 二、聚合类窗口函数

### 1、SUM() - 求和

```sql
-- 累计求和
SUM(salary) OVER (PARTITION BY dept_id ORDER BY hire_date) as 累计工资

-- 分区求和
SUM(salary) OVER (PARTITION BY dept_id) as 部门工资总额
```

##### 累计求和与分区求和的区别

假设部门A有3个员工数据：

**累计求和**

```sql
SELECT 
    emp_id, dept_id, hire_date, salary,
    SUM(salary) OVER (
        PARTITION BY dept_id 
        ORDER BY hire_date
    ) as 累计工资
FROM employees;
```

**结果**：

**分区求和**

```sql
SELECT 
    emp_id, dept_id, hire_date, salary,
    SUM(salary) OVER (PARTITION BY dept_id) as 部门工资总额
FROM employees;
```

**结果**：

##### 等价写法

**累计求和**

**分区求和**

```sql
-- 这两种写法完全等价
SUM(salary) OVER (PARTITION BY dept_id)
SUM(salary) OVER (PARTITION BY dept_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
```

##### 更复杂的窗口框架示例

```sql
-- 最近3条记录的移动平均（含当前行）
AVG(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as 移动平均工资

-- 从当前行到分区结束的累计
SUM(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) as 反向累计
```

### 2、AVG() - 平均值

```sql
-- 移动平均（最近3条记录）
AVG(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) as 近期平均工资

-- 部门平均工资
AVG(salary) OVER (PARTITION BY dept_id) as 部门平均工资
```

### 3、COUNT() - 计数

```sql
-- 累计计数
COUNT(*) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
) as 部门累计人数

-- 分区计数
COUNT(*) OVER (PARTITION BY dept_id) as 部门总人数
```

### 4、MIN() / MAX() - 最小/最大值

```sql
-- 部门内最高和最低工资
MAX(salary) OVER (PARTITION BY dept_id) as 部门最高工资,
MIN(salary) OVER (PARTITION BY dept_id) as 部门最低工资,

-- 历史最高值
MAX(salary) OVER (
    PARTITION BY emp_id 
    ORDER BY effective_date
) as 历史最高工资
```

## 三、 排名类窗口函数

### 1、ROW_NUMBER() - 行号

```sql
-- 为每个部门的员工按工资排序
ROW_NUMBER() OVER (
    PARTITION BY dept_id 
    ORDER BY salary DESC
) as 工资排名
```

### 2、RANK() / DENSE_RANK() - 排名

```sql
-- 排名（允许并列，可能有间隔）
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 排名,

-- 密集排名（允许并列，无间隔）
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) as 密集排名
```

### 3、NTILE() - 分桶

```sql
-- 将每个部门的员工分成4个等级
NTILE(4) OVER (PARTITION BY dept_id ORDER BY salary DESC) as 工资等级
```

## 四、位移类窗口函数

### 1、LAG() / LEAD() - 前一行/后一行

```sql
-- 与前一个月比较
LAG(sales, 1) OVER (PARTITION BY product_id ORDER BY month) as 上月销售额,
LEAD(sales, 1) OVER (PARTITION BY product_id ORDER BY month) as 下月销售额,

-- 带默认值
LAG(sales, 1, 0) OVER (PARTITION BY product_id ORDER BY month) as 上月销售额
```

### 2、FIRST_VALUE() / LAST_VALUE() - 首行/末行

```sql
-- 部门内第一个和最后一个员工的工资
FIRST_VALUE(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
) as 最早员工工资,

LAST_VALUE(salary) OVER (
    PARTITION BY dept_id 
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as 最晚员工工资
```

## 五、统计分析类窗口函数

### 1、STDDEV() / VARIANCE() - 标准差/方差

```sql
-- 部门工资的标准差和方差
STDDEV(salary) OVER (PARTITION BY dept_id) as 工资标准差,
VARIANCE(salary) OVER (PARTITION BY dept_id) as 工资方差
```

### 2、CORR() - 相关系数

```sql
-- 销售额与广告费用的相关系数
CORR(sales, ad_cost) OVER (PARTITION BY region) as 相关性
```

## 六、分布类窗口函数

### 1、PERCENT_RANK() - 百分比排名

```sql
-- 工资在部门中的百分比排名
PERCENT_RANK() OVER (PARTITION BY dept_id ORDER BY salary) as 工资百分比排名
```

### 2、CUME_DIST() - 累积分布

```sql
-- 工资的累积分布
CUME_DIST() OVER (PARTITION BY dept_id ORDER BY salary) as 工资累积分布
```

## 七、使用示例

### 1、销售分析

### 2、员工工资分析

### 3、金融数据时间序列分析

最后修改：2025 年 12 月 03 日

👆请简单粗暴地爱我。

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Spring框架依赖注入

MySQL知识扩展

Day12 - JDBC驱动安装及其简单使用方法

Java常用注解表

Electron的简介

Day13 - elm后端JDBC版管理员部分

Java常用注解表

Day14 - elm后端JDBC版商家部分

DeepSeek R1/V3 使用本地知识库

Day12 - JDBC驱动安装及其简单使用方法

Oracle窗口函数

发表评论 取消回复 使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款

Oracle窗口函数

发表评论取消回复
使用cookie技术保留您的个人信息以便您下次快速评论，继续评论表示您已同意该条款