Lakehouse 排名与分位分析指南

概述

排名与分位分析是数据分析中的高级场景，用于计算数据在分组内的相对位置、百分比分位等。云器 Lakehouse 提供完整的窗口函数支持，包括排名函数、偏移函数和聚合窗口函数。本指南按业务场景分类，帮助你快速掌握高效的排名与分位分析方法。

涉及的 SQL 命令

ROW_NUMBER()

RANK()

DENSE_RANK()

PERCENT_RANK()

LAG()

LEAD()

AVG() OVER (... ROWS BETWEEN)

PERCENTILE()

前置准备

以下示例使用模拟的员工绩效表

performance

-- 创建测试表 CREATE TABLE IF NOT EXISTS performance ( emp_id INT, emp_name STRING, dept STRING, score DOUBLE, review_date DATE ); -- 插入测试数据 INSERT INTO performance VALUES (1, 'Alice', 'Engineering', 95, '2024-06-01'), (2, 'Bob', 'Engineering', 85, '2024-06-01'), (3, 'Carol', 'Engineering', 95, '2024-06-01'), (4, 'David', 'Marketing', 88, '2024-06-01'), (5, 'Eve', 'Marketing', 92, '2024-06-01'), (6, 'Frank', 'Marketing', 78, '2024-06-01'), (7, 'Grace', 'HR', 90, '2024-06-01'), (8, 'Henry', 'HR', 85, '2024-06-01');

基础排名

使用排名函数计算员工在部门内的绩效排名。Lakehouse 提供三种排名函数，行为略有不同。

-- 三种排名函数对比 SELECT emp_name, dept, score, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY score DESC) as row_num, RANK() OVER (PARTITION BY dept ORDER BY score DESC) as rank, DENSE_RANK() OVER (PARTITION BY dept ORDER BY score DESC) as dense_rank FROM performance ORDER BY dept, row_num;

结果说明：

排名函数区别

ROW_NUMBER()

RANK()

DENSE_RANK()

百分比排名

使用

PERCENT_RANK()

-- 计算百分比排名 SELECT emp_name, dept, score, ROUND(PERCENT_RANK() OVER (PARTITION BY dept ORDER BY score DESC), 2) as pct_rank FROM performance ORDER BY dept, pct_rank DESC;

结果说明：

PERCENT_RANK

前后行对比

使用

LAG()

LEAD()

-- 计算部门内员工的分数差值 SELECT emp_name, dept, score, LAG(score, 1) OVER (PARTITION BY dept ORDER BY score DESC) as prev_score, score - LAG(score, 1) OVER (PARTITION BY dept ORDER BY score DESC) as diff FROM performance ORDER BY dept, score DESC;

结果说明：

LAG

NULL

nan

IS NULL

移动平均

使用窗口帧（ROWS BETWEEN）计算滑动窗口的统计值，适用于趋势分析。

-- 计算部门内按分数排序的移动平均（当前行及前两行） SELECT emp_name, dept, score, ROUND(AVG(score) OVER ( PARTITION BY dept ORDER BY score DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 1) as moving_avg_3 FROM performance ORDER BY dept, score DESC;

结果说明：

分位数计算

使用

PERCENTILE()

-- 计算各部门分数的中位数和四分位数 SELECT dept, PERCENTILE(score, 0.5) as median, PERCENTILE(score, 0.25) as q1, PERCENTILE(score, 0.75) as q3, MIN(score) as min_score, MAX(score) as max_score FROM performance GROUP BY dept ORDER BY dept;

结果说明：

PERCENTILE

PERCENTILE(score, ARRAY(0.25, 0.5, 0.75))

清理测试数据

完成排名分析验证后，建议清理测试表：

-- 删除测试表 DROP TABLE IF EXISTS performance;

UNDROP TABLE

注意事项

1. 窗口函数执行顺序：窗口函数在

   WHERE

   WHERE
   和

   GROUP BY

   GROUP BY
   之后执行，不能直接在

   WHERE

   WHERE
   中使用窗口函数结果。如需过滤，请使用

   QUALIFY

   QUALIFY
   或子查询。
2. NULL 值排序：

   ORDER BY

   ORDER BY
   默认将

   NULL

   NULL
   排在最后（DESC）或最前（ASC），可使用

   NULLS FIRST/LAST

   NULLS FIRST/LAST
   控制。
3. 性能优化：

   PARTITION BY

   PARTITION BY
   列建议选择基数适中的列，避免单个分区数据过大。
4. PERCENT_RANK 语法：

   PERCENT_RANK()

   PERCENT_RANK()
   不接受参数，直接写

   PERCENT_RANK() OVER (...)

   PERCENT_RANK() OVER (...)
   。

相关文档

• 窗口函数
• QUALIFY 子句
• GROUP BY 子句
• 营销归因分析 — 使用 ROW_NUMBER 确定首/末触点