Redshift 中的窗口函数

Redshift 入门

Jason Myers

Principal Architect

窗口函数

在数据窗口（分区）上操作，为窗口内每行返回一个值
分组函数会聚合结果行，窗口函数不会
通过 OVER 子句定义

三个核心概念

分区：形成行组（PARTITION BY）
排序：分区内顺序（ORDER BY）
框架：可选，对行施加额外限制

用窗口计算平均值

SELECT division_id,
       sale_date,
       revenue,

       -- Calculate the average revenue
       AVG(revenue) OVER (
           -- By division for each year and month
           PARTITION BY division_id,
                        DATE_PART('year', sale_date)
                        DATE_PART('month', sale_date),
       ) AS month_avg_revenue

  FROM orders
 ORDER BY division_id, 
          sale_date DESC;

用窗口计算平均值（续）

division_id | sale_date  | revenue | dept_month_avg_revenue
============|============|=========|=======================
      1     | 2024-01-23 | 350460 | 225500 
      1     | 2024-01-09 | 100540 | 225500 
      1     | 2023-12-15 | 231000 | 231000 
      1     | 2023-11-12 | 124000 | 68000 
      1     | 2023-11-07 | 75000  | 68000 
      1     | 2023-11-01 | 5000   | 68000 
      2     | 2024-01-10 | 500    | 500 
      2     | 2023-12-11 | 1000   | 16166.666666666667 
      2     | 2023-12-08 | 37000  | 16166.666666666667 
      2     | 2023-12-01 | 10500  | 16166.666666666667

用 LAG 计算环比（月环比）

LAG 与 LEAD 按 ORDER BY 获取窗口内相邻上一行（之前）或下一行（之后）的数据

SELECT division_id,
       DATE_PART('year', sale_date) AS sales_year,
       DATE_PART('month', sale_date) AS sales_month,

       -- Count records for the window
       COUNT(*) AS current_month_sales,

       -- Count the previous windows records
       LAG(COUNT(*), 1) OVER (

          -- For each division
          PARTITION BY division_id 
              -- Ordered by year and month
              ORDER BY DATE_PART('year', sale_date),
                       DATE_PART('month', sale_date)
       ) AS prior_month_sales

用 LAG 计算环比（月环比）（续）

  FROM sales_data
 -- Make sure to group by all the window clauses
 GROUP BY division_id, 
          sales_year, 
          sales_month
 ORDER BY division_id, 
          sales_year DESC, 
          sales_month DESC;

用 LAG 计算环比（月环比）结果

division_id	sales_year	sales_month	current_month_sales	prior_month_sales
1	2024	1	2	1
1	2023	12	1	3
1	2023	11	3	null
2	2024	1	1	3
2	2023	12	3	null

在窗口内对数据排名

RANK 按 ORDER BY 对窗口内值进行排名，从 1 开始

SELECT division_id,
       sale_date,
       revenue,
       -- Calculate the rank for each sale in the window
       RANK() OVER (
           -- For each division 
           PARTITION BY division_id 
               -- Using revenue for the rank
               ORDER BY revenue desc
       ) as division_sales_rank
  FROM sales_data
 -- Put them in rank order by division
 ORDER BY division_id, division_sales_rank;

在窗口内对数据排名（结果）

division_id	sale_date	revenue	division_sales_rank
1	2024-01-23	350460	1
1	2023-12-15	231000	2
1	2023-11-12	124000	3
1	2024-01-09	100540	4
1	2023-11-07	75000	5
1	2023-11-01	5000	6
2	2023-12-08	37000	1
2	2023-12-01	10500	2
2	2023-12-11	1000	3
2	2024-01-10	500	4

Vamos praticar!

Redshift 入门