Feeds:
Posts
Comments

Posts Tagged ‘Window Functions’

Oleh : Erik Wijaya (1200999330)

1. Pengertian
Seperti kita ketahui, banyak sekali fungsi-fungsi pada Oracle yang dapat membantu kita untuk mempermudah dalam melakukan query data dari database. Menurut Oracle Database:oracle, fungsi-fungsi tersebut adalah:
  • SQL Functions
  • Single-row Functions
  • Numeric Functions
  • Character Functions Returning Character Values
  • NLS Character Functions
  • Character  Functions Returning Number Values
  • Datetime Functions
  • General Comparison Functions
  • Conversion Functions
  • Large object Functions
  • Collection Functions
  • Hierarchical Functions
  • Data Mining Functions
  • XML Functions
  • Encoding and Decoding Functions
  • Null Related Functions
  • Environment and Identifier function
  • Aggregate Functions
  • Analytic Functions
  • Object Reference Functions
  • Model Functions

Tilisan ini akan secara lebih jauh akan membahas mengenai salah satu fungsi pada database Oracle di atas yaitu Analytic Functions.

Menurut Oracle Database:oracle dan  SQL:orablogger Inspired by oracle, Analytic Functions merupakan salah fungsi pada database Oracle yang berfungsi untuk menghitung nilai aggregate berdasarkan sekumpulan row yang dinamakan window. Fungsi ini sekilas hampir sama dengan aggregate function tetapi sebenarnya ada perbedaannya yaitu aggregate function dapat menghitung nilai aggregate dan hanya mengembalikan satu value atau row yang merupakan hasil dari pengolahan nilai beberapa value atau row yang ada sedangkan analytic function dapat menghitung nilai aggregate dan dapat mengembalikan lebih dari satu value atau row.

Sintaks Umum dari Analytic Functions adalah sebagai berikut:

            Function(arg1,…, argn) OVER ( [PARTITION BY <…>]
[ORDER BY <….>] )

Pada sintaks di atas terdiri dari:
1) 
Function(arg1,..,argn)  merupakan nama tipe fungsi dari Analytic Functions yang bisa memiliki argument.
2)
OVER merupakan bagian sintaks dari Analytic Functions yang menghubungkan antara nama tipe fungsi dengan klausa partition by dan klausa order by.
3)  Klausa PARTITION BY  (….) . Menurut (ORACLE SQL) klausa partition by  merupakan klausa yang mempunyai fungsi yang hampir sama dengan GROUP BY(….) pada Aggregate Functions  namun perbedaannya adalah klausa GROUP BY akan mengembalikan satu value atau row sedangkan klausa PARTITION BY akan mengembalikan lebih dari satu value atau row.
4)  Klausa ORDER BY untuk menampilkan hasil dari query dengan Analytic Functions dengan terurut baik secara ascending maupun descending.

2. Tipe Analytic Functions
Menurut Analytical, Analytic Functions dapat dibedakan menjadi 4 tipe yaitu:

a)      Ranking Functions
Fungsi utama dari Ranking Functions adalah untuk memberikan ranking berdasarkan hasil dari pengolahan data yang ditampilkan. Selain untuk menentukan ranking atau peringkat, pada fungsi ini juga terdapat fungsi lain yang juga menghasilkan angka namun bukan merupakan peringkat seperti misalnya nomor row, pembagian kelompok data, dan lain-lain.

Berikut merupakan beberapa contoh dari Ranking Functions:

1)      RANK()

Menurut  Oracle Reference:PSOUG.org, fungsi ini berguna untuk memberikan ranking namun bisa menyebabkan adanya ranking yang tidak berurutan.

  Sintaks:
RANK() OVER() (<opartition_clause><order_by_clause>)

Contoh:
SELECT d.department_name, e.last_name, e.salary ,
RANK() OVER(partition by e.department_id order by e.salary)
“RANK”
FROM employees e, departments d
WHERE e.department_id = d.department_id and
d.department_id in (30,60);

Gambar 1.1 Contoh fungsi RANK()

Pada contoh di Gambar 1.1 yaitu pada kolom ke-4 ditampilkan ranking atau peringkat pada tiap department_id yang diurutkan berdasarkan salary secara descending dengan menggunakan fungsi RANK() sehingga yang mendapatkan salary paling besar mendapatkan ranking 1 dan seterusnya. Fungsi ini akan memberikan ranking yang sama ketika terdapat value yang sama. Pada contoh di atas terdapat ranking yang sama yaitu ranking 3 yang disebabkan karena adanya value yang sama pada salary 4800. Pada row berikutnya, ranking yang diberikan adalah ranking 5 dan tidak ada ranking 4. Inilah yang dimaksudkan dengan fungsi rank bisa menyebabkan ranking yang tidak berurutan atau dengan istilah lain adanya gap antar ranking.

