Pembingkaian ROWS vs RANGE

Setelah Anda dapat menulis total berjalan, pertanyaan lanjutan yang wajar adalah: "Apa perbedaan antara ROWS dan RANGE dalam bingkai jendela?" Ini merupakan indikator tingkat menengah yang tepat. Banyak kandidat menggunakan bingkai setiap hari tanpa menyadari bahwa kedua kata kunci tersebut berperilaku berbeda.

Keduanya menentukan kumpulan baris yang dilihat agregat, tetapi menghitung kumpulan itu dengan cara yang secara mendasar berbeda. Pahami ini dengan benar dan Anda akan menonjol.

ROWS bersifat berdasarkan posisi. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW secara harfiah berarti baris saat ini ditambah dua baris fisik tepat di atasnya dalam urutan.

ROWS tidak memedulikan apakah baris-baris di sebelahnya memiliki nilai ORDER BY yang sama. Tiga baris berarti tiga baris, tidak lebih dan tidak kurang. Bingkai inilah yang hampir selalu Anda inginkan untuk rata-rata bergerak dan total berjalan yang ketat.

RANGE berbasis nilai. Ini mencakup setiap baris yang nilai ORDER BY-nya berada dalam rentang logis dari nilai baris saat ini, bukan berdasarkan jumlah baris.

Konsekuensi terpentingnya: dengan RANGE, semua baris yang memiliki nilai sama pada ORDER BY diperlakukan sebagai satu kelompok baris setara. Semuanya memperoleh bingkai yang sama dan karenanya hasil yang sama.

Misalkan dua penjualan terjadi pada 2024-03-02, dengan jumlah 30 dan 40, setelah 2024-03-01 dengan jumlah 100.

• Dengan RANGE hingga CURRENT ROW: kedua baris 03-02 adalah baris setara, sehingga keduanya menampilkan 100 + 30 + 40 = 170.
• Dengan ROWS hingga CURRENT ROW: baris 03-02 pertama menampilkan 130, sedangkan baris kedua menampilkan 170, karena setiap baris memperluas bingkai satu posisi demi satu posisi.

Data sama, angka berbeda. Perbedaan pada nilai yang sama inilah inti pertanyaannya.

Ingat bahwa OVER (ORDER BY ...) tanpa bingkai eksplisit menggunakan RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Artinya, total berjalan yang ditulis secara naif secara diam-diam menggunakan RANGE. Pada kunci pengurutan yang unik, hasilnya identik dengan ROWS, sehingga masalahnya tersembunyi. Begitu muncul nilai pengurutan duplikat, total berjalan Anda secara diam-diam menggabungkan baris-baris yang nilainya sama. Inilah alasan teknisi berpengalaman menuliskan ROWS secara eksplisit.

RANGE dapat menerima pergeseran nilai, bukan hanya UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW pada kolom tanggal atau numerik menyertakan setiap baris yang nilainya berada dalam jarak 7 unit dari nilai saat ini.

Pada tanggal, ini menghasilkan jendela "7 hari terakhir" yang benar-benar melewati hari yang hilang dengan tepat, sedangkan ROWS 7 PRECEDING akan mengambil 7 baris sebelumnya terlepas dari celah yang ada. Memahami nuansa ini merupakan jawaban yang kuat.

Kebalikannya: ROWS tidak memahami celah dalam data. Jika tabel penjualan Anda tidak memiliki data akhir pekan, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW mencakup 7 hari yang tercatat, yang sebenarnya mungkin mencakup dua minggu kalender.

Jadi, pilihannya bergantung pada maksud Anda: N rekaman terakhir berarti ROWS; N unit nilai terakhir (hari, dolar) berarti RANGE dengan pergeseran.

Jawaban wawancara yang jujur menyebutkan perbedaan dukungan:

• PostgreSQL mendukung RANGE dengan pergeseran (sejak v11) dan ROWS secara penuh.
• SQL Server mendukung ROWS dan RANGE, tetapi RANGE hanya dengan UNBOUNDED/CURRENT ROW, bukan pergeseran numerik.
• MySQL 8 mendukung keduanya, dengan jenis pergeseran RANGE yang terbatas.

Jika pergeseran numerik RANGE tidak tersedia, Anda dapat menirunya dengan penggabungan ke diri sendiri atau kalender yang dibuat. Menyebutkan hal ini menunjukkan pemahaman tentang kebutuhan produksi.

Ada mode pembingkaian ketiga yang kurang dikenal: GROUPS. Mode ini menghitung kelompok nilai setara, bukan baris atau nilai. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW menyertakan kelompok nilai setara saat ini dan kelompok sebelumnya.

Mode ini jarang diperlukan, tetapi menyebutkannya ketika ditanya "apakah ada mode bingkai lainnya?" menunjukkan pemahaman yang mendalam. Dukungan tersedia di PostgreSQL 11+ dan beberapa sistem lainnya.

Ringkas seluruh topik ini menjadi satu kalimat yang dapat Anda sampaikan dalam wawancara:

"Gunakan ROWS ketika yang saya maksud adalah jumlah baris fisik yang tetap, gunakan RANGE ketika yang saya maksud adalah rentang logis dari nilai pengurutan, dan ingat bahwa nilai yang sama membuat keduanya berbeda karena bingkai bawaan RANGE mengelompokkan nilai yang sama."

Satu kalimat tersebut menjawab pertanyaannya secara lengkap dan meyakinkan.

Menempatkan kedua pembingkaian dalam satu kueri membuat perbedaannya terlihat. Jalankan ini pada data dengan tanggal ganda dan bandingkan kedua kolom baris demi baris.

Pada tanggal yang unik, kedua kolom sama persis; pada tanggal yang sama, kolom RANGE mengulang total yang sama di antara nilai-nilai setara, sedangkan kolom ROWS meningkat satu langkah setiap kali.

Dua baris memiliki nilai ORDER BY yang sama dengan bingkai bawaan. Apa yang terjadi?

ROWS menghitung baris fisik; RANGE menghitung berdasarkan nilai logis kolom pengurutan dan mengelompokkan nilai yang sama sebagai nilai setara. Bingkai bawaan OVER (ORDER BY ...) adalah RANGE, sehingga total kumulatif sederhana dapat menggabungkan nilai pengurutan yang sama.

Pilih ROWS untuk "N rekaman terakhir" dan jendela bergerak yang tidak terpengaruh celah; pilih RANGE dengan pergeseran untuk "N hari/dolar terakhir" yang memperhitungkan celah. Mengetahui bahwa GROUPS tersedia merupakan nilai tambah. Selanjutnya, kita menerapkan pembingkaian ROWS untuk membuat rata-rata bergerak.