Redshift のウィンドウ処理

Redshift入門

Jason Myers

Principal Architect

ウィンドウ関数

データのウィンドウ（パーティション）上で、各行に値を返します
集約関数は結果行を集計しますが、ウィンドウ関数はしません
OVER 句で定義します

3つの主要概念

パーティション: 行をグループ化（PARTITION BY）
並び順: パーティション内の順序（ORDER BY）
フレーム: 追加の行制約（任意）

ウィンドウで平均を計算する

SELECT division_id,
       sale_date,
       revenue,

       -- 平均収益を計算
       AVG(revenue) OVER (
           -- 部門ごと、年・月単位
           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(*) AS current_month_sales,

       -- 直前ウィンドウのレコード数
       LAG(COUNT(*), 1) OVER (

          -- 部門ごと
          PARTITION BY division_id 
              -- 年・月で並べ替え
              ORDER BY DATE_PART('year', sale_date),
                       DATE_PART('month', sale_date)
       ) AS prior_month_sales

LAG による月次比較ウィンドウ（続き）

  FROM sales_data
 -- ウィンドウ句の項目で GROUP BY
 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,
       -- ウィンドウ内で各売上の順位を算出
       RANK() OVER (
           -- 部門ごと
           PARTITION BY division_id 
               -- 収益で順位付け
               ORDER BY revenue desc
       ) as division_sales_rank
  FROM sales_data
 -- 部門ごとに順位で並べ替え
 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

Passons à la pratique !

Redshift入門