2)      DENSE_RANK()
Menurut  Oracle Reference:PSOUG.org, fungsi ini berguna untuk memberikan ranking dan tidak menyebabkan adanya ranking yang tidak berurutan.

Sintaks:
DENSE_RANK() OVER()
(<partition_clause><order_by_clause>)

Contoh:

SELECT d.department_name, e.last_name, e.salary,
DENSE_RANK() OVER(partition by e.department_id order
by e.salary) “DENSE_RANK”
FROM employees e, departments d
WHERE e.department_id = d.department_id and
d.department_id in (30,60);

Gambar 1.2 Contoh fungsi DENSE_RANK

Pada contoh di Gambar 1.2 yaitu pada kolom ke-4 ditampilkan ranking atauperingkat pada tiap department_id yang diurutkan berdasarkan salary secara descending dengan menggunakan fungsi DENSE_RANK() sehingga yang mendapatkan salary paling besar mendapatkan ranking 1 dan seterusnya. Fungsi ini akan memberikan ranking yang sama ketika terdapat value yang sama. Pada contoh di atas terdapat ranking yang sama yaitu ranking 3 yang disebabkan karena adanya value yang sama pada salary 4800. Pada row berikutnya, ranking yang diberikan adalah ranking 4 berbeda dengan fungsi RANK() yang memberikan ranking 5 sehingga bisa disimpulkan bahwa fungsi DENSE_RANK() tidak akan menyebabkan adanya ranking yang tidak berurutan atau dengan istilah lain tidak adanya gap antar ranking.

3)      ROW_NUMBER()

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menampilkan nomor row pada setiap row yang ditampilkan.

Sintaks:
ROW_NUMBER() OVER
           (<partition_clause> <order_by_clause>)

Contoh:
SELECT department_id, last_name, salary,
row_number() over(partition by department_id
order by salary) “ROW_NUMBER”
FROM employees
WHERE department_id = 80;

Gambar 1.3 Contoh fungsi ROW _NUMBER()

Pada contoh Gambar 1.3 pada kolom ke-4 ditampilkan angka-angka yang merupakan nomor row dimana row pertama mendapat nomor row 1 , row kedua mendapat nomor row 2 dan seterusnya sehingga lebih cocok bila kolom yang mengandung nomor row diletakkan pada kolom yang kiri sebagai kolom [nomor row].

4)      NTILE ( value )

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk membagi hasil data menjadi kelompok-kelompok data dengan jumlah kelompok data yang ditentukan pada parameter fungsi NTILE.

Sintaks:
NTILE(value) OVER (<partition_clause> <order_by_clause>)

Contoh:
SELECT department_id, last_name, salary,
NTILE(4)
OVER(partition by department_id order
by salary) “NTILE”
FROM employees
WHERE department_id = 80;

Gambar 1.4 Contoh fungsi NTILE(value)

Pada contoh Gambar 1.4 pada kolom ke-4 ditampilkan pembagian kelompok data berdasarkan fungsi NTILE. Pada contoh di atas, terdapat 34 row atau row data yang dibagi menjadi 4 kelompok data dengan pembagian kelompok 1 memiliki 9 anggota, kelompok 2 memiliki 9 anggota, kelompok 3 memiliki 8 anggota, dan kelompok 4 memiliki 8 anggota. Hasil pembagian ini didasarkan atas sisa hasil bagi atau modulus. Bila 34 row dibagi menjadi 4 kelompok data dengan jumlah anggota yang sama maka akan dihasilkan 4 kelompok dengan 8 anggota tetapi terdapat sisa hasil bagi 2 row. Angka 2 row ini berarti 2 kelompok atas yaitu kelompok 1 dan 2 akan ditambahkan 1 anggota lagi sehingga menjadi 9 , 9 , 8 , 8.

5)      PERCENT_RANK()

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk mengubah peringkat atau ranking dari fungsi RANK() menjadi angka persentase yang valuenya berada antara 0 dan 1.
Rumusnya adalah:
                        (n – 1) / (m – 1)
n = ranking berdasarkan fungsi RANK()
m = jumlah row

Sintaks:
  PERCENT_RANK() OVER (<partition_clause>
        <order_by_clause>)
Contoh:
SELECT department_id, last_name, salary,
PERCENT_RANK() OVER(partition by department_id
order by salary desc) “PERCENT_RANK”
FROM employees
WHERE department_id = 80;

