近似聚合函数实战指南

approx_top_k

approx_histogram

approx_percentile

快速选型

approx_top_k(col, k, error)

approx_histogram(col, buckets)

approx_percentile(col, p)

前置准备

本文所有示例基于以下测试数据：

-- 服务性能日志表 CREATE TABLE service_logs ( server_id BIGINT, response_time INT, -- 响应时间（毫秒） log_level VARCHAR, -- 日志级别: error, warn, info, debug endpoint VARCHAR -- API 端点 ); -- 模拟数据 INSERT INTO service_logs VALUES (1, 100, 'info', '/api/users'), (1, 150, 'info', '/api/users'), (1, 200, 'warn', '/api/orders'), (1, 250, 'info', '/api/users'), (1, 300, 'error', '/api/payments'), (1, 350, 'info', '/api/users'), (1, 400, 'warn', '/api/orders'), (1, 450, 'info', '/api/users'), (1, 500, 'error', '/api/payments'), (1, 120, 'info', '/api/users'), (1, 180, 'warn', '/api/orders'), (1, 220, 'info', '/api/users'), (1, 280, 'error', '/api/payments'), (1, 320, 'info', '/api/users'), (1, 380, 'warn', '/api/orders'), (1, 420, 'info', '/api/users'), (1, 480, 'error', '/api/payments'), (1, 550, 'info', '/api/users'), (1, 90, 'debug', '/api/health'), (1, 600, 'error', '/api/payments'), (2, 80, 'info', '/api/users'), (2, 120, 'info', '/api/users'), (2, 200, 'warn', '/api/orders'), (2, 300, 'error', '/api/payments'), (2, 150, 'info', '/api/users'), (2, 250, 'warn', '/api/orders'), (2, 350, 'info', '/api/users'), (2, 100, 'debug', '/api/health'), (2, 180, 'info', '/api/users'), (2, 400, 'error', '/api/payments');

场景 1：TopN 近似统计（approx_top_k）

问题

从海量日志中找出出现频率最高的 N 个日志级别或 API 端点。

SQL 实现

SELECT server_id, approx_top_k(log_level, 3, 100) AS top_levels, approx_top_k(endpoint, 3, 100) AS top_endpoints FROM service_logs GROUP BY server_id;

输出:

value=count:value=count:...

参数说明

col

k

error

error 参数影响

-- 小数据集上差异不明显，大数据集（千万级以上）效果显著 SELECT approx_top_k(log_level, 3, 10) AS low_accuracy, approx_top_k(log_level, 3, 100) AS high_accuracy FROM service_logs;

100

50

场景 2：数据分布直方图（approx_histogram）

问题

了解响应时间的分布情况，识别是否存在长尾或异常值。

SQL 实现

SELECT approx_histogram(response_time, 5) AS histogram FROM service_logs;

输出:

lower=upper=count:lower=upper=count:...

解读输出

按服务器分组查看分布

SELECT server_id, approx_histogram(response_time, 4) AS histogram FROM service_logs GROUP BY server_id;

场景 3：近似分位数（approx_percentile）

问题

计算响应时间的 P50（中位数）、P90、P99，用于 SLA 监控。

SQL 实现

SELECT approx_percentile(response_time, 0.5) AS p50, approx_percentile(response_time, 0.9) AS p90, approx_percentile(response_time, 0.99) AS p99 FROM service_logs;

输出:

同时计算多个分位数

SELECT approx_percentile(response_time, ARRAY[0.5, 0.9, 0.95, 0.99]) AS percentiles FROM service_logs;

输出:

按端点分组计算 SLA

SELECT endpoint, COUNT(*) AS request_count, approx_percentile(response_time, 0.5) AS p50, approx_percentile(response_time, 0.9) AS p90, approx_percentile(response_time, 0.99) AS p99 FROM service_logs GROUP BY endpoint ORDER BY p90 DESC;

输出:

场景 4：近似值 vs 精确值对比

问题

验证近似函数的精度是否满足业务需求。

SQL 实现

SELECT -- 精确值 percentile(response_time, 0.5) AS exact_p50, percentile(response_time, 0.9) AS exact_p90, -- 近似值 approx_percentile(response_time, 0.5) AS approx_p50, approx_percentile(response_time, 0.9) AS approx_p90, -- 误差 ROUND(ABS(percentile(response_time, 0.5) - approx_percentile(response_time, 0.5)), 1) AS p50_error, ROUND(ABS(percentile(response_time, 0.9) - approx_percentile(response_time, 0.9)), 1) AS p90_error FROM service_logs;

输出:

场景 5：综合实战 — 服务性能监控看板

问题

构建一个服务性能监控查询，包含：

• 各服务器的请求量
• Top 3 错误类型
• P50/P90/P99 响应时间
• 响应时间分布

SQL 实现

SELECT server_id, COUNT(*) AS total_requests, -- Top 3 日志级别 approx_top_k(log_level, 3, 100) AS top_levels, -- 分位数 approx_percentile(response_time, 0.5) AS p50, approx_percentile(response_time, 0.9) AS p90, approx_percentile(response_time, 0.99) AS p99, -- 分布（3 个桶） approx_histogram(response_time, 3) AS distribution FROM service_logs GROUP BY server_id;

输出:

常见问题

1.

approx_top_k

approx_top_k

的返回值格式

-- 返回的是格式化的字符串，不是 JSON 或数组 -- 如果需要结构化数据，需要用字符串函数解析 SELECT approx_top_k(level, 3, 100) AS result FROM logs; -- 输出: error=5:warn=2:info=2

2.

approx_histogram

approx_histogram

的桶数选择

-- 桶数过少: 分布过于粗糙 approx_histogram(response_time, 2) -- 只有 2 个桶 -- 桶数过多: 近似效果下降，且输出字符串过长 approx_histogram(response_time, 100) -- 可能超出显示限制 -- 推荐: 5-10 个桶 approx_histogram(response_time, 5)

3.

approx_percentile

approx_percentile

不支持 FILTER 子句

-- 错误: approx_percentile 不支持 FILTER approx_percentile(response_time, 0.9) FILTER (WHERE server_id = 1) -- 正确: 用 CASE WHEN 过滤 approx_percentile(CASE WHEN server_id = 1 THEN response_time END, 0.9)

4. 空值处理

-- NULL 值会被忽略，不影响统计 SELECT approx_percentile(response_time, 0.5) FROM ( SELECT NULL AS response_time UNION ALL SELECT 100 UNION ALL SELECT 200 ); -- 输出: 150（基于 100 和 200 计算）

5. 大数据集上的性能优势

percentile

approx_percentile

性能优化建议

ARRAY[0.5, 0.9, 0.99]

SELECT

DATE_TRUNC

-- 推荐: 一次计算多个分位数 SELECT endpoint, approx_percentile(response_time, ARRAY[0.5, 0.9, 0.99]) AS percentiles FROM service_logs WHERE response_time > 0 -- 先过滤 GROUP BY endpoint; -- 不推荐: 多次扫描计算分位数 SELECT endpoint, approx_percentile(response_time, 0.5) AS p50, approx_percentile(response_time, 0.9) AS p90 -- 额外扫描 FROM service_logs GROUP BY endpoint;

相关文档

• 聚合函数参考
• approx_top_k
• approx_histogram
• approx_percentile
• 数据采样探索