mysql 实现开窗函数

原创

mob64ca12e3dd9e 2025-01-04 05:57:02 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12e3dd9e的原创作品，请联系作者获取转载授权，否则将追究法律责任

使用 MySQL 开窗函数的入门指南

在数据库操作中，开窗函数是一种强大的工具，允许我们进行复杂的数据分析。虽然对于初学者来说，开窗函数的概念可能有些抽象，但通过实际的例子，我们可以一步步掌握它。

流程概述

我们将通过以下步骤来实现 MySQL 的开窗函数：

步骤	描述
1. 创建数据库	创建一个数据库以存储我们的示例数据
2. 创建表格	创建一个表格，存储我们需要分析的数据
3. 插入数据	向表格中插入一些示例数据
4. 使用开窗函数	编写查询使用开窗函数进行数据分析

1. 创建数据库

在开始之前，我们需要创建一个数据库：

CREATE DATABASE sales_data; -- 创建一个名为 sales_data 的数据库
USE sales_data;              -- 选择使用该数据库

2. 创建表格

接下来，我们需要创建一个表格，命名为 sales，来存储销售数据。这个表格将包含 id, sell_date, amount 和 region 字段。

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 自增主键
    sell_date DATE,                      -- 销售日期
    amount DECIMAL(10, 2),              -- 销售金额
    region VARCHAR(50)                   -- 销售区域
);

3. 插入数据

对于实验数据插入，可以使用以下 SQL 代码来创建一些示例数据，以便我们进行分析：

INSERT INTO sales (sell_date, amount, region) VALUES
('2023-01-01', 100.00, 'North'),
('2023-01-02', 150.50, 'North'),
('2023-01-03', 200.00, 'South'),
('2023-01-04', 300.00, 'South'),
('2023-01-05', 250.00, 'East'),
('2023-01-06', 400.00, 'West'),
('2023-01-07', 500.00, 'East'); -- 插入多条示例数据

4. 使用开窗函数

现在是最重要的一步，使用开窗函数进行数据分析。在这个示例中，我们将分析每个区域的累计销售额。可以使用以下查询：

SELECT 
    sell_date,                             -- 选择销售日期
    region,                                -- 选择销售区域
    amount,                                -- 选择销售金额
    SUM(amount) OVER (PARTITION BY region ORDER BY sell_date) AS cumulative_amount  -- 计算累计销售额
FROM sales;

解析代码

SELECT: 用于选择表中的列。
SUM(amount) OVER (...): 这里的 SUM 聚合函数与 OVER 子句结合使用，来定义开窗函数的计算方式。
- PARTITION BY region: 根据 region（区域）进行分组，每个区域的销售数据单独计算。
- ORDER BY sell_date: 在每个区域内，按照销售日期进行排序，从而实现累计销售额的计算。

序列图

在这一过程中，可以使用序列图来直观展示操作的顺序：

sequenceDiagram
    participant Dev as 开发者
    participant DB as 数据库
    Dev->>DB: 创建数据库 `sales_data`
    Dev->>DB: 创建表格 `sales`
    Dev->>DB: 插入销售数据
    Dev->>DB: 查询累计销售额