Halo, Radio SQL kembali mengudara! Hari ini kami memiliki solusi untuk masalah yang kami transmisikan pada siaran sebelumnya, dan berjanji untuk keluar nanti. Dan ini waktu berikutnya telah datang.
Tugas itu membangkitkan respons yang hidup di antara humanoids galaksi Bima Sakti (dan tidak mengherankan, dengan perbudakan tenaga kerja mereka, yang masih mereka hormati demi manfaat peradaban). Sayangnya, di planet ketiga, peluncuran observatorium ruang Spectrum-RG ditunda pada akhir Juli 2019, RC (kronologi lokal), dengan bantuan yang direncanakan untuk menyiarkan program ini. Saya harus mencari rute transmisi alternatif, yang menyebabkan sedikit keterlambatan dalam sinyal. Tapi semuanya baik-baik saja, itu berakhir dengan baik.
Saya harus mengatakan segera bahwa tidak akan ada keajaiban dalam analisis tugas, tidak perlu mencari wahyu di sini atau menunggu beberapa implementasi yang sangat efektif (atau terutama beberapa dalam arti lain). Ini hanya tugas parsing. Di dalamnya, mereka yang tidak tahu cara mendekati solusi dari masalah seperti itu akan dapat melihat bagaimana menyelesaikannya. Apalagi, tidak ada yang mengerikan di sini.
Biarkan saya mengingatkan Anda kondisinya.Ada beberapa interval waktu yang ditentukan oleh tanggal-waktu awal dan akhir (contoh dalam sintaks PostgreSQL):
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp), (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp), (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:07:12'::timestamp), (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) )
Diperlukan dalam satu query SQL (c) untuk menghitung durasi setiap interval dalam jam kerja. Kami percaya bahwa kami bekerja pada hari kerja dari Senin hingga Jumat, jam kerja selalu dari 10:00 hingga 19:00. Selain itu, sesuai dengan kalender produksi Federasi Rusia, ada sejumlah hari libur resmi yang bukan hari kerja, dan beberapa hari libur, sebaliknya, adalah hari kerja karena penundaan hari libur yang sama. Pemendekan hari pra-liburan tidak diperlukan, kami menganggapnya lengkap. Karena liburan bervariasi dari tahun ke tahun, yaitu, ditetapkan oleh daftar eksplisit, kami akan membatasi diri hanya untuk tanggal dari 2018 dan 2019. Saya yakin, jika perlu, solusinya dapat dengan mudah ditambahkan.
Penting untuk menambahkan satu kolom dengan durasi dalam jam kerja ke periode awal dari periode . Inilah hasilnya:
id | start_time | stop_time | work_hrs
Kami tidak memeriksa data awal untuk kebenaran, kami selalu mempertimbangkan start_time <= stop_time .
Akhir dari kondisi, yang asli ada di sini: https://habr.com/en/company/postgrespro/blog/448368/ .
Tugas ini memberikan sedikit perhatian pada kenyataan bahwa saya secara sadar telah memberikan setengah kondisi yang baik dalam bentuk deskriptif (seperti yang biasanya terjadi dalam kehidupan nyata), meninggalkan pada kebijaksanaan implementasi teknis bagaimana jadwal harus ditetapkan. Di satu sisi, ini memerlukan beberapa keterampilan berpikir arsitektur. Dan di sisi lain, format yang sudah jadi dari jadwal ini akan mendorong beberapa penggunaan template dari jadwal ini. Dan jika Anda menghilangkan, maka pikiran dan fantasi akan bekerja lebih penuh. Penerimaan terbayar sepenuhnya, memungkinkan saya juga menemukan pendekatan menarik dalam solusi yang dipublikasikan.
Jadi, untuk menyelesaikan masalah asli dengan cara ini, dua subtugas perlu dipecahkan:
- Tentukan cara paling padat mengatur jadwal kerja, dan bahkan agar nyaman digunakan untuk solusi.
- Sebenarnya menghitung durasi setiap periode sumber dalam jam kerja sesuai dengan jadwal kerja dari subtugas sebelumnya.
Dan lebih baik memulai dengan yang kedua, untuk memahami dalam bentuk apa kita perlu menyelesaikan yang pertama. Kemudian selesaikan yang pertama dan kembali lagi ke yang kedua untuk mendapatkan hasil akhir.
Kami akan mengumpulkan hasilnya secara bertahap, menggunakan sintaksis CTE, yang memungkinkan kami untuk menempatkan semua sampel data yang diperlukan ke dalam subquery bernama terpisah, dan kemudian menautkan semuanya.
Baiklah, ayo pergi.
Hitung durasi dalam jam kerja
Untuk menghitung durasi masing-masing periode dalam jam kerja di dahi, Anda harus melewati periode awal (warna hijau pada diagram) dengan interval yang menggambarkan waktu kerja (oranye). Interval jam kerja adalah Senin 10: 00-19: 00, Selasa 10: 00-19: 00 dan seterusnya. Hasilnya ditunjukkan dengan warna biru:
Ngomong-ngomong, agar tidak bingung, saya akan terus menyebut periode awal sebagai periode awal, dan saya akan memanggil interval jam kerja.
Prosedur harus diulang untuk setiap periode awal. Periode awal untuk kita sudah diatur di tab periode (start_time, stop_time) , kami akan mewakili jam kerja dalam bentuk tabel, katakanlah, jadwal (strat_time, stop_time) , di mana setiap hari kerja hadir. Hasilnya adalah produk Cartesian lengkap dari semua periode awal dan interval waktu kerja.
Persimpangan dapat dihitung dengan cara klasik, setelah mempertimbangkan semua opsi yang memungkinkan untuk memotong interval - kami memotong hijau dengan oranye, hasilnya biru:
dan mengambil dalam setiap kasus nilai yang diinginkan untuk awal dan akhir hasil:
select s.start_time, s.stop_time
Karena untuk setiap persimpangan, kita hanya dapat memiliki satu dari empat opsi, semuanya digabungkan menjadi satu permintaan menggunakan gabungan semua .
Anda dapat melakukan sebaliknya dengan menggunakan tipe rentang tsrange yang tersedia di PostgreSQL dan operasi persimpangan sudah tersedia untuk itu:
select tsrange(s.start_time, s.stop_time) * tsrange(s.start_time, s.stop_time) from periods p, schedule s
Setuju bahwa jadi - uh - sedikit lebih mudah. Secara umum, ada banyak hal-hal kecil yang nyaman di PostgreSQL, jadi menulis pertanyaan tentang itu sangat bagus.
Hasilkan kalender
Sekarang kembali ke sub-tugas dengan jadwal jam kerja.
Kita perlu mendapatkan jadwal kerja dalam bentuk interval waktu kerja dari 10:00 hingga 19:00 untuk setiap hari kerja, seperti jadwal (start_time, stop_time) . Seperti yang kita pahami, akan lebih mudah untuk menyelesaikan masalah kita. Dalam kehidupan nyata, jadwal seperti itu harus ditetapkan, untuk dua tahun hanya sekitar 500 catatan, untuk tujuan praktis akan diperlukan untuk mengatur bahkan sepuluh tahun - ini adalah beberapa setengah ribu catatan, sampah nyata untuk database modern. Tetapi kami memiliki masalah yang akan diselesaikan dalam satu permintaan, dan mendaftar seluruh tabel di dalamnya tidak terlalu praktis. Mari kita coba implementasikan dengan lebih kompak.
Bagaimanapun, kami membutuhkan hari libur untuk menghapusnya dari jadwal dasar, dan di sini hanya daftar yang cocok:
dates_exclude(d) as ( values('2018-01-01'::date),
dan hari kerja tambahan yang akan ditambahkan:
dates_include(d) as ( values
Urutan hari kerja selama dua tahun dapat dihasilkan oleh fungsi generate_series () yang khusus dan sangat cocok, segera melemparkan hari Sabtu dan Minggu di sepanjang jalan:
select d from generate_series( '2018-01-01'::timestamp , '2020-01-01'::timestamp , '1 day'::interval ) as d where extract(dow from d) not in (0,6)
Kami mendapatkan hari kerja dengan menghubungkan semuanya: kami menghasilkan urutan semua hari kerja dalam dua tahun, menambahkan hari kerja tambahan dari tanggal_sertakan dan menghapus semua hari tambahan dari tanggal_sertakan :
schedule_base as ( select d from generate_series( '2018-01-01'::timestamp , '2020-01-01'::timestamp , '1 day'::interval ) as d where extract(dow from d) not in (0,6)
Dan sekarang kita mendapatkan interval waktu yang kita butuhkan:
schedule(start_time, stop_time) as ( select d + '10:00:00'::time, d + '19:00:00'::time from schedule_base )
Jadi, kami mendapat jadwalnya.
Menyatukan semuanya
Sekarang kita akan mendapatkan persimpangan:
select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p join schedule s on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time)
Perhatikan kondisi bergabung ON , itu tidak cocok dengan dua catatan yang sesuai dari tabel bergabung, tidak ada korespondensi seperti itu, tetapi beberapa optimasi diperkenalkan yang memotong interval waktu kerja dimana periode awal kami tidak berpotongan. Ini dilakukan menggunakan operator && , yang memeriksa persimpangan interval tsrange . Ini menghilangkan banyak persimpangan kosong agar tidak menghalangi mata, tetapi, di sisi lain, menghilangkan informasi tentang periode-periode awal yang jatuh seluruhnya dari jam kerja. Jadi kami mengagumi bahwa pendekatan kami berhasil, dan menulis ulang permintaan sebagai berikut:
periods_wrk as ( select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p , schedule s ) select id, start_time, stop_time , sum(upper(wrkh)-lower(wrkh)) from periods_wrk group by id, start_time, stop_time
Dalam period_wrk kami mendekomposisi setiap periode sumber ke dalam interval kerja, dan kemudian kami mempertimbangkan durasi totalnya. Hasilnya adalah produk Cartesian lengkap dari semua periode dan interval, tetapi tidak satu periode pun hilang.
Semuanya, hasilnya diterima. Saya tidak suka nilai NULL untuk interval kosong, biarkan kueri menunjukkan interval panjang nol lebih baik. Bungkus jumlahnya dalam gabungan () :
select id, start_time, stop_time , coalesce(sum(upper(wrkh)-lower(wrkh)), '0 sec'::interval) from periods_wrk group by id, start_time, stop_time
Semuanya memberikan hasil akhir:
with periods(id, start_time, stop_time) as ( values(1, '2019-03-29 07:00:00'::timestamp, '2019-04-08 14:00:00'::timestamp) , (2, '2019-04-10 07:00:00'::timestamp, '2019-04-10 20:00:00'::timestamp) , (3, '2019-04-11 12:00:00'::timestamp, '2019-04-12 16:00:00'::timestamp) , (4, '2018-12-28 12:00:00'::timestamp, '2019-01-16 16:00:00'::timestamp) ), dates_exclude(d) as ( values('2018-01-01'::date),
Hore! .. Ini bisa selesai, tetapi demi kelengkapan kami akan mempertimbangkan beberapa topik yang lebih terkait.
Pengembangan topik selanjutnya
Hari pra-liburan singkat, istirahat makan siang, jadwal berbeda untuk hari yang berbeda dalam seminggu ... Pada prinsipnya, semuanya jelas, Anda perlu memperbaiki definisi jadwal , cukup berikan beberapa contoh.
Ini adalah bagaimana Anda dapat mengatur waktu mulai dan berakhir yang berbeda untuk hari kerja, tergantung pada hari dalam seminggu:
select d + case extract(dow from d) when 1 then '10:00:00'::time
Jika Anda perlu mempertimbangkan istirahat makan siang dari pukul 13: 00-14: 00, maka alih-alih satu interval per hari, lakukan dua:
select d + '10:00:00'::time , d + '13:00:00'::time from schedule_base union all select d + '14:00:00'::time , d + '19:00:00'::time from schedule_base
Baik dan sebagainya.
Performa
Saya akan mengatakan beberapa kata tentang kinerja, karena selalu ada pertanyaan tentangnya. Saya tidak akan mengunyah banyak, ini adalah bagian dengan tanda bintang.
Secara umum, optimasi prematur adalah kejahatan. Menurut pengamatan saya selama bertahun-tahun, keterbacaan kode adalah keuntungan terpentingnya. Jika kode dibaca dengan baik, maka lebih mudah untuk memelihara dan mengembangkannya. Kode yang dapat dibaca dengan baik secara implisit membutuhkan arsitektur solusi yang baik, komentar yang tepat, dan nama variabel yang baik, kekompakan tanpa mengorbankan keterbacaan, dll. Yaitu, semua kode itu disebut baik untuk.
Oleh karena itu, permintaan selalu ditulis sebagai dapat dibaca, dan kami mulai mengoptimalkan jika dan hanya jika ternyata kinerjanya tidak mencukupi. Selain itu, kami akan mengoptimalkannya secara tepat di mana kinerja tidak mencukupi dan tepat sejauh itu menjadi cukup. Jika Anda menghargai waktu Anda sendiri, dan Anda memiliki sesuatu untuk dilakukan.
Tetapi tidak melakukan pekerjaan yang tidak perlu dalam permintaan itu benar, Anda harus selalu mencoba untuk mempertimbangkan ini.
Berdasarkan hal ini, kami akan segera menyertakan satu optimasi dalam kueri - biarkan setiap periode sumber hanya bersinggungan dengan interval waktu kerja yang memiliki poin umum (alih-alih kondisi klasik panjang pada batas rentang, lebih mudah menggunakan operator built & in untuk tipe tsrange ). Optimalisasi ini sudah muncul dalam permintaan, tetapi telah menyebabkan fakta bahwa periode awal yang benar-benar keluar dari jam kerja hilang dari hasil.
Bawa kembali pengoptimalan ini. Untuk melakukan ini, gunakan LEFT JOIN , yang akan menyimpan semua catatan dari tabel periode . Sekarang subquery period_wrk akan terlihat seperti ini:
, periods_wrk as ( select p.* , tsrange(p.start_time, p.stop_time) * tsrange(s.start_time, s.stop_time) as wrkh from periods p left join schedule s on tsrange(p.start_time, p.stop_time) && tsrange(s.start_time, s.stop_time))
Analisis permintaan menunjukkan bahwa waktu pada data uji telah berkurang sekitar setengahnya. Karena runtime tergantung pada apa yang dilakukan server pada saat yang sama, saya mengambil beberapa pengukuran dan memberikan beberapa hasil "khas", bukan yang terbesar, bukan yang terkecil, dari tengah.
Permintaan lama:
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
Baru:
explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN
Tetapi yang paling penting adalah bahwa permintaan semacam itu juga akan skala lebih baik, membutuhkan lebih sedikit sumber daya server, karena produk Cartesian penuh tumbuh sangat cepat.
Dan dalam hal ini saya akan berhenti dengan optimasi. Ketika saya memecahkan masalah ini untuk diri saya sendiri, saya memiliki kinerja yang cukup bahkan dalam bentuk yang jauh lebih buruk dari permintaan ini, tetapi sebenarnya tidak perlu untuk mengoptimalkan. Untuk mendapatkan laporan data saya sekali dalam seperempat, saya bisa menunggu sepuluh detik ekstra. Jam tambahan yang dihabiskan untuk optimasi dalam kondisi seperti itu tidak akan pernah berhasil.
Tetapi ternyata tidak menarik, mari kita masih berpikir tentang bagaimana peristiwa dapat berkembang jika optimasi dalam hal waktu eksekusi benar-benar diperlukan. Misalnya, kami ingin memantau parameter ini secara real time untuk setiap catatan kami di database, yaitu, untuk setiap bersin permintaan seperti itu akan dipanggil. Ya, atau buat alasan Anda sendiri, mengapa Anda perlu mengoptimalkan.
Hal pertama yang terlintas dalam pikiran adalah menghitung satu kali dan menempatkan tabel dalam tabel dengan interval kerja. Mungkin ada kontraindikasi: jika database tidak dapat diubah, atau kesulitan diharapkan dengan dukungan data yang relevan dalam tabel seperti itu. Maka Anda harus meninggalkan generasi waktu kerja "on the fly" dalam permintaan itu sendiri, karena ini bukan subquery yang sangat berat.
Pendekatan berikutnya dan yang paling kuat (tetapi tidak selalu berlaku) adalah optimasi algoritmik. Beberapa pendekatan ini telah disajikan dalam komentar pada artikel dengan kondisi masalah.
Saya paling suka yang ini. Jika Anda membuat tabel dengan semua (tidak hanya berfungsi) kalender dan menghitung total kumulatif berapa jam kerja setiap hari dari "penciptaan dunia" tertentu telah berlalu, maka Anda bisa mendapatkan jumlah jam kerja antara dua tanggal dengan satu operasi pengurangan. Tinggal menghitung jam kerja dengan benar untuk hari pertama dan terakhir - dan Anda selesai. Inilah yang saya dapatkan dalam pendekatan ini:
schedule_base(d, is_working) as ( select '2018-01-01'::date, 0 union all select d+1, case when extract(dow from d+1) not in (0,6) and d+1 <> all('{2019-01-01,2019-01-02,2019-01-03,2019-01-04,2019-01-07,2019-01-08,2019-03-08,2019-05-01,2019-05-02,2019-05-03,2019-05-09,2019-05-10,2019-06-12,2019-11-04,2018-01-01,2018-01-02,2018-01-03,2018-01-04,2018-01-05,2018-01-08,2018-02-23,2018-03-08,2018-03-09,2018-04-30,2018-05-01,2018-05-02,2018-05-09,2018-06-11,2018-06-12,2018-11-05,2018-12-31}') or d+1 = any('{2018-04-28,2018-06-09,2018-12-29}') then 1 else 0 end from schedule_base where d < '2020-01-01' ), schedule(d, is_working, work_hours) as ( select d, is_working , sum(is_working*'9 hours'::interval) over (order by d range between unbounded preceding and current row) from schedule_base ) select p.* , s2.work_hours - s1.work_hours + ('19:00:00'::time - least(greatest(p.start_time::time, '10:00:00'::time), '19:00:00'::time)) * s1.is_working - ('19:00:00'::time - least(greatest(p.stop_time::time, '10:00:00'::time), '19:00:00'::time)) * s2.is_working as wrk from periods p, schedule s1, schedule s2 where s1.d = p.start_time::date and s2.d = p.stop_time::date
Saya akan menjelaskan secara singkat apa yang terjadi di sini. Dalam subquery schedule_base , kami membuat semua hari kalender selama dua tahun dan setiap hari kami menentukan tanda apakah hari kerja (= 1) atau tidak (= 0). Lebih jauh, dalam subquery jadwal , kami menganggap fungsi jendela sebagai jumlah total jam kerja kumulatif dari 2018-01-01. Adalah mungkin untuk melakukan semuanya dalam satu subquery, tetapi akan menjadi lebih rumit, yang akan mengganggu keterbacaan. Kemudian, dalam permintaan utama, kami mempertimbangkan perbedaan antara jumlah jam kerja pada akhir dan awal periode dan, agak terlalu lambat, memperhitungkan jam kerja untuk hari pertama dan terakhir periode tersebut. Floriditas dikaitkan dengan pengalihan waktu sebelum awal hari kerja ke awal, dan waktu setelah akhir hari kerja hingga akhir. Selain itu, jika bagian dari permintaan dengan shedule_base dan jadwal dihapus ke dalam tabel pra-perhitungan yang terpisah (seperti yang disarankan sebelumnya), permintaan tersebut akan berubah menjadi sangat sepele.
Mari kita bandingkan eksekusi pada sampel yang lebih besar untuk menunjukkan lebih baik optimasi dilakukan, untuk empat periode dari kondisi tugas lebih banyak waktu dihabiskan untuk menghasilkan jadwal kerja.
Saya mengambil sekitar 3 ribu periode. Saya hanya akan memberikan baris ringkasan teratas dalam EXPLAIN, nilai-nilai tipikal adalah sebagai berikut.
Opsi asli:
GroupAggregate (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1) ...
Dioptimalkan:
Hash Join (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1) ...
Keuntungan waktu adalah beberapa perintah yang besarnya. Dengan peningkatan jumlah periode dan lamanya dalam beberapa tahun, kesenjangan hanya akan melebar.
Segalanya tampak baik-baik saja, tetapi mengapa, setelah melakukan optimasi seperti itu, saya meninggalkan versi pertama dari permintaan itu sendiri sampai kinerjanya cukup? Ya, karena versi yang dioptimalkan tidak diragukan lagi lebih cepat, tetapi membutuhkan lebih banyak waktu untuk memahami cara kerjanya, yaitu keterbacaan semakin memburuk. Artinya, saat berikutnya saya harus menulis ulang permintaan dalam kondisi saya yang berubah, saya (atau tidak saya) harus menghabiskan lebih banyak waktu untuk memahami bagaimana permintaan itu bekerja.
Itu saja untuk hari ini, jaga agar tentakelnya tetap hangat, dan saya ucapkan selamat tinggal kepada Anda sampai Radio SQL rilis berikutnya.