Get best of the week

Fungsi jendela dengan "jendela" atau cara menggunakan bingkai

Halo semuanya. Pada 26 Februari, kelas-kelas dimulai di OTUS dalam sebuah grup baru di kursus "Pengembang MS SQL Server" . Dalam hal ini, saya ingin berbagi dengan Anda publikasi saya tentang fungsi jendela. Ngomong-ngomong, dalam minggu mendatang Anda masih dapat bergabung dengan grup ;-).




Fungsi jendela sudah mapan dalam praktik kami, tetapi sedikit orang yang tahu cara kerja RANGE dan ROWS.

Mungkin itu sebabnya mereka agak kurang umum. Tujuan artikel ini adalah untuk memberikan contoh penggunaan sehingga Anda pasti tidak memiliki pertanyaan "Siapa itu siapa?" dan "Bagaimana cara menerapkannya?". Pertanyaan "Kenapa?" Artikel akan tetap terang.

Mari kita lihat apa itu frame, dan bagaimana mencapai efek yang sama menggunakan ORDER By dalam klausa OVER ().

Untuk demonstrasi, kami akan menggunakan tabel sederhana sehingga Anda dapat menghitung contoh tanpa menggunakan kompiler. Secara umum, saya sangat merekomendasikannya - lihat dan pikirkan apa yang akan menjadi hasil dari eksekusi, dan kemudian periksa sendiri - sehingga Anda akan menemukan bintik-bintik putih dalam persepsi fungsi jendela, yang mungkin tidak jelas ketika Anda membaca hasil yang selesai.

Meja

Create table sales 
(sales_id INT PRIMARY KEY, sales_dt DATETIME2 DEFAULT GETUTCDATE(),  customer_id INT, item_id INT, cnt INT, price_per_item DECIMAL(19,4));

INSERT INTO sales
(sales_id, sales_dt, customer_id, item_id, cnt, price_per_item)
VALUES
(1, '2020-01-10T10:00:00', 100, 200, 2, 30.15),
(2, '2020-01-11T11:00:00', 100, 311, 1, 5.00),
(3, '2020-01-12T14:00:00', 100, 400, 1, 50.00),
(4, '2020-01-12T20:00:00', 100, 311, 5, 5.00),
(5, '2020-01-13T10:00:00', 150, 311, 1, 5.00),
(6, '2020-01-13T11:00:00', 100, 315, 1, 17.00),
(7, '2020-01-14T10:00:00', 150, 200, 2, 30.15),
(8, '2020-01-14T15:00:00', 100, 380, 1, 8.00),
(9, '2020-01-14T18:00:00', 170, 380, 3, 8.00),
(10, '2020-01-15T09:30:00', 100, 311, 1, 5.00),
(11, '2020-01-15T12:45:00', 150, 311, 5, 5.00),
(12, '2020-01-15T21:30:00', 170, 200, 1, 30.15);

Mari kita mulai dengan momen sederhana - perbedaan dalam fungsi SUM dengan dan tanpa penyortiran

SELECT sales_id, customer_id, count, 
SUM(count) OVER () as total,
SUM(count) OVER (ORDER BY customer_id) AS cum,
SUM(count) OVER (ORDER BY customer_id, sales_id) AS cum_uniq
FROM sales
ORDER BY customer_id, sales_id;



Mari kita lihat rake tidak mencolok pertama, bagaimana menurut Anda berapa banyak pengembang berpikir bahwa cum dan cum_uniq adalah sama ketika membaca kode? Pikirkan sedikit? Mungkin, tetapi karena di sini jelas, dan apakah itu sangat jelas ketika membaca kode dalam aplikasi, dan bahkan dengan keunikan yang tidak begitu jelas dari bidang semacam itu.

Sekarang buka jendela kita yang indah.

Jendela, atau lebih tepatnya bingkai adalah 2 jenis ROWS dan RANGE, kenali ROWS terlebih dahulu.
Opsi pembatasan bingkai:

  1. Semuanya sebelum baris / rentang saat ini dan nilai aktual dari baris saat ini
    ANTARA PENGECUALIAN YANG TIDAK DIUNDANG
    ANTARA PENGECUALIAN YANG TIDAK DIUNDANG DAN BARANG SAAT INI
  2. Baris / rentang saat ini dan semuanya setelahnya
    ANTARA BARIS SAAT INI DAN BERIKUTNYA
  3. Menentukan berapa banyak baris sebelum dan sesudah untuk disertakan (tidak didukung untuk RANGE)
    ANTARA N Sebelumnya dan N Mengikuti
    ANTARA BARIS SAAT INI DAN N Mengikuti
    ANTARA N Sebelum dan NAMAN BARU

