1. 引言：从“数数”到“看趋势”，数据分析的两种思维

刚接触数据库那会儿，我觉得SQL最酷的功能就是“数数”。比如，老板问我：“咱们这个月有多少订单？”我就能用一句 SELECT COUNT(*) FROM orders 潇洒地甩给他一个数字。这种感觉，就像手里有了一把万能钥匙，能打开数据仓库的大门，看到总数、总和、平均值这些宏观指标。聚合函数，比如 COUNT、SUM、AVG，就是干这个的，它们把一堆数据“压缩”成一个有意义的统计值，给你一个高度概括的结论。

但很快，我就遇到了更棘手的问题。老板不再满足于“总数是多少”，他开始问：“这个月的销售额，每天是怎么变化的？哪个客户的累计消费最先突破了一万元？和上个月同期比，每个产品的销量排名有什么变化？” 这时候，只靠“压缩”数据的聚合函数就有点力不从心了。我需要的是在保留每一行数据细节的同时，还能进行跨行的计算和比较。这就像是看电影，你不仅要知道总票房（聚合），还想知道每一分钟的票房走势（窗口）。

这就是 窗口函数 登场的时刻。它不像聚合函数那样把多行“拍扁”成一行，而是为每一行数据都开一个“窗口”，透过这个窗口，它能看见同一组内的其他行，然后进行灵活的计算。你可以把它想象成Excel里的公式，比如在每一行旁边计算一个累计和、一个排名，或者和上一行做个比较。

所以，今天我想和你聊的，就是MySQL里这对“黄金搭档”——聚合函数和窗口函数。单独用，它们各自强大；结合起来用，那才是真正的“数据分析双重魔法”，能解决许多单靠一种思维搞不定的复杂问题。我会用几个我实际踩过坑、填过坑的案例，带你看看它们怎么协同作战，让你从“会写SQL”升级到“善用SQL”。

2. 聚合函数：数据的“宏观望远镜”

我们先来好好认识一下这位老朋友——聚合函数。它就像数据分析里的“宏观望远镜”，帮你拉远视角，看清森林的全貌。

2.1 核心五虎将：COUNT, SUM, AVG, MAX, MIN

这几个函数是SQL的基石，几乎每个数据分析查询都离不开它们。

  • COUNT()：最基础的计数器。COUNT(*) 数所有行，COUNT(column) 数该列非空的行数。这里有个小坑我踩过：如果你想统计有多少个不同的客户下单，记得用 COUNT(DISTINCT customer_id)，直接用 COUNT(customer_id) 可能会重复计算同一个客户的多次订单。

    -- 统计总订单数
SELECT COUNT(*) AS total_orders FROM orders;
-- 统计有多少个不同的客户下了单
SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

  • SUM() 与 AVG()：总和与均值。SUM 很简单，就是加总。但 AVG 要注意，它默认会忽略 NULL 值。如果你的数据里 NULL 代表0，那计算结果可能和预期不符，需要先用 IFNULL 或 COALESCE 函数处理一下。

    -- 计算总销售额和平均订单金额（忽略金额为NULL的订单）
SELECT
    SUM(amount) AS total_sales,
    AVG(amount) AS avg_order_amount
FROM orders;
-- 如果想把NULL当0算进平均值
SELECT AVG(COALESCE(amount, 0)) AS avg_order_amount_with_null FROM orders;

  • MAX() 与 MIN()：极值探索者。它们不仅能找数字的最大最小值，还能找日期最晚/最早，甚至文本按字母排序的最大/最小。有一次我用它快速找出了系统里最早注册的一批用户，做了一次怀旧营销，效果出奇的好。

2.2 GROUP BY：让聚合“分而治之”

单独使用聚合函数，得到的是全局统计。但真实世界的数据需要分类讨论。这时 GROUP BY 就来了。它把数据分成不同的“组”，然后在每个组内分别进行聚合计算。

比如，老板问：“每个产品类别的总销售额和平均售价是多少？” 这个问题就需要先按类别分组。

