KingbaseES中窗口函数与分组聚合的写法差异及执行计划解读优化技巧详细总结

Shen BingQiang 2026-07-09 14:17

一、从基础说起：分组聚合和窗口函数到底是干啥的

咱们在写 SQL 的时候，经常需要对数据进行汇总计算，比如算个总数、平均值、最大值啥的。最直接的做法就是用 GROUP BY 把数据按某个列分组，然后对每组进行聚合。例如统计每个产品的总销量、每个部门的平均工资。但有时候我们又想保留每一条原始数据，同时看到分组的计算结果，比如在每条订单后面显示本月的累计销售额，或者给每个员工显示本部门的平均工资。这时候如果还用 GROUP BY，数据行数就会减少，丢失细节信息。这种“既要保留每一行，又要看到聚合结果”的需求，就要靠 窗口函数 来解决了。

窗口函数是 KingbaseES 里一个非常强大的功能，它能在不改变结果行数的前提下，对每一行进行基于某个“窗口”范围的计算。这个窗口范围可以通过 PARTITION BY 分区，也可以通过 ORDER BY 排序后定义成移动范围。听起来有点绕，其实用法和普通聚合函数很像，只是多了个 OVER() 子句。

为了更好地理解两者的区别，我们先从最基础的用法开始，用同一个实际场景来演示两种写法。这里我们统一使用 KingbaseES 的 SQL 语法。

二、分组聚合的写法与示例

分组聚合是大家最熟悉的操作，语法简单：SELECT 分组字段, 聚合函数(列) FROM 表 GROUP BY 分组字段。下面建一张销售表，演示如何统计每个产品的总销量和总金额。

-- 技术栈：KingbaseES SQL

-- 建表：销售记录表
CREATE TABLE sales (
    id integer,               -- 主键标识
    product varchar(20),      -- 产品名称
    amount numeric(10,2),     -- 销售金额
    sale_date date            -- 销售日期
);

-- 插入几条测试数据
INSERT INTO sales VALUES 
(1, '产品A', 100.00, '2025-01-01'),
(2, '产品A', 200.00, '2025-01-02'),
(3, '产品B', 150.00, '2025-01-01'),
(4, '产品B', 250.00, '2025-01-02'),
(5, '产品C', 300.00, '2025-01-01'),
(6, '产品A', 180.00, '2025-01-03');

-- 分组聚合：按产品分组，计算每个产品的总金额和总笔数
SELECT 
    product,                         -- 分组字段
    COUNT(*) AS 总笔数,               -- 每组的行数
    SUM(amount) AS 总金额             -- 每组金额合计
FROM sales
GROUP BY product                     -- 按 product 分组
ORDER BY product;                    -- 排序方便查看

执行结果会输出三行，每行一个产品，分别显示它的总笔数和总金额。注意看：原始数据有 6 行，分组后只剩 3 行，那些具体的销售日期、id 信息都没有了。这就是分组聚合最大的特点：结果集行数等于分组数，只能看到每个组的汇总值，看不到组内的每一条记录。

如果我们想同时看到每一条销售记录，并且让它后面跟着该产品到目前为止的累计金额，那分组聚合就做不了了，因为 GROUP BY 不允许在 SELECT 里同时出现非聚合的原始列（比如 sale_date、id）和分组字段外的聚合结果。这个时候就要请出窗口函数。

三、窗口函数的写法与示例

窗口函数的语法是在聚合函数后面加一个 OVER() 子句。OVER() 里可以指定 PARTITION BY（类似分组）、ORDER BY（排序，用于累积或排名）、以及 ROWS / RANGE 等窗口范围。下面用同一个 sales 表，演示如何实现“每条记录显示该产品累计到当前日期的总金额”。

-- 技术栈：KingbaseES SQL

-- 窗口函数：计算每个产品的累计销售额（按日期顺序）
SELECT 
    id,                                    -- 原始列
    product,                               -- 原始列
    sale_date,                             -- 原始列
    amount,                                -- 原始列
    SUM(amount) OVER (                     -- 窗口聚合
        PARTITION BY product               -- 按产品分区，类似 GROUP BY
        ORDER BY sale_date                 -- 按日期排序，定义累计顺序
        ROWS BETWEEN UNBOUNDED PRECEDING   -- 窗口起始：分区第一行
        AND CURRENT ROW                    -- 窗口结束：当前行
    ) AS 累计金额                          -- 得到累计值
FROM sales
ORDER BY product, sale_date;

注意，这里我们使用了 SUM(...) OVER(...)，结果集的行数与原始表完全一致（6 行）。每一行除了能看到自己的原始信息，还能看到一个累计金额，这个累计金额是针对该产品、从开始到当前行的销售金额之和。这就是窗口函数的魔力——在不减少行数的情况下完成聚合计算。

窗口函数不只可以用于 SUM，COUNT、AVG、MAX、MIN 等聚合函数都支持。另外还有专门的窗口函数如 ROW_NUMBER()、RANK()、DENSE_RANK()、LAG()、LEAD() 等，它们也只有窗口函数能实现。

比如给每个产品按金额高低排名：

