Pembingkaian ROWS berbanding RANGE

Setelah anda boleh menulis jumlah terkumpul, soalan susulan yang semula jadi ialah: "Apakah perbezaan antara ROWS dan RANGE dalam bingkai tetingkap?" Ini ialah petunjuk tahap pertengahan yang tepat. Ramai calon menggunakan bingkai setiap hari tanpa menyedari bahawa kedua-dua kata kunci itu berkelakuan secara berbeza.

Kedua-duanya menentukan set baris yang dilihat oleh agregat, tetapi kedua-duanya mengira set itu dengan cara yang pada asasnya berbeza. Fahami perkara ini dengan betul dan anda akan lebih menonjol.

ROWS adalah berdasarkan kedudukan. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW bermaksud secara literal baris semasa serta dua baris fizikal tepat di atasnya dalam jujukan yang diisih.

Ia tidak mempedulikan sama ada baris bersebelahan berkongsi nilai ORDER BY yang sama. Tiga baris bermaksud tiga baris, tanpa pengecualian. Inilah pembingkaian yang hampir sentiasa anda mahukan untuk purata bergerak dan jumlah terkumpul yang ketat.

RANGE adalah berdasarkan nilai. Ia merangkumi setiap baris yang nilainya bagi ORDER BY berada dalam julat logik nilai baris semasa, bukannya berdasarkan bilangan baris.

Akibat yang paling penting ialah dengan RANGE, semua baris yang seri pada nilai ORDER BY dianggap sebagai satu kumpulan rakan setara. Kesemuanya mendapat bingkai yang sama dan oleh itu hasil yang sama.

Andaikan dua jualan berlaku pada 2024-03-02, dengan amaun 30 dan 40, selepas jualan pada 2024-03-01 dengan amaun 100.

• Dengan RANGE hingga CURRENT ROW: kedua-dua baris 03-02 ialah rakan setara, jadi kedua-duanya memaparkan 100 + 30 + 40 = 170.
• Dengan ROWS hingga CURRENT ROW: baris 03-02 pertama memaparkan 130, manakala baris kedua memaparkan 170, kerana setiap baris memanjangkan bingkai satu kedudukan pada satu masa.

Data yang sama, nombor yang berbeza. Perbezaan apabila berlaku seri inilah inti soalan tersebut.

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

Ini bermakna jumlah terkumpul yang naif secara senyap menggunakan RANGE. Pada kunci isihan yang unik, ia sama dengan ROWS, jadi pepijat itu tersembunyi. Sebaik sahaja nilai isihan pendua muncul, jumlah terkumpul anda secara senyap mengumpulkan baris yang seri bersama-sama. Sebab itulah jurutera berpengalaman menulis ROWS secara eksplisit.

RANGE boleh mengambil ofset nilai, bukan hanya UNBOUNDED. RANGE BETWEEN 7 PRECEDING AND CURRENT ROW pada lajur tarikh atau berangka merangkumi setiap baris yang nilainya berada dalam lingkungan 7 unit daripada nilai semasa.

Pada tarikh, ini menghasilkan tetingkap "7 hari terakhir" yang sebenar dan melangkau hari yang tiada dengan betul, manakala ROWS 7 PRECEDING akan mengambil 7 baris sebelumnya tanpa mengira jurang. Mengetahui nuansa ini merupakan jawapan yang mantap.

Sebaliknya, ROWS tidak memahami jurang dalam data. Jika jadual jualan Anda tiada rekod hujung minggu, ROWS BETWEEN 6 PRECEDING AND CURRENT ROW merangkumi 7 hari yang direkodkan, yang sebenarnya mungkin meliputi dua minggu kalendar.

Jadi, pilihan bergantung pada tujuan: N rekod terakhir bermaksud ROWS; N unit nilai terakhir (hari, dolar) bermaksud RANGE dengan ofset.

Jawapan temu duga yang jujur menyatakan jurang sokongan:

• PostgreSQL menyokong RANGE dengan ofset (sejak v11) dan menyokong ROWS sepenuhnya.
• Pelayan SQL menyokong ROWS dan RANGE, tetapi RANGE hanya dengan UNBOUNDED/CURRENT ROW, bukan ofset berangka.
• MySQL 8 menyokong kedua-duanya, dengan jenis ofset RANGE yang terhad.

Jika ofset RANGE berangka tidak tersedia, Anda boleh menirunya dengan cantuman kendiri atau kalendar yang dijana. Menyebut perkara ini menunjukkan kesedaran terhadap persekitaran produksi.

Terdapat mod pembingkaian ketiga yang kurang diketahui: GROUPS. Mod ini mengira kumpulan setara, bukannya baris atau nilai. GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW merangkumi kumpulan setara semasa dan kumpulan sebelumnya.

Mod ini jarang diperlukan, tetapi menyebutnya apabila ditanya "adakah terdapat mod bingkai lain?" menunjukkan pemahaman yang mendalam. Sokongan tersedia dalam PostgreSQL 11+ dan beberapa sistem lain.

Ringkaskan seluruh topik ini menjadi satu ayat yang boleh Anda ucapkan dalam temu duga:

"Gunakan ROWS apabila saya maksudkan bilangan baris fizikal yang tetap, gunakan RANGE apabila saya maksudkan julat logik nilai susunan, dan ingat bahawa nilai yang sama menyebabkan kedua-duanya berbeza kerana RANGE lalai mengumpulkan nilai yang sama."

Satu ayat ini menjawab soalan tersebut dengan lengkap dan yakin.

Meletakkan kedua-dua bingkai dalam satu pertanyaan menjadikan perbezaannya jelas. Jalankan pertanyaan ini pada data dengan tarikh pendua dan bandingkan kedua-dua lajur baris demi baris.

Pada tarikh unik, lajur tersebut sepadan tepat; pada tarikh yang sama, lajur RANGE mengulangi jumlah yang sama untuk semua nilai setara, manakala lajur ROWS meningkat satu langkah demi satu langkah.

ROWS mengira baris fizikal; RANGE mengira berdasarkan nilai logik lajur susunan dan mengumpulkan nilai yang sama sebagai nilai setara. Bingkai lalai OVER (ORDER BY ...) ialah RANGE, sebab itulah jumlah berjalan yang mudah boleh menggabungkan nilai pengisihan pendua.

Pilih ROWS untuk "N rekod terakhir" dan tetingkap bergerak yang tidak terjejas oleh jurang; pilih RANGE dengan ofset untuk "N hari/dolar terakhir" yang mengambil kira jurang. Mengetahui kewujudan GROUPS ialah kelebihan tambahan. Seterusnya, kita menggunakan pembingkaian ROWS untuk membina purata bergerak.