Redshift'te pencereleme

Redshift’e Giriş

Jason Myers

Principal Architect

Pencere fonksiyonları

Her satır için bir değer üreterek bir veri penceresi (bölüm) üzerinde çalışır
Grup fonksiyonları satırları toplar, pencere fonksiyonları toplamaz
OVER maddesi ile tanımlanır

Üç temel kavram

Bölümleme — satır grupları oluşturma (PARTITION BY)
Sıralama — her bölüm içindeki sıra (ORDER BY)
Çerçeveleme — satırlara ek kısıtlar, isteğe bağlı

Ortalama hesaplamak için pencereleme kullanma

SELECT division_id,
       sale_date,
       revenue,

       -- Ortalama geliri hesaplayın
       AVG(revenue) OVER (
           -- Her yıl ve ay için bölüme göre
           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;

Ortalama hesaplamak için pencereleme (devam)

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

Aydan aya pencerelerde lag kullanma

LAG ve LEAD, ORDER BY maddesine göre pencerede bir satırın bir üstündeki (önceki) veya altındaki (sonraki) veriyi almamıza yardımcı olur

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

       -- Penceredeki kayıtları sayın
       COUNT(*) AS current_month_sales,

       -- Önceki pencerenin kayıtlarını sayın
       LAG(COUNT(*), 1) OVER (

          -- Her bölüm için
          PARTITION BY division_id 
              -- Yıl ve aya göre sıralı
              ORDER BY DATE_PART('year', sale_date),
                       DATE_PART('month', sale_date)
       ) AS prior_month_sales

Aydan aya pencerelerde lag kullanma (devam)

  FROM sales_data
 -- Tüm pencere maddelerine göre gruplamayı unutmayın
 GROUP BY division_id, 
          sales_year, 
          sales_month
 ORDER BY division_id, 
          sales_year DESC, 
          sales_month DESC;

Aydan aya pencerelerde lag kullanma (sonuçlar)

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

Pencerelerde verileri sıralama

RANK, ORDER BY maddesine göre pencerede bir değeri 1'den başlayarak sıralamamızı sağlar

SELECT division_id,
       sale_date,
       revenue,
       -- Her satış için penceredeki sırayı hesaplayın
       RANK() OVER (
           -- Her bölüm için
           PARTITION BY division_id 
               -- Sıralama için revenue kullanılır
               ORDER BY revenue desc
       ) as division_sales_rank
  FROM sales_data
 -- Bölüme göre sıra düzeninde listeleyin
 ORDER BY division_id, division_sales_rank;

Pencerelerde verileri sıralama (sonuçlar)

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

Hadi pratik yapalım!

Redshift’e Giriş