ClickHouse 窗口函数详解：告别 GROUP BY 的局限性，实现灵活数据分析

什么是窗口函数？

窗口函数是 SQL 中一种强大的分析功能，它允许在对每一行进行计算时，能够访问到与当前行相关的多行数据。与 GROUP BY 不同，窗口函数不会将多行合并为一行，而是保留所有原始行，同时添加计算列。

直观理解

想象一个 Excel 表格：

 

 

姓名	部门	工资
张三	技术部	8000
李四	技术部	9000
王五	技术部	7500
赵六	销售部	7000

普通 GROUP BY：

sql

SELECT 部门, AVG(工资) as 平均工资 FROM 员工表 GROUP BY 部门;

结果：2行数据（部门聚合结果）

窗口函数：

sql

SELECT 姓名, 部门, 工资, AVG(工资) OVER (PARTITION BY 部门) as 部门平均工资 FROM 员工表;

结果：4行数据（保留所有原始行，新增计算列）

窗口函数的核心概念

1. 窗口定义

sql

函数名() OVER (
    PARTITION BY 分组字段
    ORDER BY 排序字段
    [窗口帧]
)

• PARTITION BY：将数据分成多个窗口（类似 GROUP BY）

• ORDER BY：在窗口内排序

• 窗口帧：定义计算范围（如前后几行）

2. 常用窗口函数分类

排名函数

sql

-- 为每行分配唯一序号
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn

-- 排名，相同值会有并列
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank

-- 密集排名，无间隔
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank

聚合函数

sql

-- 窗口内求和
SUM(salary) OVER (PARTITION BY department) as dept_total

-- 窗口内平均值
AVG(salary) OVER (PARTITION BY department) as dept_avg

-- 窗口内最大值
MAX(salary) OVER (PARTITION BY department) as dept_max

分布函数

sql

-- 百分比排名
PERCENT_RANK() OVER (ORDER BY salary) as pct_rank

-- 累计分布
CUME_DIST() OVER (ORDER BY salary) as cume_dist

实战案例：解决数据去重问题

问题场景

在区块链任务表中，每个 (start_block, end_block) 组合可能有多个版本，我们需要获取每个组合的最新版本（created_at 最大的记录）。

传统方案的局限性

sql

-- GROUP BY 无法获取完整记录
SELECT start_block, end_block, MAX(created_at)
FROM block_tasks 
GROUP BY start_block, end_block;

问题：只能返回分组字段和聚合值，无法获取其他字段的完整信息。

窗口函数解决方案

sql

SELECT * FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY start_block, end_block 
            ORDER BY created_at DESC
        ) as rn
    FROM block_tasks 
    WHERE status = 'init'
) WHERE rn = 1;

执行过程分解

原始数据：

 

 

id	start_block	end_block	status	created_at
1	1000	2000	init	2024-01-01 10:00:00
2	1000	2000	init	2024-01-02 15:00:00
3	1001	2001	init	2024-01-01 09:00:00
4	1001	2001	init	2024-01-03 14:00:00

窗口函数计算后：

 

 

id	start_block	end_block	created_at	rn
1	1000	2000	2024-01-01 10:00:00	2
2	1000	2000	2024-01-02 15:00:00	1	✅
3	1001	2001	2024-01-01 09:00:00	3
4	1001	2001	2024-01-03 14:00:00	1	✅
5	1001	2001	2024-01-02 11:00:00	2

最终结果（rn = 1）：

 

 

id	start_block	end_block	created_at
2	1000	2000	2024-01-02 15:00:00
4	1001	2001	2024-01-03 14:00:00

更多实用场景

场景1：计算移动平均

sql

-- 计算每行及前2行的平均价格
SELECT 
    date, price,
    AVG(price) OVER (
        ORDER BY date 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg
FROM stock_prices;

场景2：计算累计和

sql

-- 计算每月销售额的累计和
SELECT 
    month, sales,
    SUM(sales) OVER (
        ORDER BY month 
        ROWS UNBOUNDED PRECEDING
    ) as cumulative_sales
FROM monthly_sales;

场景3：前后行比较

sql

-- 比较当前行与前一行的差异
SELECT 
    date, revenue,
    LAG(revenue) OVER (ORDER BY date) as prev_revenue,
    revenue - LAG(revenue) OVER (ORDER BY date) as growth
FROM daily_revenue;

ClickHouse 中的窗口函数

基本语法

sql

function_name([expression]) OVER (
    [PARTITION BY expression1, expression2, ...]
    [ORDER BY expression1 [ASC|DESC], expression2 [ASC|DESC], ...]
    [frame_specification]
)

性能优化建议

1. 利用索引：确保 PARTITION BY 和 ORDER BY 的字段有合适索引

2. 避免全表排序：使用 LIMIT 限制结果集大小

3. 合理分区：数据分区可以减少单个窗口的数据量

ClickHouse 特定函数

sql

-- 获取每个分组的第一个值
first_value(column) OVER (PARTITION BY group ORDER BY time)

-- 获取每个分组的最后一个值  
last_value(column) OVER (PARTITION BY group ORDER BY time)

-- 计算分位数
quantile(0.5)(column) OVER (PARTITION BY group)

窗口函数 vs GROUP BY

 

 

特性	GROUP BY	窗口函数
输出行数	分组数量	原始行数
字段访问	只能访问分组字段和聚合值	可以访问所有原始字段
多个聚合	需要多个查询或复杂JOIN	单次查询可计算多个窗口
排序控制	无法控制选择哪条记录	明确指定排序和选择逻辑
性能	通常更快，但信息有限	稍慢，但功能更强大

实际代码示例

Go + GORM 实现

go

func GetLatestTasks(db *gorm.DB) ([]*BlockTask, error) {
    var tasks []*BlockTask
    
    sql := `
        SELECT * FROM (
            SELECT 
                *,
                ROW_NUMBER() OVER (
                    PARTITION BY start_block, end_block 
                    ORDER BY created_at DESC
                ) as rn
            FROM block_tasks 
            WHERE status = 'init'
        ) WHERE rn = 1
        ORDER BY start_block DESC
        LIMIT 100
    `
    
    err := db.Raw(sql).Scan(&tasks).Error
    return tasks, err
}

总结

窗口函数是现代 SQL 中不可或缺的强大工具，它解决了 GROUP BY 的诸多限制：

• ✅ 保留原始数据：不丢失任何行信息

• ✅ 灵活分析：支持排名、聚合、分布等多种计算

• ✅ 性能优秀：相比多次查询或复杂 JOIN，通常更高效

• ✅ 代码简洁：用声明式语法替代复杂的过程逻辑

在数据去重、移动平均、排名计算、趋势分析等场景中，窗口函数都能提供优雅而高效的解决方案。掌握窗口函数，将极大提升你的数据分析能力！