Gunakan fungsi jendela AnalyticDB for MySQL - AnalyticDB

Anda dapat menggunakan fungsi jendela untuk melakukan perhitungan kompleks seperti peringkat grup, rata-rata bergerak, dan jumlah kumulatif. Topik ini menjelaskan sintaksis fungsi jendela serta memberikan contoh penggunaannya dalam AnalyticDB for MySQL.

Fungsi Agregat
Fungsi Pengurutan
- CUME_DIST: Mengembalikan distribusi kumulatif setiap nilai dalam satu set nilai.
- RANK: Mengembalikan peringkat setiap nilai dalam dataset.
- DENSE_RANK: Mengembalikan peringkat setiap nilai dalam satu set nilai.
- NTILE: Mendistribusikan data dalam setiap partisi jendela ke dalam n ember. Ember dinomori dari 1 hingga n.
- ROW_NUMBER: Mengembalikan nomor unik dan berurutan untuk setiap baris berdasarkan urutan baris dalam partisi jendela, dimulai dari 1.
- PERCENT_RANK: Mengembalikan persentase peringkat setiap nilai dalam dataset dalam format (r - 1)/(n - 1). r adalah peringkat baris saat ini yang dihitung menggunakan fungsi RANK(), dan n adalah jumlah total baris dalam partisi jendela saat ini.
Fungsi Nilai
- FIRST_VALUE: Mengembalikan nilai baris pertama dalam partisi jendela.
- LAST_VALUE: Mengembalikan nilai baris terakhir dalam partisi jendela.
- LAG: Mengembalikan nilai baris yang mendahului baris saat ini sebanyak offset baris dalam jendela.
- LEAD: Mengembalikan nilai baris yang mengikuti baris saat ini sebanyak offset baris dalam jendela.
- NTH_VALUE: Mengembalikan nilai baris yang digeser oleh jumlah offset baris tertentu dalam jendela. Offset dimulai dari 1.

Ikhtisar

Fungsi jendela menghitung nilai agregat berdasarkan data baris dari hasil kueri. Fungsi jendela dieksekusi setelah klausa HAVING dan sebelum klausa ORDER BY. Fungsi jendela dipicu setelah Anda menggunakan klausa OVER untuk menentukan jendela.

AnalyticDB for MySQL mendukung tiga jenis fungsi jendela: fungsi agregat, fungsi pengurutan, dan fungsi nilai.

Sintaksis

function over ([partition by a] order by b RANGE|ROWS BETWEEN start AND end)

Fungsi jendela mencakup bagian-bagian berikut:

Aturan Partisi: Membagi baris input menjadi partisi yang berbeda. Prosesnya mirip dengan proses pengelompokan pada klausa GROUP BY. Bagian aturan partisi bersifat opsional.
Aturan Pengurutan: Menentukan urutan eksekusi baris input dalam fungsi jendela.
Kerangka Jendela: Menentukan batas data tempat fungsi jendela melakukan perhitungan.
Kerangka jendela mendukung mode RANGE dan ROWS:
- RANGE mendefinisikan rentang nilai kolom.
- ROWS mendefinisikan jumlah baris relatif terhadap baris saat ini.
- Untuk RANGE dan ROWS, Anda dapat menggunakan BETWEEN start AND end untuk menentukan nilai batas. Nilai valid untuk argumen dalam BETWEEN start AND end:
  - CURRENT ROW: Baris saat ini.
  - N PRECEDING: n baris sebelumnya.
  - UNBOUNDED PRECEDING: Baris dari baris pertama hingga baris saat ini.
  - N FOLLOWING: n baris berikutnya.
  - UNBOUNDED FOLLOWING: Baris dari baris saat ini hingga baris terakhir.

Sebagai contoh, kueri berikut menghitung jumlah parsial dari profit berdasarkan setiap baris data dalam jendela saat ini:

select year,country,profit,sum(profit) over (partition by country order by year ROWS BETWEEN UNBOUNDED PRECEDING and CURRENT ROW) as slidewindow from testwindow;
+------+---------+--------+-------------+
| year | country | profit | slidewindow |
+------+---------+--------+-------------+
| 2001 | USA     |     50 |          50 |
| 2001 | USA     |   1500 |        1550 |
| 2000 | Germany |     75 |          75 |
| 2000 | Germany |     75 |         150 |
| 2001 | Germany |     79 |         229 |
| 2000 | Finland |   1500 |        1500 |
| 2001 | Finland |     10 |        1510 |