SELECT
    category,
    SUM(amount) AS category_sales,
    AVG(price) AS avg_price,
    COUNT(*) AS order_count
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY category;

这里有个非常重要的点：SELECT 后面跟着的列，要么被包含在 GROUP BY 子句里，要么被聚合函数包裹。 这就是所谓的“单值规则”，违反它数据库会报错，因为引擎不知道非聚合的列该返回组里的哪一行。

2.3 HAVING：对聚合结果进行筛选

WHERE 子句是在分组前对原始行进行过滤，而 HAVING 子句是在分组后对聚合结果进行过滤。这是另一个容易混淆的地方。

举个例子：找出总销售额超过10万元的产品类别。

SELECT
    category,
    SUM(amount) AS category_sales
FROM orders
JOIN products ON orders.product_id = products.id
GROUP BY category
HAVING category_sales > 100000; -- 这里过滤的是聚合后的结果

你不能用 WHERE SUM(amount) > 100000，因为 WHERE 执行时，分组和聚合还没发生呢。记住这个顺序：WHERE -> GROUP BY -> 聚合计算 -> HAVING。

聚合函数这套“宏观望远镜”用熟了，你能快速回答关于规模、总量、平均水平的问题。但当你需要深究“每个个体的趋势如何”、“在组内的相对位置怎样”时，就需要请出另一位魔法师了。

3. 窗口函数：数据的“微观显微镜”

如果说聚合函数是望远镜，那窗口函数就是一台高倍率的“显微镜”。它允许你仔细观察每一行数据，同时又能感知它所在的“环境”（窗口）。

3.1 OVER()子句：定义你的观察窗口

所有窗口函数的魔法都始于 OVER() 子句。它定义了计算发生的“窗口范围”。这个窗口可以很简单，包含整个结果集，也可以很精细，通过 PARTITION BY 和 ORDER BY 来划分和排序。

  • 整个结果集作为窗口：OVER() 里面什么都不写，意思就是“以所有行为窗口”。比如计算每一行订单金额占总销售额的比例：

    SELECT
    order_id,
    amount,
    amount / SUM(amount) OVER () AS sales_ratio
FROM orders;

    这里的 SUM(amount) OVER () 会计算所有订单的总金额，然后这个总值被用于每一行的除法计算。你看到了吗？我们既保留了每一行的细节（order_id, amount），又得到了一个基于全局的衍生值（sales_ratio）。这是聚合函数做不到的。

  • 使用 PARTITION BY 分组：这相当于在窗口内再建“隔间”。比如，我想看每个客户内部的订单累计金额，而不是全局累计。

    SELECT
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS customer_running_total
FROM orders;

    PARTITION BY customer_id 意味着为每个客户单独开一个窗口，累计和的计算在每个客户内部独立进行，遇到新客户就清零重算。这非常适用于分析用户生命周期价值、消费轨迹等。

  • 使用 ORDER BY 排序：这决定了窗口内行的计算顺序。在上面的例子里，ORDER BY order_date 保证了累计和是按时间顺序累加的。ORDER BY 对于计算移动平均、排名等至关重要。

3.2 三大类窗口函数实战

窗口函数家族庞大，主要分三类：聚合类、排名类、取值类。

1. 聚合类窗口函数：就是 SUM, AVG, COUNT, MAX, MIN 这些老朋友，但加上 OVER() 子句后，它们不再压缩行，而是为每一行生成一个聚合值。

-- 计算每个订单的金额，以及该订单所属客户的平均订单金额
SELECT
    customer_id,
    order_id,
    amount,
    AVG(amount) OVER (PARTITION BY customer_id) AS avg_customer_order
FROM orders;

这样你一眼就能看出某个订单是高于还是低于该客户的平均水平。

2. 排名类函数：ROW_NUMBER, RANK, DENSE_RANK 这三个函数都用于排名，但处理“并列”情况的方式不同。

  • ROW_NUMBER()：不管值是否相同，都给出连续的唯一序号（1,2,3,4...）。
  • RANK()：值相同的行排名相同，但会跳过后续名次（1,2,2,4...）。
  • DENSE_RANK()：值相同的行排名相同，且名次连续不跳过（1,2,2,3...）。

