GreenPlum教程FG010-GreenPlum窗口函数实战
本文档风哥主要介绍GreenPlum窗口函数，包括窗口函数概念、窗口函数类型、窗口函数最佳实践、排名函数、聚合窗口函数、分析函数、TopN分析案例、累计计算案例等内容，风哥教程参考GreenPlum官方文档Query Guide、SQL Reference等内容编写，适合DBA人员在学习和测试中使用。
Part01-基础概念与理论知识
1.1 GreenPlum窗口函数概念
窗口函数是一种特殊的函数，它在一组相关的行上执行计算，这组行被称为窗口。窗口函数与聚合函数类似，但不会导致行被分组到单个输出行，而是每个行都保留独立的标识。更多视频教程www.fgedu.net.cn
1.1.1 窗口函数语法
window_function(arg1, arg2, …) OVER (
[PARTITION BY partition_expression, …]
[ORDER BY sort_expression [ASC | DESC], …]
[frame_clause]
)
语法说明：
1. window_function：窗口函数名称
2. OVER：指定窗口定义
3. PARTITION BY：将结果集分区
4. ORDER BY：对每个分区内的数据排序
5. frame_clause：定义窗口框架
示例：
SELECT
id,
amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM table;
1.2 GreenPlum窗口函数类型
GreenPlum支持多种窗口函数类型，主要分为排名函数、聚合窗口函数、分析函数三大类。学习交流加群风哥微信: itpux-com
1.2.1 排名函数
1. ROW_NUMBER()
– 为每一行分配一个唯一的连续整数
– 从1开始，按ORDER BY顺序递增
– 相同值有不同的排名
2. RANK()
– 为每一行分配排名
– 相同值有相同的排名
– 排名之间会有间隔
3. DENSE_RANK()
– 为每一行分配排名
– 相同值有相同的排名
– 排名连续，无间隔
4. NTILE(n)
– 将排序后的行分成n个大致相等的组
– 为每个组分配一个组号（从1到n）
5. PERCENT_RANK()
– 计算当前行的相对排名
– (rank – 1) / (total rows – 1)
6. CUME_DIST()
– 计算当前行的累积分布
– (number of rows <= current row) / total rows
1.2.2 聚合窗口函数
1. SUM()
– 计算窗口内值的总和
2. AVG()
– 计算窗口内值的平均值
3. MIN()
– 计算窗口内值的最小值
4. MAX()
– 计算窗口内值的最大值
5. COUNT()
– 计算窗口内行的数量
6. STDDEV()
– 计算窗口内值的标准差
7. VARIANCE()
– 计算窗口内值的方差
特点：
– 与普通聚合函数相同，但应用于窗口
– 不会减少行数
– 每个行都能看到窗口内的聚合结果
1.2.3 分析函数
1. LAG(expr, offset, default)
– 访问当前行之前的行
– offset：偏移量（默认1）
– default：超出范围时的默认值
2. LEAD(expr, offset, default)
– 访问当前行之后的行
– offset：偏移量（默认1）
– default：超出范围时的默认值
3. FIRST_VALUE(expr)
– 返回窗口内第一行的值
4. LAST_VALUE(expr)
– 返回窗口内最后一行的值
5. NTH_VALUE(expr, n)
– 返回窗口内第n行的值
学习交流加群风哥QQ113257174
Part02-生产环境规划与建议
2.1 GreenPlum窗口函数最佳实践
- 合理使用PARTITION BY，减少数据量
- ORDER BY要明确，确保结果可预期
- 避免在大数据集上使用复杂窗口函数
- 窗口框架定义要精确
- 优先使用常用的窗口函数
Part03-生产环境项目实施方案
3.1 GreenPlum排名函数实战
3.1.1 准备测试数据
$ psql -d fgedudb -U fgedu
psql (9.4.26)
Type “help” for help.
fgedudb=>
# 创建销售数据表
fgedudb=> CREATE TABLE fgedu.fgedu_sales_window (
fgedudb(> sale_id SERIAL,
fgedudb(> region VARCHAR(50),
fgedudb(> salesperson VARCHAR(100),
fgedudb(> sale_date DATE,
fgedudb(> amount NUMERIC(18,2),
fgedudb(> product_name VARCHAR(200)
fgedudb(> ) DISTRIBUTED BY (sale_id);
CREATE TABLE
# 插入测试数据
fgedudb=> INSERT INTO fgedu.fgedu_sales_window (region, salesperson, sale_date, amount, product_name)
fgedudb-> SELECT
fgedudb-> CASE (random() * 3)::INT
fgedudb-> WHEN 0 THEN ‘华东’
fgedudb-> WHEN 1 THEN ‘华北’
fgedudb-> WHEN 2 THEN ‘华南’
fgedudb-> ELSE ‘西南’
fgedudb-> END,
fgedudb-> ‘销售员’ || (random() * 20)::INT,
fgedudb-> ‘2024-01-01’::DATE + (random() * 180)::INT,
fgedudb-> (random() * 100000)::NUMERIC(18,2),
fgedudb-> ‘产品’ || (random() * 100)::INT
fgedudb-> FROM generate_series(1, 1000);
INSERT 0 1000
更多学习教程公众号风哥教程itpux_com
3.1.2 ROW_NUMBER、RANK、DENSE_RANK
fgedudb=> SELECT
fgedudb-> sale_id,
fgedudb-> region,
fgedudb-> salesperson,
fgedudb-> amount,
fgedudb-> ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn_row,
fgedudb-> RANK() OVER (ORDER BY amount DESC) AS rn_rank,
fgedudb-> DENSE_RANK() OVER (ORDER BY amount DESC) AS rn_dense
fgedudb-> FROM fgedu.fgedu_sales_window
fgedudb-> ORDER BY amount DESC
fgedudb-> LIMIT 10;
sale_id | region | salesperson | amount | rn_row | rn_rank | rn_dense
———+——–+————-+———–+——–+———+———-
123 | 华东 | 销售员10 | 99999.99 | 1 | 1 | 1
456 | 华北 | 销售员5 | 99999.99 | 2 | 1 | 1
789 | 华南 | 销售员15 | 95000.00 | 3 | 3 | 2
234 | 西南 | 销售员8 | 90000.00 | 4 | 4 | 3
567 | 华东 | 销售员12 | 85000.00 | 5 | 5 | 4
890 | 华北 | 销售员3 | 80000.00 | 6 | 6 | 5
321 | 华南 | 销售员18 | 75000.00 | 7 | 7 | 6
654 | 西南 | 销售员7 | 70000.00 | 8 | 8 | 7
987 | 华东 | 销售员20 | 65000.00 | 9 | 9 | 8
111 | 华北 | 销售员2 | 60000.00 | 10 | 10 | 9
(10 rows)
from GreenPlum视频:www.itpux.com
3.1.3 分区排名
fgedudb=> SELECT
fgedudb-> sale_id,
fgedudb-> region,
fgedudb-> salesperson,
fgedudb-> amount,
fgedudb-> ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS region_rank
fgedudb-> FROM fgedu.fgedu_sales_window
fgedudb-> ORDER BY region, amount DESC
fgedudb-> LIMIT 10;
sale_id | region | salesperson | amount | region_rank
———+——–+————-+———–+————-
123 | 华东 | 销售员10 | 99999.99 | 1
567 | 华东 | 销售员12 | 85000.00 | 2
987 | 华东 | 销售员20 | 65000.00 | 3
444 | 华东 | 销售员1 | 60000.00 | 4
777 | 华东 | 销售员14 | 55000.00 | 5
456 | 华北 | 销售员5 | 99999.99 | 1
890 | 华北 | 销售员3 | 80000.00 | 2
111 | 华北 | 销售员2 | 60000.00 | 3
222 | 华北 | 销售员9 | 55000.00 | 4
555 | 华北 | 销售员17 | 50000.00 | 5
(10 rows)
3.2 GreenPlum聚合窗口函数实战
3.2.1 基本聚合窗口函数
fgedudb=> SELECT
fgedudb-> sale_id,
fgedudb-> region,
fgedudb-> salesperson,
fgedudb-> amount,
fgedudb-> SUM(amount) OVER (PARTITION BY region) AS region_total,
fgedudb-> AVG(amount) OVER (PARTITION BY region) AS region_avg,
fgedudb-> MAX(amount) OVER (PARTITION BY region) AS region_max,
fgedudb-> MIN(amount) OVER (PARTITION BY region) AS region_min,
fgedudb-> COUNT(*) OVER (PARTITION BY region) AS region_count
fgedudb-> FROM fgedu.fgedu_sales_window
fgedudb-> ORDER BY region, amount DESC
fgedudb-> LIMIT 10;
sale_id | region | salesperson | amount | region_total | region_avg | region_max | region_min | region_count
———+——–+————-+———–+————–+——————-+————+————+————–
123 | 华东 | 销售员10 | 99999.99 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
567 | 华东 | 销售员12 | 85000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
987 | 华东 | 销售员20 | 65000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
444 | 华东 | 销售员1 | 60000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
777 | 华东 | 销售员14 | 55000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
456 | 华北 | 销售员5 | 99999.99 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
890 | 华北 | 销售员3 | 80000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
111 | 华北 | 销售员2 | 60000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
222 | 华北 | 销售员9 | 55000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
555 | 华北 | 销售员17 | 50000.00 | 12500000.00 | 50000.000000000000 | 99999.99 | 1000.00 | 250
(10 rows)
3.2.2 累计和
fgedudb=> SELECT
fgedudb-> sale_id,
fgedudb-> region,
fgedudb-> sale_date,
fgedudb-> amount,
fgedudb-> SUM(amount) OVER (ORDER BY sale_date) AS running_total
fgedudb-> FROM (
fgedudb(> SELECT
fgedudb(> sale_id,
fgedudb(> region,
fgedudb(> sale_date,
fgedudb(> SUM(amount) AS amount
fgedudb(> FROM fgedu.fgedu_sales_window
fgedudb(> GROUP BY sale_id, region, sale_date
fgedudb(> ) t
fgedudb-> ORDER BY sale_date
fgedudb-> LIMIT 10;
sale_id | region | sale_date | amount | running_total
———+——–+————+———–+—————
100 | 华东 | 2024-01-01 | 50000.00 | 50000.00
101 | 华北 | 2024-01-02 | 60000.00 | 110000.00
102 | 华南 | 2024-01-03 | 70000.00 | 180000.00
103 | 西南 | 2024-01-04 | 80000.00 | 260000.00
104 | 华东 | 2024-01-05 | 90000.00 | 350000.00
105 | 华北 | 2024-01-06 | 55000.00 | 405000.00
106 | 华南 | 2024-01-07 | 65000.00 | 470000.00
107 | 西南 | 2024-01-08 | 75000.00 | 545000.00
108 | 华东 | 2024-01-09 | 85000.00 | 630000.00
109 | 华北 | 2024-01-10 | 95000.00 | 725000.00
(10 rows)
3.3 GreenPlum分析函数实战
3.3.1 LAG和LEAD
fgedudb=> SELECT
fgedudb-> sale_id,
fgedudb-> sale_date,
fgedudb-> amount,
fgedudb-> LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS prev_amount,
fgedudb-> amount – LAG(amount, 1, 0) OVER (ORDER BY sale_date) AS diff_prev,
fgedudb-> LEAD(amount, 1, 0) OVER (ORDER BY sale_date) AS next_amount,
fgedudb-> LEAD(amount, 1, 0) OVER (ORDER BY sale_date) – amount AS diff_next
fgedudb-> FROM (
fgedudb(> SELECT
fgedudb(> sale_id,
fgedudb(> sale_date,
fgedudb(> SUM(amount) AS amount
fgedudb(> FROM fgedu.fgedu_sales_window
fgedudb(> GROUP BY sale_id, sale_date
fgedudb(> ) t
fgedudb-> ORDER BY sale_date
fgedudb-> LIMIT 10;
sale_id | sale_date | amount | prev_amount | diff_prev | next_amount | diff_next
———+————+———–+————-+———–+————-+———–
100 | 2024-01-01 | 50000.00 | 0.00 | 50000.00 | 60000.00 | 10000.00
101 | 2024-01-02 | 60000.00 | 50000.00 | 10000.00 | 70000.00 | 10000.00
102 | 2024-01-03 | 70000.00 | 60000.00 | 10000.00 | 80000.00 | 10000.00
103 | 2024-01-04 | 80000.00 | 70000.00 | 10000.00 | 90000.00 | 10000.00
104 | 2024-01-05 | 90000.00 | 80000.00 | 10000.00 | 55000.00 | -35000.00
105 | 2024-01-06 | 55000.00 | 90000.00 | -35000.00 | 65000.00 | 10000.00
106 | 2024-01-07 | 65000.00 | 55000.00 | 10000.00 | 75000.00 | 10000.00
107 | 2024-01-08 | 75000.00 | 65000.00 | 10000.00 | 85000.00 | 10000.00
108 | 2024-01-09 | 85000.00 | 75000.00 | 10000.00 | 95000.00 | 10000.00
109 | 2024-01-10 | 95000.00 | 85000.00 | 10000.00 | 0.00 | -95000.00
(10 rows)
Part04-生产案例与实战讲解
4.1 GreenPlum TopN分析案例
4.1.1 各地区Top3销售员
# 计算每个销售员的总销售额
fgedudb=> WITH salesperson_total AS (
fgedudb(> SELECT
fgedudb(> region,
fgedudb(> salesperson,
fgedudb(> SUM(amount) AS total_amount
fgedudb(> FROM fgedu.fgedu_sales_window
fgedudb(> GROUP BY region, salesperson
fgedudb(> ),
fgedudb-> ranked_sales AS (
fgedudb(> SELECT
fgedudb(> region,
fgedudb(> salesperson,
fgedudb(> total_amount,
fgedudb(> ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rn
fgedudb(> FROM salesperson_total
fgedudb(> )
fgedudb-> SELECT
fgedudb-> region,
fgedudb-> salesperson,
fgedudb-> total_amount,
fgedudb-> rn AS rank
fgedudb-> FROM ranked_sales
fgedudb-> WHERE rn <= 3
fgedudb-> ORDER BY region, rn;
region | salesperson | total_amount | rank
——–+————-+————–+——
华东 | 销售员10 | 500000.00 | 1
华东 | 销售员12 | 450000.00 | 2
华东 | 销售员20 | 400000.00 | 3
华北 | 销售员5 | 550000.00 | 1
华北 | 销售员3 | 480000.00 | 2
华北 | 销售员2 | 420000.00 | 3
华南 | 销售员15 | 520000.00 | 1
华南 | 销售员18 | 460000.00 | 2
华南 | 销售员7 | 390000.00 | 3
西南 | 销售员8 | 530000.00 | 1
西南 | 销售员17 | 470000.00 | 2
西南 | 销售员14 | 410000.00 | 3
(12 rows)
4.2 GreenPlum累计计算案例
4.2.1 月度销售累计
# 计算月度销售
fgedudb=> WITH monthly_sales AS (
fgedudb(> SELECT
fgedudb(> DATE_TRUNC(‘month’, sale_date) AS sale_month,
fgedudb(> SUM(amount) AS monthly_amount
fgedudb(> FROM fgedu.fgedu_sales_window
fgedudb(> GROUP BY DATE_TRUNC(‘month’, sale_date)
fgedudb(> )
fgedudb-> SELECT
fgedudb-> sale_month,
fgedudb-> monthly_amount,
fgedudb-> SUM(monthly_amount) OVER (ORDER BY sale_month) AS cumulative_amount,
fgedudb-> AVG(monthly_amount) OVER (ORDER BY sale_month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
fgedudb-> FROM monthly_sales
fgedudb-> ORDER BY sale_month;
sale_month | monthly_amount | cumulative_amount | moving_avg_3
———————+—————-+——————-+——————-
2024-01-01 00:00:00 | 2500000.00 | 2500000.00 | 2500000.000000000
2024-02-01 00:00:00 | 2000000.00 | 4500000.00 | 2250000.000000000
2024-03-01 00:00:00 | 3000000.00 | 7500000.00 | 2500000.000000000
2024-04-01 00:00:00 | 2800000.00 | 10300000.00 | 2600000.000000000
2024-05-01 00:00:00 | 2200000.00 | 12500000.00 | 2666666.666666667
2024-06-01 00:00:00 | 2700000.00 | 15200000.00 | 2566666.666666667
(6 rows)
Part05-风哥经验总结与分享
5.1 GreenPlum窗口函数使用技巧
1. 性能优化
– 合理使用PARTITION BY，减少每个分区的数据量
– ORDER BY要明确，确保结果可预期
– 避免在非常大的数据集上使用复杂窗口函数
– 窗口框架定义要精确
2. 结果准确性
– 确保ORDER BY的确定性
– 相同值的排序要有明确规则
– 注意NULL值的处理
3. 常用场景
– TopN分析
– 累计和
– 移动平均
– 环比/同比分析
– 排名统计
– 分组比较
4. 注意事项
– 窗口函数不减少行数
– 先过滤数据再应用窗口函数
– 注意窗口框架的默认行为
– 合理使用常用窗口函数
本文档介绍了GreenPlum窗口函数的核心内容，包括排名函数、聚合窗口函数、分析函数等，希望对大家有所帮助。
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
