Total kumulatif dalam SQL

Hasil kumulatif (kumulatif) telah lama dianggap sebagai salah satu panggilan SQL. Anehnya, bahkan setelah kemunculan fungsi jendela, ia tetap menjadi orang-orangan sawah (dalam hal apa pun, untuk pemula). Hari ini kita melihat mekanisme 10 solusi paling menarik untuk masalah ini - dari fungsi jendela hingga peretasan yang sangat spesifik.

Dalam spreadsheet seperti Excel, total berjalan dihitung sangat sederhana: hasil dalam catatan pertama cocok dengan nilainya:



... lalu kami merangkum nilai saat ini dan total sebelumnya.



Dengan kata lain

$$

$$Total_1 = Value_1 \\ Total_2 = Total_1 + Value_2 \\ Total_3 = Total_2 + Value_3 \\ \ ldots \\ Total_n = Total_ {n-1} + Value_n \\ $$

... atau:

$$

$$\ begin {cases} Total_1 = Value_1, n = 1 \\ Total_n = Total_ {n-1} + Value_n, n \ geq 2 \ end {cases}$$

Munculnya dua atau lebih kelompok dalam tabel agak mempersulit tugas: sekarang kami menghitung beberapa hasil (untuk setiap kelompok secara terpisah). Namun, di sini solusinya ada di permukaan: setiap kali perlu untuk memeriksa kelompok mana dari catatan saat ini milik. Klik dan seret , dan pekerjaan selesai:



Seperti yang Anda lihat, perhitungan total kumulatif dikaitkan dengan dua komponen yang tidak berubah:
(a) mengurutkan data berdasarkan tanggal dan
(B) mengacu pada baris sebelumnya.

Tapi apa itu SQL? Untuk waktu yang sangat lama tidak ada fungsi yang diperlukan di dalamnya. Alat yang diperlukan - fungsi jendela - pertama kali muncul hanya dalam standar SQL: 2003 . Pada titik ini, mereka sudah berada di Oracle (versi 8i). Tetapi implementasi dalam DBMS lainnya tertunda selama 5-10 tahun: SQL Server 2012, MySQL 8.0.2 (2018), MariaDB 10.2.0 (2017), PostgreSQL 8.4 (2009), DB2 9 untuk z / OS (2007) tahun), dan bahkan SQLite 3.25 (2018).

1. Fungsi jendela

Fungsi jendela mungkin adalah cara termudah. Dalam kasus dasar (tabel tanpa grup) kami menganggap data diurutkan berdasarkan tanggal:

 order by dt 

... tapi kami hanya tertarik pada garis sebelum yang sekarang:

 rows between unbounded preceding and current row 

Pada akhirnya, kita perlu penjumlahan dengan parameter ini:

 sum(val) over (order by dt rows between unbounded preceding and current row) 

Permintaan lengkap akan terlihat seperti ini:

 select s.*, coalesce(sum(s.val) over (order by s.dt rows between unbounded preceding and current row), 0) as total from test_simple s order by s.dt; 

Dalam kasus total kumulatif untuk grup (bidang grp ) kita hanya perlu satu suntingan kecil. Sekarang kami menganggap data tersebut dibagi menjadi “windows” berdasarkan grup:



Untuk menjelaskan pemisahan ini, Anda harus menggunakan partition by kata kunci:

 partition by grp 

Dan, karenanya, pertimbangkan jumlah untuk windows ini:

 sum(val) over (partition by grp order by dt rows between unbounded preceding and current row) 

Kemudian seluruh kueri dikonversi seperti ini:

 select tg.*, coalesce(sum(tg.val) over (partition by tg.grp order by tg.dt rows between unbounded preceding and current row), 0) as total from test_groups tg order by tg.grp, tg.dt; 

Kinerja fungsi jendela akan tergantung pada spesifikasi DBMS Anda (dan versinya!), Ukuran tabel, dan ketersediaan indeks. Tetapi dalam kebanyakan kasus, metode ini akan menjadi yang paling efektif. Namun, fungsi jendela tidak tersedia dalam versi DBMS yang lebih lama (yang masih digunakan). Selain itu, mereka tidak dalam DBMS seperti Microsoft Access dan SAP / Sybase ASE. Jika solusi independen vendor diperlukan, pertimbangan harus diberikan pada alternatif.