实战场景：找出每个销售区域销售额前三名的销售员。

SELECT *
FROM (
    SELECT
        region,
        salesperson,
        sales_amount,
        RANK() OVER (PARTITION BY region ORDER BY sales_amount DESC) AS sales_rank
    FROM sales_records
) AS ranked_sales
WHERE sales_rank <= 3;

这里用 RANK() 很合适，因为它允许并列（比如两个销售员并列第一），并且后续排名会跳过（并列第一后，下一个是第三名）。如果你希望并列第一后下一个是第二名，就用 DENSE_RANK()。

3. 取值类函数：LAG 和 LEAD 这是我个人最爱的窗口函数之一，用于访问“前面”或“后面”行的数据，在分析时间序列数据（如日活、销售额）时无敌好用。

-- 计算每日销售额，以及与前一天相比的增长率
SELECT
    sale_date,
    daily_sales,
    LAG(daily_sales, 1) OVER (ORDER BY sale_date) AS previous_day_sales,
    (daily_sales - LAG(daily_sales, 1) OVER (ORDER BY sale_date)) / LAG(daily_sales, 1) OVER (ORDER BY sale_date) * 100 AS growth_rate_percent
FROM daily_sales_summary;

LAG(column, n) 获取当前行之前第n行的值，LEAD(column, n) 则获取之后第n行的值。有了它们，环比、同比分析变得异常简单。

4. 双剑合璧：聚合与窗口的协同实战案例

单独使用它们已经很强了，但真正的魔法在于结合。下面我分享两个真实的复合场景。

4.1 案例一：计算“贡献度”与“组内排名”

假设你是一个电商数据分析师，老板想知道：每个商品类别的总销售额（聚合思维），以及每个类别内部，单个商品销售额占该类别总销售额的比例（窗口思维），同时还要给类别内的商品按销售额排个名（窗口思维）。

用一句SQL搞定：

SELECT
    category,
    product_name,
    product_sales,
    -- 聚合函数 + OVER(PARTITION BY ...): 计算类别总销售额
    SUM(product_sales) OVER (PARTITION BY category) AS category_total_sales,
    -- 窗口计算：单个商品贡献度
    ROUND(product_sales * 100.0 / SUM(product_sales) OVER (PARTITION BY category), 2) AS contribution_percent,
    -- 窗口函数：类别内排名
    RANK() OVER (PARTITION BY category ORDER BY product_sales DESC) AS rank_in_category
FROM product_sales_summary;

在这个查询里：

  1. SUM(product_sales) OVER (PARTITION BY category) 是一个聚合窗口函数，它为每一行计算了其所属类别的销售总和。
  2. 利用这个“类别总和”，我们轻松算出了贡献百分比。
  3. RANK() 则在每个类别分区内，根据销售额进行排名。

这就是协同的威力：聚合函数（SUM）在窗口的上下文中，为每一行提供了关键的上下文信息（组总和），使得更精细的分析成为可能。

4.2 案例二：识别“头部客户”与“连续增长”

另一个常见需求：找出累计消费金额最高的前10%的客户（头部客户），并分析这些头部客户最近3个月是否有连续消费增长的趋势。

这个需求需要分两步，但每一步都融合了两种思维。

第一步：用窗口函数计算累计百分比，识别头部客户。

WITH customer_totals AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id -- 这里是聚合函数的GROUP BY
),
ranked_customers AS (
    SELECT
        customer_id,
        total_spent,
        -- 关键：用窗口函数计算累计百分比
        SUM(total_spent) OVER (ORDER BY total_spent DESC) AS running_total,
        SUM(total_spent) OVER () AS grand_total,
        (SUM(total_spent) OVER (ORDER BY total_spent DESC)) / SUM(total_spent) OVER () AS cumulative_ratio
    FROM customer_totals
)
SELECT *
FROM ranked_customers
WHERE cumulative_ratio <= 0.1; -- 筛选出累计贡献在前10%的客户