-- 窗口函数：按产品分组内部，对金额进行排名
SELECT 
    id,
    product,
    amount,
    RANK() OVER (
        PARTITION BY product
        ORDER BY amount DESC     -- 金额降序，金额大的排前面
    ) AS 金额排名
FROM sales
ORDER BY product, 金额排名;

这个查询返回每一行以及它在所属产品内的排名。如果同一个产品有两笔相同金额，RANK() 会并列，然后跳过后续排名（比如并列第一后下一个是第三）。DENSE_RANK() 则不会跳过，并列第一后下一个是第二。

四、写法差异的核心对比

为了更清晰地看出差异，我们列个对比表（不用表格，文字描述）：

行数变化：GROUP BY 会压缩行，每个分组只输出一行；窗口函数保持原行数不变，聚合结果附加到每一行。
SELECT 字段：GROUP BY 的 SELECT 中只能包含分组列和聚合函数，不能包含非分组的原始列（除非它们也出现在分组中，或者被聚合）；窗口函数可以随意选择任何列，窗口聚合结果作为一个新列出现。
使用场景：GROUP BY 用于生成汇总报表，比如“每个产品总销量”；窗口函数用于“给每条记录配上上下文汇总”，比如“查看每条订单的同时，显示该产品累计销量”。
性能差异：GROUP BY 通常比窗口函数快，因为窗口函数需要对数据进行排序和额外的内存/临时文件处理，尤其在 ORDER BY 排序量大的时候。但窗口函数写起来更方便，避免了多次子查询和自连接。

五、执行计划解读与优化技巧

我们要做性能优化，首先得看懂执行计划。KingbaseES 里用 EXPLAIN 命令查看查询计划。下面分别解读分组聚合和窗口函数的计划。

1. 分组聚合的执行计划

-- 查看分组聚合的执行计划
EXPLAIN ANALYZE
SELECT product, SUM(amount) AS total
FROM sales
GROUP BY product;

输出可能像这样（简化）：

HashAggregate  (cost=XXX..XXX rows=3 width=44) (actual time=0.1..0.2 rows=3)
  Group Key: product
  ->  Seq Scan on sales  (cost=0..XXX rows=6 width=40) (actual time=0.0..0.0 rows=6)

关键点：

使用了 HashAggregate，说明 KingbaseES 通过哈希算法来分组。如果数据量大且内存足够，这通常很快。
Group Key 显示分组的字段。
如果分组字段有索引，可能会使用 GroupAggregate（需要排序），效率也高。

优化技巧：给分组字段建索引可以加速分组操作，不过对于小表效果不明显。当分组字段很多或表很大时，索引能显著减少扫描成本。

2. 窗口函数的执行计划

-- 查看窗口函数的执行计划
EXPLAIN ANALYZE
SELECT id, product, amount,
       SUM(amount) OVER (PARTITION BY product ORDER BY sale_date) AS 累计
FROM sales;

输出可能像：

WindowAgg  (cost=XXX..XXX rows=6 width=84) (actual time=0.2..0.3 rows=6)
  ->  Sort  (cost=XXX..XXX rows=6 width=52) (actual time=0.1..0.2 rows=6)
        Sort Key: product, sale_date
        ->  Seq Scan on sales  (cost=0..XXX rows=6 width=52)

看到了吧，窗口函数先做了一次 Sort，这是因为 OVER() 里指定了 ORDER BY sale_date，数据库必须先把数据按分区和排序列排好，然后才能进行窗口计算。这个排序是窗口函数最常见的性能瓶颈。优化技巧：

尽量减少窗口函数中 ORDER BY 列的宽度，如果可以，用整数或日期代替长字符串排序。
建立合适的联合索引，例如 CREATE INDEX idx_sales_product_date ON sales(product, sale_date);，这样 Sort 步骤就有可能被消除，因为索引本身已经有序，扫描后直接送给 WindowAgg，避免额外的排序操作。
避免在大表上使用 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 这种默认窗口，因为默认就是它，不需要写。但是如果用 RANGE 或更复杂的窗口范围，可能会增加计算开销。
如果不需要排序，可以只写 PARTITION BY 而不写 ORDER BY，这样窗口函数会以任意顺序计算全分区聚合，不会触发排序。不过这时结果就是分区内所有行的总和，每行都一样，没有累计意义。

3. 更复杂的优化场景

有时候我们需要同时做多种窗口计算，比如既要排名，又要累计。这时候如果分别写两个窗口函数，KingbaseES 可能会分别排序多次。我们可以利用 WINDOW 子句定义一个窗口，然后在多个窗口函数中引用它，避免重复排序。

-- 使用 WINDOW 子句定义窗口，多个窗口函数复用同一个排序
SELECT 
    id, product, amount,
    RANK() OVER w AS rnk,
    SUM(amount) OVER w AS 累计
FROM sales
WINDOW w AS (PARTITION BY product ORDER BY sale_date)
ORDER BY product, sale_date;

这样只进行一次排序，效率更高。

六、典型应用场景

分组聚合和窗口函数各有擅长，选择不当会导致代码复杂或性能低下。常见场景如下：