2. Subquery

Seperti disebutkan di atas, fungsi jendela diperkenalkan sangat terlambat dalam DBMS utama. Penundaan ini seharusnya tidak mengejutkan: dalam teori relasional, data tidak dipesan. Lebih dari semangat teori relasional berhubungan dengan solusi melalui subquery.

Subquery seperti itu harus mempertimbangkan jumlah nilai dengan tanggal sebelum saat ini (dan termasuk saat ini): $$$dt_ {baris} \ leq dt_ {baris saat ini}$.

Apa dalam kode terlihat seperti ini:

 select s.*, (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt <= s.dt) as total from test_simple s order by s.dt; 

Solusi yang sedikit lebih efisien adalah di mana subquery mempertimbangkan total hingga tanggal saat ini (tetapi tidak termasuk itu), dan kemudian merangkumnya dengan nilai di baris:

 select s.*, s.val + (select coalesce(sum(t2.val), 0) from test_simple t2 where t2.dt < s.dt) as total from test_simple s order by s.dt; 

Dalam kasus hasil kumulatif untuk beberapa kelompok, kita perlu menggunakan subquery yang berkorelasi:

 select g.*, (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt) as total from test_groups g order by g.grp, g.dt; 

Kondisi g.grp = t2.grp memeriksa baris untuk dimasukkan dalam grup (yang, pada prinsipnya, mirip dengan pekerjaan partition by grp di fungsi jendela).

3. Koneksi internal

Karena subkueri dan gabungan dapat dipertukarkan, kita dapat dengan mudah mengganti satu dengan yang lain. Untuk melakukan ini, Anda harus menggunakan Self Join, menghubungkan dua contoh dari tabel yang sama:

 select s.*, coalesce(sum(t2.val), 0) as total from test_simple s inner join test_simple t2 on t2.dt <= s.dt group by s.dt, s.val order by s.dt; 

Seperti yang Anda lihat, kondisi pemfilteran dalam subquery t2.dt <= s.dt telah menjadi kondisi gabungan. Selain itu, untuk menggunakan jumlah fungsi agregasi sum() kita perlu mengelompokkan berdasarkan tanggal dan nilai berdasarkan group by s.dt, s.val .

Demikian pula, Anda dapat melakukannya untuk grp grup grp berbeda:

 select g.*, coalesce(sum(t2.val), 0) as total from test_groups g inner join test_groups t2 on g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt; 

4. produk Cartesian

Karena kami mengganti subquery dengan join, mengapa tidak mencoba produk Cartesian? Solusi ini hanya akan membutuhkan suntingan minimal:

 select s.*, coalesce(sum(t2.val), 0) as total from test_simple s, test_simple t2 where t2.dt <= s.dt group by s.dt, s.val order by s.dt; 

Atau untuk kasus grup:

 select g.*, coalesce(sum(t2.val), 0) as total from test_groups g, test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt group by g.grp, g.dt, g.val order by g.grp, g.dt; 

Solusi yang terdaftar (subquery, inner join, cartesian join) berhubungan dengan SQL-92 dan SQL: 1999 , dan karenanya akan tersedia di hampir semua DBMS. Masalah utama dengan semua solusi ini adalah kinerja yang buruk. Ini bukan masalah besar jika kita mewujudkan sebuah tabel dengan hasilnya (tetapi Anda masih ingin lebih cepat!). Metode lebih lanjut jauh lebih efektif (disesuaikan untuk spesifikasi DBMS spesifik dan versinya sudah ditentukan, ukuran tabel, indeks).

5. Permintaan rekursif

Salah satu pendekatan yang lebih spesifik adalah kueri rekursif dalam ekspresi tabel umum. Untuk melakukan ini, kita memerlukan "jangkar" - kueri yang mengembalikan baris pertama:

 select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple) 

Kemudian, dengan bantuan union all , hasil kueri rekursif ditambahkan ke "jangkar". Untuk melakukan ini, Anda dapat mengandalkan bidang tanggal dt , menambahkan satu hari ke dalamnya:

 select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) -- + 1   SQL Server 