Tapi mari kita lihat frame dalam aksi.

Kami membuka "jendela" pada baris saat ini dan semua yang sebelumnya, untuk fungsi SUM, seperti yang Anda lihat, ini bertepatan dengan penyortiran ASC

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY customer_id, sales_id;



Pada saat yang sama, saya ingin mengingatkan Anda bahwa urutan pengurutan di jendela (dalam klausa OVER ()) tidak terkait dengan urutan pengurutan dalam kueri itu sendiri, dalam contoh itu adalah sama untuk menyederhanakan perhitungan jika Anda memutuskan untuk memeriksa perhitungan dan pemahaman Anda tentang cara fungsi berfungsi.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id) AS cum_uniq,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS current_and_all_before2
FROM sales
ORDER BY cnt;



Sekarang mari kita lihat fungsionalitas frame ketika kita memasukkan semua baris berikutnya.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_frame,
SUM(cnt) OVER (ORDER BY customer_id DESC, sales_id DESC) AS current_and_all_order_desc
FROM sales
ORDER BY customer_id, sales_id;



Seperti yang Anda lihat di sini, Anda juga bisa mendapatkan hasil yang sama untuk fungsi agregat, jika Anda menggunakan penyortiran terbalik - tetapi ROWS sedikit lebih stabil, karena jika bidang penyortiran Anda tidak unik, Anda bisa mendapatkan kejutan dalam bentuk nilai yang sama.

Dan akhirnya, opsi yang tidak bisa lagi ditiru oleh macam-macam, ketika kita menentukan jumlah garis tertentu yang harus dimasukkan dalam bingkai

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS before_and_current,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS current_and_1_next,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, sales_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS before2_and_2_next
FROM sales
ORDER BY customer_id, sales_id;



Dalam opsi ini, Anda sudah secara khusus menunjukkan garis mana yang berada dalam kisaran, dan menurut saya, yang paling jelas dari hasil.

Perbedaan antara ROWS dan RANGE


Perbedaannya adalah bahwa ROWS beroperasi pada satu baris dan RANGE pada suatu rentang. Benar, ini jelas dari namanya, tetapi sedikit menjelaskan dalam praktik?

Mari kita lihat gambar (sumber di bagian bawah artikel)





Sekarang, jika kita perhatikan dengan teliti, akan menjadi jelas bahwa baris dengan nilai parameter sortir yang sama disebut range.

Seperti yang disebutkan di atas, ROWS terbatas hanya string, sementara RANGE menangkap seluruh rentang nilai yang cocok yang Anda tentukan dalam fungsi jendela ORDER BY.

SELECT sales_id, customer_id, cnt, 
SUM(cnt) OVER (ORDER BY customer_id) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, sales_id;



ROWS - selalu beroperasi pada garis tertentu, bahkan jika penyortiran tidak unik, tetapi RANGE hanya menggabungkan periode menjadi rentang dengan nilai-nilai yang cocok dari bidang penyortiran. Dalam pengertian ini, fungsi ini sangat mirip dengan perilaku fungsi SUM () dengan pengurutan berdasarkan bidang yang tidak unik. Mari kita lihat contoh lain.

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id, price_per_item) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS UNBOUNDED PRECEDING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE UNBOUNDED PRECEDING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item;




Sudah ada 2 bidang dan rentang ditentukan oleh rentang dengan nilai yang cocok untuk kedua bidang.

Dan opsinya adalah ketika kami memasukkan dalam perhitungan semua baris berikutnya dari yang sekarang, yang dalam kasus fungsi SUM bertepatan dengan nilai yang dapat diperoleh dengan menggunakan penyortiran terbalik:

SELECT sales_id, customer_id, price_per_item, cnt, 
SUM(cnt) OVER (ORDER BY customer_id DESC, price_per_item DESC) AS cum_uniq,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before,
customer_id,
cnt,
SUM(cnt) OVER (ORDER BY customer_id, price_per_item RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS current_and_all_before2
FROM sales
ORDER BY 2, price_per_item, sales_id desc;



Seperti yang Anda lihat, RANGE kembali menangkap seluruh rentang pasangan yang cocok.

Terlepas dari kenyataan bahwa fungsi ROWS dan RANGE jauh dari baru setiap kali, timbul pertanyaan tentang bagaimana menggunakannya. Saya harap artikel ini telah menambahkan pemahaman tentang bagaimana ROWS dan RANGE berbeda, dan sekarang Anda tidak akan ragu dalam hal mana frame ini atau itu diperlukan.

Sumber Ilustrasi RANGE tentang perbedaan dan fungsi BARIS ROWS
dengan SQL Server 2016, Mark Tabladillo

siap untuk kursus

More articles:


All Articles