这里，我们先通过聚合（GROUP BY + SUM）得到每个客户的总消费。然后，在第二个CTE里，我们使用窗口函数 SUM() OVER (ORDER BY ...) 对客户总消费进行降序累计求和，并用另一个 SUM() OVER () 得到全局总和，从而算出累计占比。这是一个典型的先聚合再开窗的模式。

第二步：对筛选出的头部客户，用LAG分析月度消费趋势。

WITH head_customers AS (/* 上面识别头部客户的SQL */),
monthly_sales AS (
    SELECT
        hc.customer_id,
        DATE_FORMAT(o.order_date, '%Y-%m') AS sale_month,
        SUM(o.amount) AS monthly_amount
    FROM head_customers hc
    JOIN orders o ON hc.customer_id = o.customer_id
    WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 3 MONTH)
    GROUP BY hc.customer_id, DATE_FORMAT(o.order_date, '%Y-%m') -- 再次聚合，得到月消费
)
SELECT
    customer_id,
    sale_month,
    monthly_amount,
    LAG(monthly_amount, 1) OVER (PARTITION BY customer_id ORDER BY sale_month) AS prev_month_amount,
    CASE
        WHEN LAG(monthly_amount, 1) OVER (PARTITION BY customer_id ORDER BY sale_month) IS NOT NULL
        THEN monthly_amount > LAG(monthly_amount, 1) OVER (PARTITION BY customer_id ORDER BY sale_month)
        ELSE NULL
    END AS is_growth
FROM monthly_sales
ORDER BY customer_id, sale_month;

这一步，我们先对头部客户的近期订单按月聚合（GROUP BY）。然后，对每个客户（PARTITION BY customer_id）按月序（ORDER BY sale_month）使用 LAG 函数，获取其上个月的消费额，从而判断本月是否增长。这又是一个聚合后开窗的经典组合。

通过这两个案例，你会发现，很多复杂的业务分析，其实就是“聚合”和“窗口”两种思维交替或嵌套使用的过程。聚合帮你提炼摘要，窗口帮你洞察细节和关系。

5. 性能陷阱与优化心法

功能强大，随之而来的就是性能挑战。窗口函数和聚合函数用不好，很容易写出拖垮数据库的查询。

5.1 窗口函数的性能杀手：全表排序与大规模分区

窗口函数的核心操作之一是排序（如果使用了 ORDER BY）。OVER (PARTITION BY A ORDER BY B) 这个子句，数据库底层通常需要先按A分组，然后在每个组内按B排序。如果A的取值很少（分区很大）或者B上没有索引，就会导致大量的全表扫描和文件排序（Using filesort），在MySQL中尤其消耗资源。

优化心法1：利用索引覆盖 为 OVER() 子句中 PARTITION BY 和 ORDER BY 用到的列建立复合索引，可以极大提升性能。例如，对于 OVER (PARTITION BY user_id ORDER BY created_at)，建立一个 (user_id, created_at) 的索引，数据库可以直接利用索引的有序性来避免额外的排序操作。

优化心法2：减少窗口范围 默认情况下，OVER (ORDER BY ...) 的窗口范围是从结果集开头到当前行（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）。如果你只需要最近几行的数据（比如计算3期移动平均），一定要明确指定窗口框架（FRAME），减少计算量。

-- 计算最近3个订单的平均金额（包括当前行）
SELECT
    order_id,
    amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM orders;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 明确告诉数据库只关注当前行及前两行，而不是从头开始的所有行。

5.2 聚合函数与GROUP BY的优化：警惕中间结果集膨胀

GROUP BY 操作会产生中间结果集。如果分组键（GROUP BY的列）基数很大（即唯一值很多），或者SELECT中包含了大量未被聚合的、非索引的列，可能导致临时表巨大，内存放不下就要写磁盘，速度骤降。

优化心法3：只SELECT需要的列 在GROUP BY查询中，尽量避免 SELECT *。只选择你真正需要聚合的列和分组列。多余的列不仅增加I/O，还可能迫使MySQL使用更慢的临时表算法。