Gambar 1.5 Contoh fungsi PERCENT_RANK()

Pada contoh Gambar 1.5 pada kolom ke-4 ditampilkan hasil pengubahan ranking menjadi persentase ranking dengan menggunakan fungsi PERCENT_RANK(). Sebagai contoh, pada row ke-2 bisa ditampilkan hasil .03030303 karena :

(n-1) / (m-1)  = (2-1)/(34-1) = 0.03030303

Catatan: ranking pada row ke-2: 2 , jumlah row: 34

b)      Window Functions

Contoh dari fungsi ini antara lain adalah SUM, MAX, AVG, COUNT, MIN yang biasanya sering dipakai pada Aggregate Function. Menurut Oracle Database:oracle dan  SQL:orablogger Inspired by oracle, perbedaannya adalah bila digunakan pada Aggregate Function maka fungsi di atas akan mengembalikan satu value atau row sedangkan bila digunakan pada Analytic Functions akan mengembalikan lebih dari satu value atau row.
Berikut merupakan contoh dari fungsi SUM dan AVG dengan menggunakan Analytic Functions:

1)      SUM (value)

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menghitung kumulatif value berdasarkan kolom yang ditentukan.

Sintaks:
SUM(value) OVER (<partition_clause> <order_by_clause>)

Gambar 1.6 Contoh fungsi SUM(value) dengan Analytic Functions

Contoh:
SELECT department_id, last_name, salary, sum(salary) over(partition by department_id order by department_id)     “SUM_SAL”        FROM employees        WHERE department_id = 80;

Pada contoh Gambar 1.6 di kolom ke-4 ditampilkan jumlah salary pada department_id yang bernilai 80.
Perhatikan contoh berikut dengan menggunakan Group By:

SELECT department_id, last_name, salary, sum(salary) “SUM_SAL”  FROM employees WHERE department_id = 80 GROUP BY department_id, last_name, salary;

Gambar 1.7 Contoh fungsi SUM(value) dengan Aggregate Functions

Pada Aggregate Function bila kita ingin menampilkan hasil yang sesuai dengan yang kita inginkan maka kita juga harus membatasi jumlah kolom yang ditampilkan . Pada gambar 1.7, jumlah salary yang ditampilkan bukan merupakan jumlah salary keseluruhan dari department_id yang bernilai 80 namun merupakan jumlah salary berdasarkan kolom department_id, last_name, dan salary.
Sedangkan pada gambar 1.6 walaupun ditampilkan department_id, last_name, dan salary  namun tetap bisa didapatkan jumlah salary pada department_id yang bernilai 80.

2)      AVG (value)

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna untuk menghitung rata-rata value berdasarkan kolom yang ditentukan.

Sintaks:
AVG(value) OVER (<partition_clause> <order_by_clause>)

Gambar 1.8 Contoh fungsi AVG(value) dengan Analytic Functions

Contoh:
SELECT department_id, last_name, salary, avg(salary)
over(partition by department_id order by department_id)
“AVG_SAL” FROM employees WHERE department_id = 80;

Pada contoh gambar 1.8 di kolom ke-4 ditampilkan rata-rata salary pada department_id yang bernilai 80.
Perhatikan contoh berikut dengan menggunakan Group By:

SELECT department_id, last_name, salary, avg (salary)
“AVG_SAL”  FROM employees WHERE department_id = 80
GROUP BY department_id, last_name, salary;

Normal
0

false
false
false

EN-US
X-NONE
X-NONE

MicrosoftInternetExplorer4

/* Style Definitions */
table.MsoNormalTable
{mso-style-name:”Table Normal”;
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-qformat:yes;
mso-style-parent:””;
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin:0in;
mso-para-margin-bottom:.0001pt;
mso-pagination:widow-orphan;
font-size:10.0pt;
font-family:”Calibri”,”sans-serif”;}

Gambar 1.9 Contoh fungsi AVG(value) dengan Aggregate Functions

Sama seperti contoh fungsi SUM pada contoh sebelumnya, rata-rata salary yang ditampilkan pada gambar 1.9 bukan merupakan rata-rata salary dari department_id yang bernilai 80 tetapi merupakan rata-rata salary

berdasarkan kolom department_id, last_name, dan salary.
Sedangkan pada gambar 1.8 walaupun ditampilkan department_id, last_name, dan salary namun tetap bisa didapatkan rata-rata salary pada department_id yang bernilai 80.