Kueri berikut hanya dapat menghitung jumlah total dari profit:

select country,sum(profit) over (partition by country) from testwindow;
+---------+-----------------------------------------+
| country | sum(profit) OVER (PARTITION BY country) |
+---------+-----------------------------------------+
| Germany |                                     229 |
| Germany |                                     229 |
| Germany |                                     229 |
| USA     |                                    1550 |
| USA     |                                    1550 |
| Finland |                                    1510 |
| Finland |                                    1510 |

Catatan penggunaan

Pastikan nilai batas memenuhi persyaratan berikut:

start tidak boleh UNBOUNDED FOLLOWING. Jika tidak, kesalahan Window frame start cannot be UNBOUNDED FOLLOWING akan dikembalikan.
end tidak boleh UNBOUNDED PRECEDING. Jika tidak, kesalahan Window frame end cannot be UNBOUNDED PRECEDING akan dikembalikan.
Ketika start adalah CURRENT ROW dan end adalah N PRECEDING, kesalahan Window frame starting from CURRENT ROW cannot end with PRECEDING akan dikembalikan.
Ketika start adalah N FOLLOWING dan end adalah N PRECEDING, kesalahan Window frame starting from FOLLOWING cannot end with PRECEDING akan dikembalikan.
Ketika start adalah N FOLLOWING dan end adalah CURRENT ROW, kesalahan Window frame starting from FOLLOWING cannot end with CURRENT ROW akan dikembalikan.

Ketika kerangka jendela dalam mode RANGE, aturan berikut berlaku:

Ketika start atau end adalah N PRECEDING, kesalahan Window frame RANGE PRECEDING is only supported with UNBOUNDED akan dikembalikan.
Ketika start atau end adalah N FOLLOWING, kesalahan Window frame RANGE FOLLOWING is only supported with UNBOUNDED akan dikembalikan.

Persiapan

Dalam topik ini, data dari tabel testwindow digunakan dalam contoh fungsi jendela.

create table testwindow(year int, country varchar(20), product varchar(20), profit int) distributed by hash(year);

insert into testwindow values (2000,'Finland','Computer',1500);
insert into testwindow values (2001,'Finland','Phone',10);
insert into testwindow values (2000,'Germany','Calculator',75);
insert into testwindow values (2000,'Germany','Calculator',75);
insert into testwindow values (2001,'Germany','Calculator',79);
insert into testwindow values (2001,'USA','Calculator',50);
insert into testwindow values (2001,'USA','Computer',1500);

SELECT * FROM testwindow;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2000 | Germany | Calculator |     75 |
| 2000 | Germany | Calculator |     75 |
| 2001 | Germany | Calculator |     79 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |

Fungsi agregat

Semua fungsi agregat dapat digunakan sebagai fungsi jendela dengan menambahkan klausa OVER. Fungsi agregat menghitung setiap baris data berdasarkan baris-baris dalam jendela geser saat ini. Untuk informasi lebih lanjut, lihat Fungsi Agregat.

Sebagai contoh, kueri berikut menghasilkan jumlah berjalan dari harga pesanan berdasarkan tanggal untuk setiap petugas kasir:

SELECT clerk, orderdate, orderkey, totalprice,sum(totalprice) OVER (PARTITION BY clerk ORDER BY orderdate) AS rolling_sum FROM orders ORDER BY clerk, orderdate, orderkey

CUME_DIST

CUME_DIST()

Deskripsi: Fungsi ini mengembalikan distribusi kumulatif setiap nilai dalam satu set nilai.
Hasil pengembalian: Dataset yang diperoleh setelah pengurutan dalam partisi jendela, termasuk baris saat ini dan jumlah baris data yang mendahului baris saat ini. Nilai terkait dalam pengurutan dihitung ke nilai distribusi yang sama.
Tipe Data Nilai Pengembalian: DOUBLE.

Contoh:

select year,country,product,profit,cume_dist() over (partition by country order by profit) as cume_dist from testwindow;
+------+---------+------------+--------+--------------------+
| year | country | product    | profit | cume_dist          |
+------+---------+------------+--------+--------------------+
| 2001 | USA     | Calculator |     50 |                0.5 |
| 2001 | USA     | Computer   |   1500 |                1.0 |
| 2001 | Finland | Phone      |     10 |                0.5 |
| 2000 | Finland | Computer   |   1500 |                1.0 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2000 | Germany | Calculator |     75 | 0.6666666666666666 |
| 2001 | Germany | Calculator |     79 |                1.0 |

RANK

RANK()

Deskripsi: Fungsi ini mengembalikan peringkat setiap nilai dalam dataset.
Nilai peringkat adalah jumlah baris yang mendahului baris saat ini ditambah satu. Baris saat ini tidak dihitung. Oleh karena itu, nilai terkait dalam pengurutan mungkin menghasilkan celah dalam urutan. Peringkat dihitung untuk setiap partisi jendela.
Tipe Data Nilai Pengembalian: BIGINT.

Contoh:

select year,country,product,profit,rank() over (partition by country order by profit) as rank from testwindow;
+------+---------+------------+--------+------+
| year | country | product    | profit | rank |
+------+---------+------------+--------+------+
| 2001 | Finland | Phone      |     10 |    1 |
| 2000 | Finland | Computer   |   1500 |    2 |
| 2001 | USA     | Calculator |     50 |    1 |
| 2001 | USA     | Computer   |   1500 |    2 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2000 | Germany | Calculator |     75 |    1 |
| 2001 | Germany | Calculator |     79 |    3 |

DENSE_RANK

DENSE_RANK()

Deskripsi: Fungsi ini mengembalikan peringkat setiap nilai dalam satu set nilai.
DENSE_RANK() dan RANK() memiliki fitur serupa, tetapi nilai terkait dari DENSE_RANK() tidak menghasilkan celah dalam urutan.
Tipe Data Nilai Pengembalian: BIGINT.

Contoh:

select year,country,product,profit,dense_rank() over (partition by country order by profit) as dense_rank from testwindow;
+------+---------+------------+--------+------------+
| year | country | product    | profit | dense_rank |
+------+---------+------------+--------+------------+
| 2001 | Finland | Phone      |     10 |          1 |
| 2000 | Finland | Computer   |   1500 |          2 |
| 2001 | USA     | Calculator |     50 |          1 |
| 2001 | USA     | Computer   |   1500 |          2 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2000 | Germany | Calculator |     75 |          1 |
| 2001 | Germany | Calculator |     79 |          2 |

NTILE

NTILE(n)

Deskripsi: Fungsi ini mendistribusikan data dalam setiap partisi jendela ke dalam n ember. Ember dinomori dari 1 hingga n.
Perbedaan maksimum antara nomor ember adalah 1. Jika baris data dalam partisi jendela tidak didistribusikan secara merata ke setiap ember, data sisa didistribusikan mulai dari ember pertama dengan satu baris data untuk setiap ember. Sebagai contoh, jika ada enam baris dan empat ember, baris-baris tersebut didistribusikan ke ember dengan cara berikut: 1, 1, 2, 2, 3, dan 4.
Tipe Data Nilai Pengembalian: BIGINT.

Contoh:

select year,country,product,profit,ntile(2) over (partition by country order by profit) as ntile2 from testwindow;
+------+---------+------------+--------+--------+
| year | country | product    | profit | ntile2 |
+------+---------+------------+--------+--------+
| 2001 | USA     | Calculator |     50 |      1 |
| 2001 | USA     | Computer   |   1500 |      2 |
| 2001 | Finland | Phone      |     10 |      1 |
| 2000 | Finland | Computer   |   1500 |      2 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2000 | Germany | Calculator |     75 |      1 |
| 2001 | Germany | Calculator |     79 |      2 |

ROW_NUMBER

ROW_NUMBER()

Deskripsi: Fungsi ini mengembalikan nomor unik dan berurutan untuk setiap baris berdasarkan urutan baris dalam partisi jendela, dimulai dari 1.
Tipe Data Nilai Pengembalian: BIGINT.

Contoh:

SELECT year, country, product, profit, ROW_NUMBER() OVER(PARTITION BY country) AS row_num1 FROM testwindow;
+------+---------+------------+--------+----------+
| year | country | product    | profit | row_num1 |
+------+---------+------------+--------+----------+
| 2001 | USA     | Calculator |     50 |        1 |
| 2001 | USA     | Computer   |   1500 |        2 |
| 2000 | Germany | Calculator |     75 |        1 |
| 2000 | Germany | Calculator |     75 |        2 |
| 2001 | Germany | Calculator |     79 |        3 |
| 2000 | Finland | Computer   |   1500 |        1 |
| 2001 | Finland | Phone      |     10 |        2 |

PERCENT_RANK

PERCENT_RANK()

Deskripsi: Fungsi ini mengembalikan persentase peringkat setiap nilai dalam dataset dalam format (r - 1)/(n - 1). r adalah peringkat baris saat ini yang dihitung oleh RANK(), dan n adalah jumlah total baris dalam partisi jendela saat ini.
Tipe Data Nilai Pengembalian: DOUBLE.

Contoh:

select year,country,product,profit,PERCENT_RANK() over (partition by country order by profit) as ntile3 from testwindow;
+------+---------+------------+--------+--------+
| year | country | product    | profit | ntile3 |
+------+---------+------------+--------+--------+
| 2001 | Finland | Phone      |     10 |    0.0 |
| 2000 | Finland | Computer   |   1500 |    1.0 |
| 2001 | USA     | Calculator |     50 |    0.0 |
| 2001 | USA     | Computer   |   1500 |    1.0 |
| 2000 | Germany | Calculator |     75 |    0.0 |
| 2000 | Germany | Calculator |     75 |    0.0 |
| 2001 | Germany | Calculator |     79 |    1.0 |

FIRST_VALUE

FIRST_VALUE(x)

Deskripsi: Fungsi ini mengembalikan nilai baris pertama dalam partisi jendela.
Tipe Data Nilai Pengembalian: Sama dengan tipe argumen input.

Contoh:

select year,country,product,profit,first_value(profit) over (partition by country order by profit) as firstValue from testwindow;
+------+---------+------------+--------+------------+
| year | country | product    | profit | firstValue |
+------+---------+------------+--------+------------+
| 2000 | Germany | Calculator |     75 |         75 |
| 2000 | Germany | Calculator |     75 |         75 |
| 2001 | Germany | Calculator |     79 |         75 |
| 2001 | USA     | Calculator |     50 |         50 |
| 2001 | USA     | Computer   |   1500 |         50 |
| 2001 | Finland | Phone      |     10 |         10 |
| 2000 | Finland | Computer   |   1500 |         10 |

LAST_VALUE

LAST_VALUE(x)

Deskripsi: Fungsi ini mengembalikan nilai baris terakhir dalam partisi jendela. Kerangka jendela default dari LAST_VALUE adalah rows between unbounded preceding and current row, yang membandingkan data dalam baris saat ini dan semua baris sebelumnya. Jika Anda ingin LAST_VALUE mengembalikan nilai baris terakhir, tambahkan klausa berikut setelah klausa ORDER BY: rows between unbounded preceding and unbounded following.
Tipe Data Nilai Pengembalian: Sama dengan tipe argumen input.

Contoh 1:

select year,country,product,profit,last_value(profit) over (partition by country order by profit) as firstValue from testwindow;
+----------------+-------------------+-------------------+------------------+----------------------+
| year           | country           | product           | profit           | firstValue           |
+----------------+-------------------+-------------------+------------------+----------------------+
|           2001 | USA               | Calculator        |               50 |                   50 |
|           2001 | USA               | Computer          |             1500 |                 1500 |
|           2001 | Finland           | Phone             |               10 |                   10 |
|           2000 | Finland           | Computer          |             1500 |                 1500 |
|           2000 | Germany           | Calculator        |               75 |                   75 |
|           2000 | Germany           | Calculator        |               75 |                   75 |
|           2001 | Germany           | Calculator        |               79 |                   79 |

Contoh 2:

select year,country,product,profit,last_value(profit) over (partition by country order by profitrows between unbounded preceding and unbounded following) as lastValue from testwindow;
+------+---------+-----------+--------+-----------+
| year | country | product   | profit | lastValue |
+------+---------+-----------+--------+-----------+
| 2001 | Finland | Phone     |   10   |   1500    |
| 2000 | Finland | Computer  |  1500  |   1500    |
| 2000 | Germany | Calculator|   75   |    79     |
| 2000 | Germany | Calculator|   75   |    79     |
| 2001 | Germany | Calculator|   79   |    79     |
| 2001 | USA     | Calculator|   50   |   1500    |
| 2001 | USA     | Computer  |  1500  |   1500    |
+------+---------+-----------+--------+-----------+

LAG

LAG(x[, offset[, default_value]])

Deskripsi: Fungsi ini mengembalikan nilai baris yang mendahului baris saat ini sebanyak offset baris dalam jendela.
Nilai awal offset adalah 0, yang menentukan baris data saat ini. Nilai offset dapat berupa ekspresi skalar. Nilai default offset adalah 1.
Jika nilai offset adalah null atau lebih besar dari panjang jendela, default_value dikembalikan. Jika Anda tidak menentukan default_value, null dikembalikan.
Tipe Data Nilai Pengembalian: Sama dengan tipe argumen input.

Contoh:

select year,country,product,profit,lag(profit) over (partition by country order by profit) as lag from testwindow;
+------+---------+------------+--------+------+
| year | country | product    | profit | lag  |
+------+---------+------------+--------+------+
| 2001 | USA     | Calculator |     50 | NULL |
| 2001 | USA     | Computer   |   1500 |   50 |
| 2000 | Germany | Calculator |     75 | NULL |
| 2000 | Germany | Calculator |     75 |   75 |
| 2001 | Germany | Calculator |     79 |   75 |
| 2001 | Finland | Phone      |     10 | NULL |
| 2000 | Finland | Computer   |   1500 |   10 |

LEAD

LEAD(x[,offset[, default_value]])

Deskripsi: Fungsi ini mengembalikan nilai baris yang mengikuti baris saat ini sebanyak offset baris dalam jendela.
Nilai awal offset adalah 0, yang menentukan baris data saat ini. Nilai offset dapat berupa ekspresi skalar. Nilai default offset adalah 1.
Jika nilai offset adalah null atau lebih besar dari panjang jendela, default_value dikembalikan. Jika Anda tidak menentukan default_value, null dikembalikan.
Tipe Data Nilai Pengembalian: Sama dengan tipe argumen input.

Contoh:

select year,country,product,profit,lead(profit) over (partition by country order by profit) as lead from testwindow;
+------+---------+------------+--------+------+
| year | country | product    | profit | lead |
+------+---------+------------+--------+------+
| 2000 | Germany | Calculator |     75 |   75 |
| 2000 | Germany | Calculator |     75 |   79 |
| 2001 | Germany | Calculator |     79 | NULL |
| 2001 | Finland | Phone      |     10 | 1500 |
| 2000 | Finland | Computer   |   1500 | NULL |
| 2001 | USA     | Calculator |     50 | 1500 |
| 2001 | USA     | Computer   |   1500 | NULL |

NTH_VALUE

NTH_VALUE(x, offset)

Deskripsi: Fungsi ini mengembalikan nilai baris yang digeser oleh jumlah offset baris tertentu dalam jendela. Offset dimulai dari 1.
Jika nilai offset adalah null atau lebih besar dari jumlah nilai dalam jendela, null dikembalikan. Jika nilai offset adalah 0 atau negatif, kesalahan akan dikembalikan.
Tipe Data Nilai Pengembalian: Sama dengan tipe argumen input.

Contoh:

select year,country,product,profit,nth_value(profit,1) over (partition by country order by profit) as nth_value from testwindow;
+------+---------+------------+--------+-----------+
| year | country | product    | profit | nth_value |
+------+---------+------------+--------+-----------+
| 2001 | Finland | Phone      |     10 |        10 |
| 2000 | Finland | Computer   |   1500 |        10 |
| 2001 | USA     | Calculator |     50 |        50 |
| 2001 | USA     | Computer   |   1500 |        50 |
| 2000 | Germany | Calculator |     75 |        75 |
| 2000 | Germany | Calculator |     75 |        75 |
| 2001 | Germany | Calculator |     79 |        75 |