优化心法4：考虑使用派生表（子查询）提前过滤 如果原始表很大，但你需要聚合的数据只是其中一小部分，可以先在子查询中用WHERE条件过滤掉无关数据，再进行聚合和开窗。

-- 不好的写法：先对所有数据开窗再过滤
SELECT * FROM (
    SELECT ... , ROW_NUMBER() OVER (PARTITION BY ...) as rn
    FROM huge_table
) t WHERE rn = 1 AND create_date > '2023-01-01';

-- 更好的写法：先过滤数据，减少处理量
SELECT * FROM (
    SELECT ... , ROW_NUMBER() OVER (PARTITION BY ...) as rn
    FROM huge_table
    WHERE create_date > '2023-01-01' -- 提前过滤
) t WHERE rn = 1;

5.3 组合使用时的执行顺序理解

理解SQL的执行顺序对优化至关重要。当聚合函数和窗口函数出现在同一个查询时，记住：

  1. FROM / JOIN 确定数据源
  2. WHERE 过滤行
  3. GROUP BY 分组
  4. 聚合函数（如SUM(column)）计算
  5. HAVING 过滤组
  6. 窗口函数 计算（OVER子句中的计算发生在此刻！）
  7. SELECT 选择列
  8. DISTINCT
  9. ORDER BY
  10. LIMIT

关键点：窗口函数是在GROUP BY和聚合发生之后才计算的。这意味着，窗口函数中引用的列，已经是聚合后的结果（如果前面有GROUP BY的话）。这也解释了为什么我们可以在窗口函数里使用聚合函数的结果列。

6. 思维跃迁：从“写查询”到“设计分析”

掌握了这些技术细节后，最重要的转变是思维上的：从“我该怎么写出这个查询”变成“我该用哪种数据视角来解决这个问题”。

当你拿到一个新的分析需求时，可以快速在脑子里过一遍：

  • 如果问题只关心总体概括（多少、总和、平均），用聚合函数 + GROUP BY。
  • 如果问题需要在保留每一行细节的同时，考察它在上下文中的关系（排名、累计、前后对比），用窗口函数。
  • 如果问题两者兼有（既要知道组的总量，又要看组内个体的相对位置），那就组合使用。通常的模式是：先用子查询或CTE进行必要的聚合和过滤，再对结果使用窗口函数进行精细加工。

我自己的经验是，在报表开发和数据探查中，窗口函数的使用频率越来越高。它让很多原本需要多次查询、在应用层拼接逻辑的复杂分析，得以在数据库一层用一句SQL优雅地完成。这不仅仅是代码的简化，更是性能的提升和逻辑的清晰化。刚开始可能会觉得OVER()子句有点绕，但多写几次，习惯了这种“为每一行打开一个窗口”的思维方式后，你会发现数据分析的视野一下子被打开了。

# 数据分析
Logo
DAMO开发者矩阵

DAMO开发者矩阵，由阿里巴巴达摩院和中国互联网协会联合发起，致力于探讨最前沿的技术趋势与应用成果，搭建高质量的交流与分享平台，推动技术创新与产业应用链接，围绕“人工智能与新型计算”构建开放共享的开发者生态。

更多推荐

cover

InternW0：面向高效现实世界交互的基础物理世界模型

avatar DAMO开发者矩阵
cover

世界模型的内涵与发展

avatar DAMO开发者矩阵

2026深度解读：Work Agent长程任务的执行机制与能力边界

AI的交互形态正在发生底层转变。早期大模型只能完成单轮问答，用户抛出问题，模型返回一段文本，对话随即结束；后续多轮对话形态出现，支持在上下文内持续沟通，但整体依然由用户主导，每一步操作都需要人类给出新指令。工具调用能力的落地，让模型可以对接检索、计算等外部能力，突破纯文本生成的限制。而Work Agent的出现，把工具调用串联成可自主推进的任务链，长程任务执行能力，正是Work Agent与传统聊

avatar DAMO开发者矩阵