Persediaan Temu Duga Pengaturcaraan · Pelajaran

Jumlah Terkumpul Dengan Bingkai Tetingkap

Bina jumlah berjalan menggunakan SUM OVER dengan bingkai tersusun

Pelajaran 1 daripada 413 langkah

Jumlah Terkumpul Dengan Bingkai Tetingkap ialah pelajaran Persediaan Temu Duga Pengaturcaraan percuma di CoddyKit. Ini ialah pelajaran 1 daripada 4. Anda boleh membaca keseluruhan pelajaran di bawah secara percuma — kemudian berlatih secara praktikal dalam pelayar menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7. Pelajaran ini merupakan sebahagian daripada laluan pembelajaran Persediaan Temu Duga Pengaturcaraan, dan kemajuan anda disegerakkan merentas web serta aplikasi CoddyKit. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Soalan Jumlah Terkumpul

Hampir setiap temu duga penganalisis merangkumi soalan seperti: "Tunjukkan pendapatan terkumpul mengikut masa." Jumlah terkumpul ialah jumlah yang bertambah baris demi baris, mengumpulkan segala-galanya dari awal hingga baris semasa.

Sebelum fungsi tetingkap wujud, calon menyelesaikannya dengan cantuman kendiri yang perlahan atau subkueri berkorelasi. Jawapan moden yang dijangka ialah SUM(...) OVER (ORDER BY ...). Mengetahui versi berasaskan bingkai menunjukkan bahawa anda memahami SQL yang ditulis selepas sekitar tahun 2012.

Anatomi Jumlah Tetingkap Bertertib

Jumlah terkumpul hanyalah agregat yang ditukar menjadi fungsi tetingkap. Anda mengekalkan SUM(amount) tetapi menambahkan klausa OVER dengan ORDER BY.

ORDER BY di dalam OVERlah yang menjadikannya terkumpul: ia memberitahu SQL supaya mengumpulkan baris mengikut urutan itu. Tanpa ORDER BY, SUM akan menjumlahkan seluruh partisi bagi setiap baris dan bukannya bertambah sedikit demi sedikit.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Mengapa ORDER BY Menyiratkan Bingkai

Inilah perincian yang suka disoal oleh penemuduga: apabila anda menambahkan ORDER BY pada agregat tetingkap, SQL menggunakan bingkai lalai RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Lalai itu tepat menghasilkan jumlah terkumpul: setiap baris dari awal partisi sehingga dan termasuk baris semasa. Jika anda memahami lalai ini, anda memahami sebab jumlah terkumpul "terus berfungsi".

Menjadikan Bingkai Eksplisit

Anda boleh menulis bingkai secara manual. Kedua-dua kueri ini mengembalikan hasil yang sama, tetapi versi eksplisit menunjukkan kepada penemuduga bahawa anda tahu perkara yang berlaku di sebaliknya.

Menulis ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ialah bentuk eksplisit paling selamat untuk jumlah terkumpul kerana ia mengira baris fizikal dan mengelakkan kejutan pengumpulan nilai daripada RANGE (yang dibincangkan dalam pelajaran seterusnya).

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Contoh Lengkap: Jualan Harian

Bayangkan jualan selama empat hari: Isnin 100, Selasa 50, Rabu 200, Khamis 75. Jumlah terkumpul bertambah dari kiri ke kanan.

Isnin: 100
Selasa: 100 + 50 = 150
Rabu: 150 + 200 = 350
Khamis: 350 + 75 = 425

Baris terakhir sentiasa sama dengan jumlah keseluruhan. Ini ialah semakan kewajaran pantas yang boleh anda sebut dalam temu duga: nilai jumlah terkumpul terakhir mesti sepadan dengan SUM(amount) bagi keseluruhan set.

Menetapkan Semula Setiap Kumpulan dengan PARTITION BY

Soalan sebenar biasanya mahukan jumlah terkumpul bagi setiap pelanggan atau bagi setiap wilayah, bukannya satu jumlah global. Tambahkan PARTITION BY dan pengumpulan bermula semula pada permulaan setiap partisi.

Model mentalnya: PARTITION BY membahagikan baris kepada kumpulan yang berasingan, manakala ORDER BY + bingkai berjalan secara berasingan dalam setiap kumpulan.

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

Perangkap Pemecah Seri

Jika dua baris berkongsi nilai ORDER BY yang sama (dua jualan pada tarikh yang sama), bingkai RANGE lalai menganggapnya sebagai rakan setara dan memberikan jumlah terkumpul yang sama kepada kedua-duanya, termasuk kedua-dua amaun.

Jika anda memerlukan peningkatan baris demi baris yang ketat walaupun apabila berlaku seri, tukar kepada pembingkaian ROWS dan tambahkan pemecah seri unik pada ORDER BY, seperti sale_date, id. Penemuduga sengaja memasukkan tarikh pendua untuk melihat sama ada anda menyedarinya.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Jumlah Terkumpul bagi Kiraan

Logik kumulatif tidak terhad kepada SUM. Mana-mana agregat boleh berfungsi sebagai fungsi tetingkap, jadi anda boleh membina kiraan, purata atau nilai maksimum terkumpul.

Kiraan pesanan terkumpul ialah metrik papan pemuka yang biasa: berapa banyak pesanan yang telah kita terima setakat setiap hari?

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

Cara Lama: Subkueri Berkorelasi

Penemuduga kadangkala meminta anda menyelesaikan jumlah terkumpul tanpa fungsi tetingkap untuk menguji kedalaman pemahaman anda. Penyelesaian klasik sebelum fungsi tetingkap ialah subkueri berkorelasi yang menjumlahkan semula setiap baris terdahulu.

