Window Functions: Fungsi Window di SQL 📊

Di materi kelima ini, kita akan belajar cara menggunakan window functions di berbagai DBMS. Contoh yang diberikan mencakup PostgreSQL, MySQL, dan SQL Server.

Prerequisites 📋

Sebelum memulai materi ini, pastikan Anda sudah memahami:

Dasar Window Functions (Basic Window Functions)

1. ROW_NUMBER

-- PostgreSQL
SELECT 
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

-- MySQL (versi 8.0+)
SELECT 
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

-- SQL Server
SELECT 
    category,
    product_name,
    price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) as rank_in_category
FROM products;

-- Data urutan produk:
category    | product_name | price  | rank_in_category
----------- | ----------- | ------ | ----------------
Electronics | Laptop Pro  | 15000  | 1
Electronics | Smart TV    | 12000  | 2
Electronics | Tablet      | 8000   | 3
Gadgets     | Smartwatch  | 5000   | 1
Gadgets     | Headphones  | 2000   | 2

2. RANK dan DENSE_RANK

-- PostgreSQL
SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) as rank,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) as dense_rank
FROM product_sales;

-- MySQL (versi 8.0+)
SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) as `rank`,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) as dense_rank
FROM product_sales;

-- SQL Server
SELECT 
    product_name,
    sales_amount,
    RANK() OVER (ORDER BY sales_amount DESC) as rank,
    DENSE_RANK() OVER (ORDER BY sales_amount DESC) as dense_rank
FROM product_sales;

-- Data peringkat penjualan:
product_name | sales_amount | rank | dense_rank
------------ | ------------ | ---- | ----------
Laptop Pro   | 150000      | 1    | 1
Smart TV     | 150000      | 1    | 1
Tablet       | 100000      | 3    | 2
Smartwatch   | 80000       | 4    | 3

3. Running Totals (Total Berjalan)

-- PostgreSQL
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- MySQL (versi 8.0+)
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- SQL Server
SELECT 
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as running_total
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Hasil:
order_date  | total_amount | running_total
----------- | ------------ | -------------
2024-01-01  | 1000        | 1000
2024-01-02  | 1500        | 2500
2024-01-03  | 2000        | 4500
2024-01-04  | 1800        | 6300

4. Moving Averages (Rata-rata Bergerak)

-- PostgreSQL
SELECT 
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3days
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- MySQL (versi 8.0+)
SELECT 
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3days
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- SQL Server
SELECT 
    order_date,
    total_amount,
    AVG(total_amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) as moving_avg_3days
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Hasil:
order_date  | total_amount | moving_avg_3days
----------- | ------------ | ---------------
2024-01-01  | 1000        | 1000.00
2024-01-02  | 1500        | 1250.00
2024-01-03  | 2000        | 1500.00
2024-01-04  | 1800        | 1766.67
2024-01-05  | 2200        | 2000.00

5. LAG dan LEAD Functions

-- PostgreSQL
SELECT 
    order_date,
    total_amount,
    LAG(total_amount) OVER (ORDER BY order_date) as prev_day_amount,
    LEAD(total_amount) OVER (ORDER BY order_date) as next_day_amount
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- MySQL (versi 8.0+)
SELECT 
    order_date,
    total_amount,
    LAG(total_amount) OVER (ORDER BY order_date) as prev_day_amount,
    LEAD(total_amount) OVER (ORDER BY order_date) as next_day_amount
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- SQL Server
SELECT 
    order_date,
    total_amount,
    LAG(total_amount) OVER (ORDER BY order_date) as prev_day_amount,
    LEAD(total_amount) OVER (ORDER BY order_date) as next_day_amount
FROM orders
WHERE status = 'completed'
ORDER BY order_date;

-- Hasil:
order_date  | total_amount | prev_day_amount | next_day_amount
----------- | ------------ | --------------- | --------------
2024-01-01  | 1000        | NULL            | 1500
2024-01-02  | 1500        | 1000            | 2000
2024-01-03  | 2000        | 1500            | 1800
2024-01-04  | 1800        | 2000            | 2200
2024-01-05  | 2200        | 1800            | NULL

6. FIRST_VALUE dan LAST_VALUE

-- PostgreSQL
SELECT 
    category,
    product_name,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) as most_expensive_in_category,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as least_expensive_in_category
FROM products;

-- MySQL (versi 8.0+)
SELECT 
    category,
    product_name,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) as most_expensive_in_category,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as least_expensive_in_category
FROM products;

-- SQL Server
SELECT 
    category,
    product_name,
    price,
    FIRST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
    ) as most_expensive_in_category,
    LAST_VALUE(product_name) OVER (
        PARTITION BY category 
        ORDER BY price DESC
        RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) as least_expensive_in_category
FROM products;

-- Hasil:
category    | product_name | price  | most_expensive_in_category | least_expensive_in_category
----------- | ----------- | ------ | -------------------------- | --------------------------
Electronics | Laptop Pro  | 15000  | Laptop Pro                 | Tablet
Electronics | Smart TV    | 12000  | Laptop Pro                 | Tablet
Electronics | Tablet      | 8000   | Laptop Pro                 | Tablet
Gadgets     | Smartwatch  | 5000   | Smartwatch                 | Headphones
Gadgets     | Headphones  | 2000   | Smartwatch                 | Headphones

7. NTILE Function

-- PostgreSQL
SELECT 
    product_name,
    price,
    NTILE(4) OVER (ORDER BY price DESC) as price_quartile
FROM products;

-- MySQL (versi 8.0+)
SELECT 
    product_name,
    price,
    NTILE(4) OVER (ORDER BY price DESC) as price_quartile
FROM products;

-- SQL Server
SELECT 
    product_name,
    price,
    NTILE(4) OVER (ORDER BY price DESC) as price_quartile
FROM products;

-- Hasil:
product_name | price  | price_quartile
------------ | ------ | --------------
Laptop Pro   | 15000  | 1
Smart TV     | 12000  | 1
Tablet       | 8000   | 2
Smartwatch   | 5000   | 3
Headphones   | 2000   | 4

Tips Penting

  1. Perhatikan perbedaan sintaks dan fitur window functions antar DBMS
  2. Gunakan partisi untuk meningkatkan performa pada dataset besar
  3. Pertimbangkan penggunaan frame clause yang tepat
  4. Optimalkan query dengan index yang sesuai

Latihan (Exercises)

  1. Buat analisis penjualan dengan running totals
  2. Implementasikan moving averages untuk trend analysis
  3. Buat ranking system dengan multiple criteria

Referensi (References)

Materi Sebelumnya (Previous Materials)

Materi Selanjutnya (Next Materials)

Fatkul Nur Koirudin