Kriteria ROWS & RANGE

Saat ini, kita belum bisa menentukan secara fleksibel berapa banyak baris sebelum atau sesudah yang perlu diperhitungkan. Sekarang hal itu bisa dilakukan dengan kriteria ROWS & RANGE. Untuk menggunakannya, kita menulis:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

Dan kita bisa menentukan opsi berikut:

• CURRENT ROW: baris saat ini
• n PRECEDING: baris sebelum baris saat ini
• n FOLLOWING: baris setelah baris saat ini 

Perbedaan antara ROWS & RANGE adalah kriteria ROWS tidak memedulikan nilainya, hanya posisinya, sedangkan RANGE mendefinisikan jendela berdasarkan rentang nilai, bukan posisi baris.

Untuk RANGE, kita harus menentukan ORDER BY --column_name-- karena jika tidak, sistem tidak akan tahu cara memilih jendela.

Contoh:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

Di sini, kueri membuat jendela yang mencakup baris saat ini, baris sebelumnya, dan baris sesudahnya.

ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

Di sini, dibuat sebuah jendela yang menyertakan level saat ini, satu level sebelumnya, dan satu level sesudahnya untuk setiap level (diurutkan secara menaik). Jika level saat ini adalah 5, jendela itu akan menyertakan level 4, 5, dan 6.

Catatan: Penggunaan RANGE BETWEEN mungkin mengakibatkan lebih banyak baris disertakan dalam jendela, karena penggunaan ini menyertakan semua baris yang memiliki nilai sama dengan nilai dalam rentang, sedangkan ROWS BETWEEN akan selalu menyertakan jumlah baris yang sama (selama tersedia dalam kumpulan data). 

Selain itu, pada tanggal yang disimpan sebagai teks (seperti '2024-01-05'), offset RANGE tidak melakukan perhitungan tanggal: dengan 1 PRECEDING, jendela hanya mencakup baris dengan tanggal yang sama. Untuk menghitung hari, urutkan berdasarkan angka, misalnya julianday(date).

Berikut contoh sederhana untuk menjelaskan perbedaan ROWS dan RANGE:

Menggunakan ROWS:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

Menggunakan RANGE:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range