Bagian dari kode yang menambahkan satu hari tidak universal. Sebagai contoh, ini adalah r.dt = dateadd(day, 1, cte.dt) untuk SQL Server, r.dt = cte.dt + 1 untuk Oracle, dll.

Menggabungkan "jangkar" dan permintaan utama, kami mendapatkan hasil akhir:

 with cte (dt, val, total) as (select dt, val, val as total from test_simple where dt = (select min(dt) from test_simple) union all select r.dt, r.val, cte.total + r.val from cte inner join test_simple r on r.dt = dateadd(day, 1, cte.dt) -- r.dt = cte.dt + 1  Oracle,  .. ) select dt, val, total from cte order by dt; 

Solusi untuk kasus dengan kelompok tidak akan jauh lebih rumit:

 with cte (dt, grp, val, total) as (select g.dt, g.grp, g.val, g.val as total from test_groups g where g.dt = (select min(dt) from test_groups where grp = g.grp) union all select r.dt, r.grp, r.val, cte.total + r.val from cte inner join test_groups r on r.dt = dateadd(day, 1, cte.dt) -- r.dt = cte.dt + 1  Oracle,  .. and cte.grp = r.grp ) select dt, grp, val, total from cte order by grp, dt; 

6. Permintaan rekursif dengan fungsi row_number()

Keputusan sebelumnya didasarkan pada kontinuitas bidang tanggal dt dengan kenaikan berurutan 1 hari. Kami menghindari ini dengan menggunakan fungsi jendela row_number() , yang memberi nomor pada baris. Tentu saja, ini tidak adil - karena kita akan mempertimbangkan alternatif untuk fungsi jendela. Namun, solusi ini mungkin semacam pembuktian konsep : dalam praktiknya, mungkin ada bidang yang menggantikan nomor baris (record id). Selain itu, di SQL Server, fungsi row_number() muncul sebelum dukungan penuh untuk fungsi jendela diperkenalkan (termasuk sum() ).

Jadi, untuk permintaan rekursif dengan row_number() kita membutuhkan dua STE. Yang pertama, kita hanya memberi nomor pada baris:

 with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple) 

... dan jika nomor baris sudah ada dalam tabel, maka Anda dapat melakukannya tanpa itu. Dalam kueri berikut, kami sudah cte1 ke cte1 :

 cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 ) 

Dan seluruh permintaan terlihat seperti ini:

 with cte1 (dt, val, rn) as (select dt, val, row_number() over (order by dt) as rn from test_simple), cte2 (dt, val, rn, total) as (select dt, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.rn = cte2.rn + 1 ) select dt, val, total from cte2 order by dt; 

... atau untuk kasus grup:

 with cte1 (dt, grp, val, rn) as (select dt, grp, val, row_number() over (partition by grp order by dt) as rn from test_groups), cte2 (dt, grp, val, rn, total) as (select dt, grp, val, rn, val as total from cte1 where rn = 1 union all select cte1.dt, cte1.grp, cte1.val, cte1.rn, cte2.total + cte1.val from cte2 inner join cte1 on cte1.grp = cte2.grp and cte1.rn = cte2.rn + 1 ) select dt, grp, val, total from cte2 order by grp, dt; 

7. CROSS APPLY / LATERAL

Salah satu cara paling eksotis menghitung total yang berjalan adalah dengan menggunakan pernyataan CROSS APPLY (SQL Server, Oracle) atau LATERAL setara (MySQL, PostgreSQL). Operator-operator ini muncul agak terlambat (misalnya, di Oracle hanya dari versi 12c). Dan di beberapa DBMS (misalnya, MariaDB ) mereka tidak sama sekali. Oleh karena itu, keputusan ini murni untuk kepentingan estetika.

Secara fungsional, menggunakan CROSS APPLY atau LATERAL identik dengan subquery: kami melampirkan hasil perhitungan ke permintaan utama:

 cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2 

... yang terlihat seperti ini:

 select s.*, t2.total from test_simple s cross apply (select coalesce(sum(t2.val), 0) as total from test_simple t2 where t2.dt <= s.dt ) t2 order by s.dt; 

