Dalam tutorial ini, kita akan belajar tentang cara kerja klausa PARTITION BY dalam SQL dan mengetahui cara kita boleh menggunakannya untuk membahagikan data untuk subset yang lebih berbutir.
Sintaks:
Mari kita mulakan dengan sintaks klausa PARTITION BY. Sintaks mungkin bergantung pada konteks di mana anda menggunakannya tetapi berikut ialah sintaks umum:
PILIH lajur1, lajur2, ...
OVER (PARTITION BY partition_column1, partition_column2, ...)
DARI table_name
Sintaks yang diberikan mewakili elemen berikut:
- lajur1, lajur2 – Ini merujuk kepada lajur yang ingin kami sertakan dalam set hasil.
- PARTITION BY lajur – Klausa ini mentakrifkan cara kami ingin membahagikan atau mengumpulkan data.
Data Sampel
Mari kita cipta jadual asas dengan sampel data untuk menunjukkan cara menggunakan klausa PARTITION BY. Untuk contoh ini, mari buat jadual asas yang menyimpan maklumat produk.
CIPTA produk JADUAL (
product_id INT PRIMARY KUNCI AUTO_INCREMENT,
nama_produk VARCHAR( 255 ),
kategori VARCHAR( 255 ),
harga PERPULUHAN( 10 , 2 ),
kuantiti INT,
tarikh tamat tempoh DATE,
kod bar BIGINT
);
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Topi Chef 25cm' ,
'kedai roti' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Telur Puyuh - Dalam Tin' ,
'pantry' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Kopi - Egg Nog Capuccino' ,
'kedai roti' ,
92.53 ,
10 ,
'22-09-2023' ,
8704051853058 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Pear - Berduri' ,
'kedai roti' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Pasta - Rambut Malaikat' ,
'pantry' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Wain - Prosecco Valdobiaddene' ,
'menghasilkan' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Pastri - Aneka Mini Perancis' ,
'pantry' ,
36.73 ,
52 ,
'2023-05-29' ,
5963886298051 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Oren - Tin, Mandarin' ,
'menghasilkan' ,
65.0 ,
1 ,
'2023-04-20' ,
6131761721332 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Babi - Bahu' ,
'menghasilkan' ,
55.55 ,
73 ,
'2023-05-01' ,
9343592107125 );
masukkan
ke dalam
produk (nama_produk,
kategori,
harga,
kuantiti,
tarikh luput,
kod bar)
nilai ( 'Dc Hikiage Hira Huba' ,
'menghasilkan' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
Setelah kami mempunyai persediaan data sampel, kami boleh meneruskan dan menggunakan klausa PARTITION BY.
Penggunaan Asas
Katakan kita ingin mengira jumlah item untuk setiap kategori produk dalam jadual sebelumnya. Kita boleh menggunakan PARTITION BY untuk membahagikan item ke dalam kategori unik dan kemudian menentukan jumlah kuantiti dalam setiap kategori.
Contohnya adalah seperti berikut:
PILIH
Nama Produk,
kategori,
kuantiti,
JUMLAH(kuantiti) LEBIH (PARTITITION MENGIKUT kategori) SEBAGAI jumlah_item
DARI
produk;
Perhatikan bahawa dalam contoh yang diberikan, kami membahagikan data menggunakan lajur 'kategori'. Kami kemudian menggunakan fungsi agregat SUM() untuk menentukan jumlah item dalam setiap kategori secara berasingan. Keputusan menunjukkan jumlah item dalam setiap kategori.
Menggunakan Klausa PARTITION BY
Untuk meringkaskan, kes penggunaan yang paling biasa bagi klausa PARTITION BY adalah bersama-sama dengan fungsi tetingkap. Fungsi tetingkap digunakan pada setiap partition secara berasingan.
Beberapa fungsi tetingkap biasa untuk digunakan dengan PARTITION BY termasuk yang berikut:
- SUM() – Kira jumlah lajur dalam setiap partition.
- AVG() – Kira purata lajur dalam setiap partition.
- COUNT() – Kira bilangan baris dalam setiap partition.
- ROW_NUMBER() – Berikan nombor baris unik pada setiap baris dalam setiap partition.
- RANK() – Berikan pangkat kepada setiap baris dalam setiap partition.
- DENSE_RANK() – Berikan pangkat padat kepada setiap baris dalam setiap partition.
- NTILE() – Bahagikan data kepada kuantiti dalam setiap partition.
Itu sahaja!
Kesimpulan
Dalam tutorial ini, kami belajar cara bekerja dengan klausa PARTITION BY dalam SQL untuk membahagikan data kepada pelbagai segmen dan kemudian menggunakan operasi khusus untuk setiap partition yang terhasil secara berasingan.