Ia berfungsi tetapi mempunyai kerumitan O(n kuasa dua): bagi setiap baris, ia mengimbas semula jadual. Sebut perkara ini untuk menunjukkan bahawa anda tahu sebab fungsi tetingkap menggantikannya.

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

Penapisan berbanding Hasil Tetingkap

Soalan susulan yang kerap ditanya: "Tunjukkan hanya hari apabila jumlah terkumpul melebihi 1000." Anda tidak boleh meletakkan fungsi tetingkap dalam WHERE kerana bingkai dikira selepas WHERE dijalankan.

Pembetulannya ialah mengira jumlah terkumpul dalam CTE atau subkueri, kemudian menapis kueri luar. Ini ialah peraturan pembungkusan yang sama dan terpakai pada setiap fungsi tetingkap.

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

Perkara untuk Disebut dalam Temu Duga

Apabila menyampaikan jawapan tentang jumlah terkumpul, huraikan perkara berikut untuk memperoleh markah penuh:

SUM OVER (ORDER BY ...) ialah bentuk kumulatif.
Menambahkan ORDER BY mewujudkan bingkai lalai daripada UNBOUNDED PRECEDING hingga CURRENT ROW.
Gunakan PARTITION BY untuk menetapkan semula jumlah bagi setiap kumpulan.
Tambahkan pemecah seri unik dan pembingkaian ROWS untuk mengelakkan perangkap nilai pendua.
Bungkus dalam CTE untuk menapis berdasarkan hasil tersebut.

Semakan Pantas

Uji pemahaman anda tentang bingkai lalai.

Jawab dalam pelajaran interaktif

Ringkasan: Jumlah Terkumpul

Jumlah terkumpul ialah agregat tetingkap bertertib. SUM(amount) OVER (ORDER BY sale_date) mengumpulkan baris dari awal partisi hingga baris semasa, hasil daripada bingkai UNBOUNDED PRECEDING-hingga-CURRENT ROW yang tersirat.

Tetapkan semula jumlah bagi setiap kumpulan dengan PARTITION BY, tambahkan pemecah seri serta pembingkaian ROWS untuk mengendalikan nilai isihan pendua, dan bungkus dalam CTE setiap kali anda perlu menapis berdasarkan nilai kumulatif. Seterusnya, kita menghuraikan perbezaan ROWS berbanding RANGE yang disentuh dalam pelajaran ini.

Percuma untuk bermula

Pelajari Persediaan Temu Duga Pengaturcaraan dengan tutor kecerdasan buatan — percuma

Tulis dan jalankan kod sebenar dalam pelayar anda, dapatkan bantuan segera daripada tutor kecerdasan buatan yang tersedia 24/7, dan sambung semula dari tempat anda berhenti di web atau dalam aplikasi.

Kursus: 90
Pelajaran: 360

Mula belajar secara percuma Lihat kursus penuh

Soalan Lazim

Adakah pelajaran “Jumlah Terkumpul Dengan Bingkai Tetingkap” percuma?

Ya — teks penuh “Jumlah Terkumpul Dengan Bingkai Tetingkap” boleh dibaca secara percuma di web ini. Untuk berlatih secara interaktif menggunakan penyunting kod terbina dalam dan tutor kecerdasan buatan 24/7, serta membuka kunci baki kursus Persediaan Temu Duga Pengaturcaraan, tingkat taraf kepada CoddyKit PRO. Kursus Persediaan Temu Duga Pengaturcaraan merangkumi sejumlah 4 pelajaran.

Apakah yang akan saya pelajari dalam “Jumlah Terkumpul Dengan Bingkai Tetingkap”?

Bina jumlah berjalan menggunakan SUM OVER dengan bingkai tersusun Anda berlatih Persediaan Temu Duga Pengaturcaraan menggunakan kod praktikal yang dijalankan terus dalam pelayar, manakala tutor kecerdasan buatan 24/7 menjawab soalan anda semasa anda mengikuti pelajaran.

Adakah saya memerlukan pengalaman untuk memulakan Persediaan Temu Duga Pengaturcaraan?

Tiada pengalaman terdahulu diperlukan. Pembelajaran Persediaan Temu Duga Pengaturcaraan di CoddyKit disusun untuk pelajar daripada peringkat pemula hingga lanjutan, jadi anda boleh bermula di sini atau dari awal dan belajar mengikut kadar anda sendiri. Ini ialah pelajaran 1 daripada 4.

Berapa lamakah pelajaran “Jumlah Terkumpul Dengan Bingkai Tetingkap” diambil?

Kebanyakan pelajaran CoddyKit mengambil masa kira-kira 5–10 minit. Setiap pelajaran ringkas dan interaktif, jadi anda boleh membuat kemajuan secara berterusan dan menyambung tepat dari tempat anda berhenti di web atau aplikasi.

Bolehkah saya menulis dan menjalankan kod dalam pelajaran Persediaan Temu Duga Pengaturcaraan ini?

Ya. Setiap pelajaran Persediaan Temu Duga Pengaturcaraan menyertakan penyunting kod terbina dalam, jadi anda boleh menulis dan menjalankan kod sebenar terus dalam pelayar serta menerima maklum balas kecerdasan buatan serta-merta — tanpa memerlukan persediaan setempat.

Semua pelajaran dalam kursus ini

Jumlah Terkumpul Dengan Bingkai Tetingkap
Pembingkaian ROWS berbanding RANGE
Purata Bergerak Merentas Tetingkap Gelongsor
Taburan Kumulatif dan Peratusan Jumlah

← Kembali ke Persediaan Temu Duga Pengaturcaraan