Dalam SQL, jumlah kumulatif merujuk kepada kaedah yang membolehkan kita mengira jumlah lajur lajur berangka dalam jadual pangkalan data. Jumlah terkumpul dimainkan dalam pelbagai jenis senario seperti menjejak kemajuan atau mengira purata pusingan nilai tertentu.
Dalam tutorial ini, kita akan belajar bagaimana untuk melaksanakan dan menggunakan jumlah kumulatif dalam SQL menggunakan pelbagai kaedah dan teknik.
Data Sampel
Untuk lebih memahami cara kita boleh melaksanakan dan menggunakan jumlah kumulatif, mari kita mulakan dengan menyediakan jadual asas dengan data sampel. Ini membolehkan kami menunjukkan cara kerja semua kaedah dengan cepat dan cekap dalam siaran ini.
Mulakan dengan mencipta jadual asas yang menyimpan maklumat produk.
CIPTA produk JADUAL (
product_id INT PRIMARY KUNCI,
nama_produk VARCHAR ( 255 ) ,
harga PERPULUHAN ( 10 , 2 )
) ;
Ini harus membuat jadual baharu yang dipanggil 'produk' yang menyimpan id produk, nama produk dan harga setiap produk.
Kami kemudiannya boleh meneruskan untuk menambah data sampel seperti yang ditunjukkan dalam penyata sisipan contoh berikut:
MASUKKAN KE DALAM produk ( id_produk, nama_produk, harga )
NILAI
( 1 , 'Kod Studio Visual' , 10.00 ) ,
( 2 , 'Teks Sublime' , 80.00 ) ,
( 3 , 'PyCharm Professional' , 199.00 ) ,
( 4 , 'IDE Eclipse' , 30.00 ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699.00 ) ,
( 6 , 'Desktop GitHub' , 20.00 ) ,
( 7 , 'Tunggu Perisian' , 10.00 ) ,
( 8 , 'Xcode' , 660.00 ) ,
( 9 , 'NetBeans' , 0.00 ) ,
( 10 , 'Atom' , 60.00 ) ;
NOTA: Data yang diberikan adalah rekaan sepenuhnya. Ia tidak mewakili harga sebenar mana-mana item yang disenaraikan.
Jadual yang terhasil adalah seperti berikut:
Jumlah Terkumpul SQL (Self Join)
Salah satu teknik yang boleh kita gunakan untuk melakukan jumlah terkumpul pada lajur tertentu ialah menggunakan kaedah sambung sendiri. Satu kelebihan kaedah ini ialah ia berfungsi dalam hampir semua pangkalan data SQL walaupun yang tidak menyokong fungsi Window.
Ambil contoh jadual 'produk' sebelumnya. Kita boleh mencipta jumlah kumulatif lajur harga seperti yang ditunjukkan dalam pertanyaan berikut:
PILIHp1.product_id,
p1.nama_produk,
p1.harga,
JUMLAH ( p2.harga ) AS kumulatif_sum
DARI
produk p1
SERTAI
produk p2
HIDUP
p1.product_id > = p2.product_id
KUMPULAN OLEH
p1.product_id,
p1.nama_produk,
p1.harga
PERINTAH OLEH
p1.product_id;
Adakah anda perasan cara kerja pertanyaan itu? Jika tidak, kekal bersama kami sambil kami menerangkannya langkah demi langkah.
Dalam contoh pertanyaan yang diberikan, kami mulakan dengan mencipta dua alias - 'p1' dan 'p2' - untuk jadual 'produk' yang membolehkan kami melakukan gabungan sendiri.
Kami kemudiannya meneruskan untuk menyertai 'p1' dan 'p2' dengan syarat 'id_produk' bagi 'p1' lebih besar daripada atau sama dengan 'id_produk' bagi 'p2'.
Dalam langkah seterusnya, kami memanggil fungsi sum() yang pada asasnya mengira jumlah kumulatif harga untuk setiap baris.
Akhir sekali, kami mengumpulkan hasil menggunakan 'id_produk', 'nama_produk' dan 'harga' dan memesan hasilnya.
Selepas operasi ini, kita harus mempunyai jumlah terkumpul untuk setiap rekod seperti yang ditunjukkan dalam jadual yang terhasil seperti yang ditunjukkan dalam yang berikut:
Seperti yang anda lihat, kami mendapat jumlah keseluruhan semua baris sebelumnya. Baris terakhir harus mengandungi jumlah keseluruhan semua baris sebelumnya.
Jumlah Kumulatif SQL (Fungsi Tetingkap)
Cara yang lebih cekap dan praktikal untuk mengira jumlah kumulatif dalam SQL ialah memanfaatkan fungsi tetingkap apabila ia disokong.
Jika anda mempunyai pangkalan data seperti SQL Server, PostgreSQL atau MySQL versi 8.0 dan ke atas, ini ialah kaedah yang paling berguna dan disyorkan untuk menentukan jumlah kumulatif lajur yang diberikan.
Lihat contoh seperti yang ditunjukkan dalam berikut:
PILIHproduct_id,
Nama Produk,
harga,
JUMLAH ( harga ) LEBIH ( PESANAN MENGIKUT product_id ) AS kumulatif_sum
DARI
produk;
Dalam kes ini, kita mulakan dengan memilih lajur 'id_produk', 'nama_produk' dan 'harga' daripada jadual 'produk'.
Kami kemudian menggunakan fungsi SUM() sebagai fungsi tetingkap menggunakan klausa OVER.
Dalam klausa OVER, kami menentukan klausa ORDER BY yang mentakrifkan susunan di mana jumlah kumulatif dikira.
Ini sepatutnya mengembalikan output yang serupa seperti yang ditunjukkan dalam yang berikut:
Anda akan perasan bahawa menggunakan fungsi tetingkap adalah lebih koheren, cekap dan boleh dibaca berbanding dengan menggunakan sambung sendiri.
Kesimpulan
Dalam tutorial ini, kami mempelajari semua tentang jumlah kumulatif dalam SQL. Kami juga membincangkan cara menggunakan gabungan sendiri dan fungsi tetingkap untuk melaksanakan jumlah kumulatif dalam SQL.