场景一：月度销售汇总报表
需要按月份统计总销售额，不需要看到每天细节。用 GROUP BY sale_month 最直接，快且简单。
场景二：查看每笔订单的同时，显示该客户的历史累计消费
必须保留每一行，并附带累计值。用窗口函数 SUM(amount) OVER (PARTITION BY client ORDER BY order_date)。
场景三：计算各部门员工薪资排名
RANK() OVER (PARTITION BY dept ORDER BY salary DESC)，窗口函数最佳。
场景四：计算移动平均，比如最近3天的平均气温
窗口函数配合 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 完美解决。
场景五：找出每个产品最新一条记录
用 ROW_NUMBER() OVER (PARTITION BY product ORDER BY sale_date DESC) 然后筛选序号为1的行。窗口函数 + 子查询是经典做法。
场景六：求每个分类下占比，比如每个产品销售额占总体的百分比
窗口函数 amount / SUM(amount) OVER () 得到占比，如果还要按分类，就加上 PARTITION BY category。

可以看到，窗口函数在处理“明细 + 聚合”混合需求时非常灵活，而分组聚合适合纯粹的汇总场景。

七、技术优缺点与注意事项

优缺点

分组聚合
优点：语法简单、执行效率高（尤其在全表扫描 + 哈希聚合时），内存消耗相对较低。
缺点：会丢失行级细节，无法在同一结果中同时查看原始数据和聚合值；如果要实现累计、排名等效果，需要多次子查询或自连接，代码非常难写。

窗口函数
优点：能够在一行内同时展示原始列和聚合列，支持排名、累计、移动计算等复杂逻辑，代码简洁易懂。
缺点：需要排序，大数据量下可能消耗大量内存和临时磁盘 I/O；多个窗口函数如果没有优化可能导致重复排序；部分数据库对窗口函数支持有限，但 KingbaseES 完全支持 SQL 标准。

注意事项

谨慎使用大排序：如果在窗口函数的 ORDER BY 中使用多列或长字符串列，排序开销会剧增。可以考虑将排序列改为整数编码。
内存设置：KingbaseES 的参数 work_mem 影响排序和哈希的内存使用。如果窗口函数导致大量磁盘排序，可以适当调大 work_mem（但注意不要过大导致其他查询内存不足）。
窗口函数中 PARTITION BY 的列越少越好：分区过多会导致每个分区很小，虽然排序容易，但分区数量本身也会带来计算开销。
避免在 SELECT 中同时大量使用多个不同窗口：尽量用 WINDOW 子句复用。
测试真实数据量：开发环境用小数据看不出问题，生产环境千万级数据时，窗口函数的排序可能成为瓶颈。务必用 EXPLAIN ANALYZE 实际测试。
窗口函数不能直接用于 WHERE 子句：因为窗口函数的计算发生在 WHERE 过滤之后、ORDER BY 之前。如果想对窗口结果过滤，需要在外层再包一层子查询。

八、总结

分组聚合和窗口函数就像是 SQL 工具箱中的两把利器，一个负责“压缩汇总”，一个负责“增强明细”。理解它们的本质区别——有没有 OVER()，行数变不变——是正确选用的关键。实际开发中，很多需求都可以用两种方式实现，但性能差异可能十倍百倍。例如求每个产品的最新日期，如果用 GROUP BY 结合 MAX(date) 只能得到最新日期，拿不到其他字段；如果用子查询 + 关联，写起来绕，性能也差；而窗口函数一行搞定。反过来，如果只是统计每个分类的总数，非要用窗口函数，就会多付出排序的代价，完全没必要。

记住几个口诀：

要汇总，不要细节 → GROUP BY
要细节，也要汇总 → 窗口函数
要排名、累计、移动 → 窗口函数
要性能，能接受子查询 → 可以放弃窗口函数，但代码会变丑

最后，不管用哪种，别忘了用 EXPLAIN 看看执行计划，看看有没有多余的排序，是不是走了索引。优化无定式，但掌握了窗口函数和分组聚合的“脾气”，你就能写出又快又漂亮的 SQL 了。

KingbaseES中窗口函数与分组聚合的写法差异及执行计划解读优化技巧详细总结

一、从基础说起：分组聚合和窗口函数到底是干啥的

二、分组聚合的写法与示例

三、窗口函数的写法与示例

四、写法差异的核心对比

五、执行计划解读与优化技巧

1. 分组聚合的执行计划

2. 窗口函数的执行计划

3. 更复杂的优化场景

六、典型应用场景

七、技术优缺点与注意事项

优缺点

注意事项

八、总结

Comments

关联文章

AI助手

一、从基础说起：分组聚合和窗口函数到底是干啥的

二、分组聚合的写法与示例

三、窗口函数的写法与示例

四、写法差异的核心对比

五、执行计划解读与优化技巧

1. 分组聚合的执行计划

2. 窗口函数的执行计划

3. 更复杂的优化场景

六、典型应用场景

七、技术优缺点与注意事项

优缺点

注意事项

八、总结

同分类面试题

Comments

关联文章

AI助手