c)      Reporting Functions
Fungsi ini bisa disebut sebagai fungsi yang melaporkan hasil pengolahan data yang telah ditampilkan.
Salah satu contoh dari fungsi ini adalah RATIO_TO_REPORT(value) yang menurut  Oracle Reference:PSOUG.org, berguna untuk membandingkan value sebuah row dengan jumlah value dari seluruh row.

Sintaks:
     RATIO_TO_REPORT(value) OVER (<partition_clause>
           <order_by_clause>)

Gambar 2.0 Contoh fungsi RATIO_TO_REPORT (value)

Contoh:
SELECT department_id, last_name, salary,         ratio_to_report(salary) over(partition by department_id)
“RATIO_TO_REPORT”
FROM employees
WHERE department_id = 80;

Pada contoh gambar 2.0 di kolom ke-4 ditampilkan kolom RATIO_TO_REPORT yang merupakan hasil perbandingan antara salary dari setiap row dengan jumlah salary untuk semua row yaitu 304500.
Sebagai contoh pada row ke-1, hasilnya adalah .045977011 yang didapatkan dari salary row ke-1 (14000) dibagi dengan jumlah salary untuk semua row (304500).

c)      Lag/Lead Functions
Fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan. Contoh penggunaan fungsi ini dalam kehidupan nyata adalah misalnya perbandingan antara penjualan bulan ini dengan bulan sebelumnya atau sesudahnya (April dengan Maret atau April dengan Mei).

1)      LAG ( value_expression,offset,default )
Menurut Oracle Reference:PSOUG.org, fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan yaitu sejauh n baris di atas baris yang dispesifikasikan.

Sintaks:
LAG
(<value expression>, <offset>, <default>)  OVER
([<query partition clause>] <order_by_clause>)

LAG functions memiliki 3 parameter yaitu:

  • Value expression   : merupakan nama kolom yang ingin diakses
  • Offset                    : jumlah lompatan untuk menentukan baris mana yang akan ditampilkan (ke atas)
  • Default                  : default value bila baris yang diakses tidak memiliki value

Gambar 2.1 Contoh fungsi LAG

Contoh:
SELECT department_id, last_name, salary, lag(salary,2,3)      OVER(partition by department_id order by salary desc) “LAG” FROM employees WHERE department_id = 80;

Pada contoh gambar 2.1 di kolom ke-4 ditampilkan hasil pengaksesan kolom salary dengan perbedaan 2 lompatan urutan baris ke atas baris yang dispesifikasikan dengan menggunakan fungsi LAG.
Sebagai contoh pada baris ke-1 hasilnya adalah 3 karena bila baris ke-1 mengakses 2 baris ke atas baris ke-1 maka menghasilkan tidak adanya baris yang dapat diakses sehingga yang ditampilkan adalah default value yaitu 3.
Sedangkan pada baris ke-3 hasilnya adalah 14000 karena bila baris ke-3 mengakses 2 baris ke atas baris ke-3 maka akan berhenti pada baris ke-1 yang mempunyai value 14000 sehingga yang ditampilkan pada kolom LAG di baris ke-3 adalah 14000.

2)      LEAD ( value_expression, offset, default )

Menurut Oracle Reference:PSOUG.org, fungsi ini berguna agar kita dapat mengakses kolom yang sama dengan membedakan urutan row atau baris yang ditampilkan yaitu sejauh n baris di bawah baris yang dispesifikasikan.

Sintaks:
LEAD
(<value expression>, <offset>, <default>)  OVER
([<query partition clause>] <order_by_clause>)

LEAD functions memiliki 3 parameter yaitu:

  • Value expression   : merupakan nama kolom yang ingin diakses
  • Offset                    : jumlah lompatan untuk menentukan baris mana yang akan ditampilkan (ke bawah)
  • Default                  : default value bila baris yang diakses tidak memiliki value

Gambar 2.2 Contoh fungsi LEAD

Contoh:
SELECT department_id, last_name, salary, lead(salary,2,3) over(partition by department_id order by salary desc) “LAG” FROM employees  WHERE department_id = 80;

Pada contoh gambar 2.2 di kolom ke-4 ditampilkan hasil pengaksesan kolom salary dengan perbedaan 2 lompatan urutan baris ke bawah baris yang dispesifikasikan dengan menggunakan fungsi LEAD.
Sebagai contoh, pada baris ke-1 hasilnya adalah 12000 karena bila baris ke-1 mengakses 2 baris ke bawah baris ke-1 maka akan berhenti pada baris ke-3 yang mempunyai value 12000 sehingga yang ditampilkan pada kolom LEAD di baris ke-1 adalah 12000.

DAFTAR PUSTAKA

Read Full Post »