Solusi untuk kasus dengan kelompok akan serupa:

 select g.*, t2.total from test_groups g cross apply (select coalesce(sum(t2.val), 0) as total from test_groups t2 where g.grp = t2.grp and t2.dt <= g.dt ) t2 order by g.grp, g.dt; 

Total: kami memeriksa solusi platform-independen utama. Tetapi ada solusi khusus untuk DBMS spesifik! Karena ada banyak opsi di sini, mari kita memikirkan beberapa yang paling menarik.

8. Pernyataan MODEL (Oracle)

Pernyataan MODEL di Oracle memberikan salah satu solusi paling elegan. Di awal artikel, kami memeriksa rumus umum dari total kumulatif:

$$

$$\ begin {cases} Total_1 = Value_1, n = 1 \\ Total_n = Total_ {n-1} + Value_n, n \ geq 2 \ end {cases}$$

MODEL memungkinkan Anda untuk mengimplementasikan formula ini secara harfiah satu lawan satu! Untuk melakukan ini, pertama-tama kita mengisi bidang total dengan nilai-nilai dari baris saat ini

 select dt, val, val as total from test_simple 

... lalu kita menghitung nomor baris sebagai row_number() over (order by dt) as rn (atau menggunakan bidang yang sudah jadi dengan nomor tersebut, jika ada). Dan akhirnya, kami memperkenalkan aturan untuk semua baris kecuali yang pertama: total[rn >= 2] = total[cv() - 1] + val[cv()] .

Fungsi cv() sini bertanggung jawab atas nilai baris saat ini. Dan seluruh permintaan akan terlihat seperti ini:

 select dt, val, total from (select dt, val, val as total from test_simple) t model dimension by (row_number() over (order by dt) as rn) measures (dt, val, total) rules (total[rn >= 2] = total[cv() - 1] + val[cv()]) order by dt; 

9. Kursor (SQL Server)

Total berjalan adalah salah satu dari beberapa kasus di mana kursor di SQL Server tidak hanya berguna, tetapi juga lebih disukai daripada solusi lain (setidaknya sampai versi 2012, di mana fungsi jendela muncul).

Implementasi melalui kursor cukup sepele. Pertama, Anda perlu membuat tabel sementara dan mengisinya dengan tanggal dan nilai dari utama:

 create table #temp (dt date primary key, val int null, total int null ); insert #temp (dt, val) select dt, val from test_simple order by dt; 

Kemudian kami mengatur variabel lokal di mana pembaruan akan berlangsung:

 declare @VarTotal int, @VarDT date, @VarVal int; set @VarTotal = 0; 

Setelah itu kami memperbarui tabel sementara melalui kursor:

 declare cur cursor local static read_only forward_only for select dt, val from #temp order by dt; open cur; fetch cur into @VarDT, @VarVal; while @@fetch_status = 0 begin set @VarTotal = @VarTotal + @VarVal; update #temp set total = @VarTotal where dt = @VarDT; fetch cur into @VarDT, @VarVal; end; close cur; deallocate cur; 

Dan akhirnya, kami mendapatkan hasil yang diinginkan:

 select dt, val, total from #temp order by dt; drop table #temp; 

10. Perbarui melalui variabel lokal (SQL Server)

Memperbarui melalui variabel lokal di SQL Server didasarkan pada perilaku tidak berdokumen, sehingga tidak dapat dianggap andal. Meskipun demikian, ini mungkin solusi tercepat, dan ini menarik.

Mari kita membuat dua variabel: satu untuk total kumulatif dan variabel tabel:

 declare @VarTotal int = 0; declare @tv table (dt date null, val int null, total int null ); 

Pertama, isi @tv data dari tabel utama

 insert @tv (dt, val, total) select dt, val, 0 as total from test_simple order by dt; 

Kemudian @tv memperbarui variabel tabel @tv menggunakan @VarTotal :

 update @tv set @VarTotal = total = @VarTotal + val from @tv; 

... setelah itu kita mendapatkan hasil akhir:

 select * from @tv order by dt; 

Ringkasan: Kami meninjau 10 cara teratas untuk menghitung total kumulatif dalam SQL. Seperti yang Anda lihat, bahkan tanpa fungsi jendela, masalah ini sepenuhnya dapat dipecahkan, dan mekanisme solusi tidak dapat disebut rumit.