SQL: कार्य समय कार्य समाधान

हैलो, रेडियो एसक्यूएल फिर से हवा में है! आज हमारे पास उस समस्या का समाधान है जिसे हमने अपने पिछले प्रसारण पर प्रसारित किया, और अगली बार बाहर करने का वादा किया। और यह अगली बार आया है।

इस कार्य ने मिल्की वे आकाशगंगा के मानवों के बीच एक जीवंत प्रतिक्रिया पैदा की (और आश्चर्यजनक रूप से, उनकी श्रम दासता के साथ, जो वे अभी भी सभ्यता के लाभ के लिए सम्मान करते हैं)। दुर्भाग्य से, तीसरे ग्रह पर, Spektr-RG अंतरिक्ष वेधशाला का प्रक्षेपण जुलाई 2019 के अंत में आरसी (स्थानीय कालक्रम) को स्थगित कर दिया गया था, जिसकी मदद से इस कार्यक्रम को प्रसारित करने की योजना बनाई गई थी। मुझे वैकल्पिक ट्रांसमिशन मार्गों की तलाश करनी थी, जिससे सिग्नल में थोड़ी देरी हुई। लेकिन सब ठीक है जो अच्छी तरह से समाप्त होता है।

मुझे तुरंत कहना होगा कि कार्य के विश्लेषण में कोई जादू नहीं होगा, यहां खुलासे की जरूरत नहीं है या कुछ विशेष रूप से प्रभावी (या विशेष रूप से कुछ किसी अन्य अर्थ में) कार्यान्वयन की प्रतीक्षा करें। यह केवल एक पार्सिंग कार्य है। इसमें, जो लोग नहीं जानते कि ऐसी समस्याओं के समाधान के लिए कैसे संपर्क किया जा सकता है, उन्हें कैसे हल करना है। इसके अलावा, यहाँ कुछ भी भयानक नहीं है।

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) ) 

  id | start_time | stop_time | work_hrs ----+---------------------+---------------------+---------- 1 | 2019-03-29 07:00:00 | 2019-04-08 14:00:00 | 58:00:00 2 | 2019-04-10 07:00:00 | 2019-04-10 20:00:00 | 09:00:00 3 | 2019-04-11 12:00:00 | 2019-04-12 16:07:12 | 13:07:12 4 | 2018-12-28 12:00:00 | 2019-01-16 16:00:00 | 67:00:00 

यह कार्य इस तथ्य को एक मामूली विचित्रता प्रदान करता है कि मैंने जानबूझकर एक वर्णनात्मक रूप में स्थिति का एक अच्छा आधा दिया है (जैसा कि यह आमतौर पर वास्तविक जीवन में होता है), तकनीकी कार्यान्वयन के विवेक को छोड़कर कि अनुसूची कैसे निर्धारित की जानी चाहिए। एक ओर, इसके लिए कुछ वास्तु सोच कौशल की आवश्यकता होती है। और दूसरी ओर, इस अनुसूची के तैयार प्रारूप ने इसके कुछ खाके उपयोग को प्रेरित किया होगा। और यदि आप चूक जाते हैं, तो विचार और कल्पना अधिक पूरी तरह से काम करेंगे। रिसेप्शन पूरी तरह से भुगतान किया गया, जिससे मुझे प्रकाशित समाधानों में भी दिलचस्प दृष्टिकोण मिल सके।

तो, इस तरह से मूल समस्या को हल करने के लिए, दो उप-प्रकारों को हल करना होगा:

1. यह निर्धारित करें कि सबसे अधिक काम का समय कैसे तय किया जाए, और यहां तक ​​कि समाधान के लिए उपयोग करना सुविधाजनक हो।
2. वास्तव में पिछले उपमा से काम अनुसूची के अनुसार काम के घंटे में प्रत्येक स्रोत की अवधि की गणना करें।

और दूसरे के साथ शुरू करना बेहतर है, यह समझने के लिए कि हमें किस रूप में पहले को हल करने की आवश्यकता है। फिर पहले को हल करें और अंतिम परिणाम प्राप्त करने के लिए फिर से दूसरे पर लौटें।
हम सीटीई सिंटैक्स का उपयोग करके, धीरे-धीरे परिणाम एकत्र करेंगे, जो हमें सभी आवश्यक डेटा नमूनों को अलग-अलग उप-वर्गों में डालने की अनुमति देता है, और फिर सब कुछ एक साथ लिंक करता है।

अच्छा, चलिए।

काम के घंटों में अवधि की गणना करें

माथे में काम के घंटों में प्रत्येक अवधि की अवधि की गणना करने के लिए, आपको अंतराल (आरेख पर हरा रंग) को अंतराल के साथ पार करने की आवश्यकता है जो काम के समय (नारंगी) का वर्णन करते हैं। काम के घंटे के अंतराल सोमवार 10:00 से 19:00, मंगलवार 10:00 से 19:00 तक और इतने पर हैं। परिणाम नीले रंग में दिखाया गया है:

वैसे, कम भ्रमित होने के लिए, मैं प्रारंभिक अवधियों को प्रारंभिक अवधियों के रूप में संदर्भित करना जारी रखूंगा, और मैं काम के घंटे के अंतराल को बुलाऊंगा।

प्रक्रिया को प्रत्येक प्रारंभिक अवधि के लिए दोहराया जाना चाहिए। हमारे लिए प्रारंभिक अवधियाँ पहले से ही पीरियड्स टैब (start_time, stop_time) में सेट हैं, हम तालिका के रूप में कार्य घंटों का प्रतिनिधित्व करेंगे, कहते हैं, शेड्यूल (strat_time, stop_time) , जहाँ हर कार्य दिवस मौजूद है। परिणाम काम के समय के सभी प्रारंभिक अवधियों और अंतरालों का एक पूर्ण कार्टेशियन उत्पाद है।

अंतर को शास्त्रीय तरीके से गिना जा सकता है, अंतराल को इंटरसेक्ट करने के सभी संभावित विकल्पों पर विचार करते हुए - हम नारंगी के साथ हरे रंग को काटते हैं, परिणाम नीला है:

और प्रत्येक मामले में परिणाम की शुरुआत और अंत के लिए वांछित मूल्य लेना:

  select s.start_time, s.stop_time -- case #1 from periods p, schedule s where p.start_time <= s.start_time and p.stop_time > s.stop_time union all select p.start_time, s.stop_time -- case #2 from periods p, schedule s where p.start_time >= s.start_time and p.stop_time > s.stop_time and p.start_time < s.stop_time union all select s.start_time, p.stop_time -- case #3 from periods p, schedule s where p.start_time <= s.start_time and p.stop_time < s.stop_time and p.stop_time > s.start_time union all select p.start_time, p.stop_time -- case #4 from periods p, schedule s where p.start_time >= s.start_time and p.stop_time < s.stop_time 

चूँकि प्रत्येक चौराहे के लिए हमारे पास केवल चार विकल्प हो सकते हैं, उन सभी को संघ के सभी का उपयोग करके एक अनुरोध में जोड़ा जाता है।

आप अन्यथा PostgreSQL में उपलब्ध tsrange रेंज प्रकार और इसके लिए पहले से उपलब्ध चौराहे ऑपरेशन का उपयोग करके कर सकते हैं:

  select tsrange(s.start_time, s.stop_time) * tsrange(s.start_time, s.stop_time) from periods p, schedule s 

सहमत हूँ कि इतना - उह - थोड़ा आसान। सामान्य तौर पर, PostgreSQL में ऐसी बहुत सी सुविधाजनक छोटी चीजें हैं, इसलिए इस पर प्रश्न लिखना बहुत अच्छा है।

कैलेंडर जनरेट करें

अब काम के घंटे की अनुसूची के साथ उप-कार्य पर वापस जाएं।

हमें प्रत्येक कार्य दिवस के लिए 10:00 से 19:00 तक कार्य समय अंतराल के रूप में कार्य अनुसूची प्राप्त करने की आवश्यकता है, अनुसूची (start_time, stop_time) की तरह कुछ। जैसा कि हमने समझा, यह हमारी समस्या को हल करने के लिए सुविधाजनक होगा। वास्तविक जीवन में, इस तरह के शेड्यूल को टेबल सेट किया जाना चाहिए, दो साल के लिए यह केवल 500 रिकॉर्ड है, व्यावहारिक उद्देश्यों के लिए यह दस साल भी निर्धारित करना आवश्यक होगा - यह एक और डेढ़ हजार रिकॉर्ड है, आधुनिक डेटाबेस के लिए असली बकवास। लेकिन हमारे पास एक समस्या है जिसे एक अनुरोध में हल किया जाएगा, और इसमें ऐसी पूरी तालिका को सूचीबद्ध करना बहुत व्यावहारिक नहीं है। आइए इसे और अधिक कॉम्पैक्ट रूप से लागू करने का प्रयास करें।

किसी भी मामले में, हमें उन्हें बेस शेड्यूल से हटाने के लिए छुट्टियों की आवश्यकता है, और यहां केवल लिस्टिंग उपयुक्त है:

  dates_exclude(d) as ( values('2018-01-01'::date), -- 2018 ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date), ('2019-01-01'::date), -- 2019 ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date) ) 

और अतिरिक्त व्यावसायिक दिनों को जोड़ा जाएगा:

  dates_include(d) as ( values --  2018,  2019  ('2018-04-28'::date), ('2018-06-09'::date), ('2018-12-29'::date) ) 

दो साल के लिए कार्य दिवसों का क्रम एक विशेष और बहुत ही उपयुक्त जनरेट__रे () फ़ंक्शन द्वारा उत्पन्न किया जा सकता है, जिस तरह से शनिवार और रविवार को तुरंत फेंक दिया जाता है:

  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) --     

हम सब कुछ एक साथ जोड़कर कार्य दिवस प्राप्त करते हैं: हम दो वर्षों में सभी कार्य दिवसों का एक क्रम उत्पन्न करते हैं, date_include से अतिरिक्त कार्य दिवस जोड़ते हैं और date_exclude से सभी अतिरिक्त दिनों को निकालते हैं :

  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) --     union select d from dates_include --     except select d from dates_exclude --     ) 

और अब हमें समय अंतराल की आवश्यकता है:

  schedule(start_time, stop_time) as ( select d + '10:00:00'::time, d + '19:00:00'::time from schedule_base ) 

इसलिए, हमें शेड्यूल मिला।

यह सब एक साथ रखना

अब हमें चौराहे मिलेंगे:

  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) 

कनेक्शन स्थिति पर ध्यान दें, यह सम्मिलित तालिकाओं से दो संबंधित रिकॉर्ड से मेल नहीं खाता है, ऐसा पत्राचार मौजूद नहीं है, लेकिन कुछ अनुकूलन पेश किए गए हैं जो काम के समय के अंतराल को काटते हैं, जिसके साथ हमारी प्रारंभिक अवधि अंतर नहीं करती है। यह && ऑपरेटर का उपयोग करके किया जाता है, जो tsrange अंतराल के चौराहे की जांच करता है। यह बहुत सारे खाली चौराहों को हटाता है ताकि आंखों के रास्ते में न आए, लेकिन, दूसरी ओर, उन शुरुआती अवधियों के बारे में जानकारी को हटा देता है जो पूरी तरह से बंद हो जाते हैं। इसलिए हम स्वीकार करते हैं कि हमारा दृष्टिकोण काम करता है, और अनुरोध को इस प्रकार लिखता है:

  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 

Period_wrk में हम प्रत्येक स्रोत अवधि को कार्य अंतराल में विघटित करते हैं, और फिर हम उनकी कुल अवधि पर विचार करते हैं। परिणाम सभी अवधि और अंतराल का एक पूर्ण कार्टेशियन उत्पाद था, लेकिन एक भी अवधि नहीं खोई गई थी।

सब कुछ, परिणाम प्राप्त होता है। मैं खाली अंतराल के लिए NULL मानों की तरह नहीं था, क्वेरी को शून्य-लंबाई अंतराल को बेहतर दिखाने दें। मात्रा में लपेटें () :

 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 

सभी मिलकर अंतिम परिणाम देते हैं:

 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), -- 2018 ('2018-01-02'::date), ('2018-01-03'::date), ('2018-01-04'::date), ('2018-01-05'::date), ('2018-01-08'::date), ('2018-02-23'::date), ('2018-03-08'::date), ('2018-03-09'::date), ('2018-05-01'::date), ('2018-05-02'::date), ('2018-05-09'::date), ('2018-06-11'::date), ('2018-06-12'::date), ('2018-11-05'::date), ('2018-12-31'::date), ('2019-01-01'::date), -- 2019 ('2019-01-02'::date), ('2019-01-03'::date), ('2019-01-04'::date), ('2019-01-07'::date), ('2019-01-08'::date), ('2019-03-08'::date), ('2019-05-01'::date), ('2019-05-02'::date), ('2019-05-03'::date), ('2019-05-09'::date), ('2019-05-10'::date), ('2019-06-12'::date), ('2019-11-04'::date) ), dates_include(start_time, stop_time) as ( values --  2018,  2019  ('2018-04-28 10:00:00'::timestamp, '2018-04-28 19:00:00'::timestamp), ('2018-06-09 10:00:00'::timestamp, '2018-06-09 19:00:00'::timestamp), ('2018-12-29 10:00:00'::timestamp, '2018-12-29 19:00:00'::timestamp) ) ), schedule_base(start_time, stop_time) as ( select d::timestamp + '10:00:00', d::timestamp + '19:00:00' from generate_series( (select min(start_time) from periods)::date::timestamp , (select max(stop_time) from periods)::date::timestamp , '1 day'::interval ) as days(d) where extract(dow from d) not in (0,6) ), schedule as ( select * from schedule_base where start_time::date not in (select d from dates_exclude) union select * from dates_include ), 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(coalesce(upper(wrkh)-lower(wrkh), '0 sec'::interval)) from periods_wrk group by id, start_time, stop_time 

हुर्रे! .. यह समाप्त हो सकता है, लेकिन पूर्णता के लिए हम कुछ और संबंधित विषयों पर विचार करेंगे।

विषय का और विकास

प्री-हॉलीडे प्री-डे, लंच ब्रेक, सप्ताह के अलग-अलग दिनों के लिए अलग-अलग शेड्यूल ... सिद्धांत रूप में, सब कुछ स्पष्ट है, आपको शेड्यूल की परिभाषा को ठीक करने की आवश्यकता है, बस कुछ उदाहरणों को दें।

इस तरह से आप सप्ताह के दिन के आधार पर एक कार्य दिवस के विभिन्न प्रारंभ और समाप्ति समय निर्धारित कर सकते हैं:

  select d + case extract(dow from d) when 1 then '10:00:00'::time --  when 2 then '11:00:00'::time --  when 3 then '11:00:00'::time --  --        else '10:00:00'::time end , d + case extract(dow from d) --   19   when 5 then '14:00:00'::time --  else '19:00:00'::time end from schedule_base 

यदि आपको 13:00 से 14:00 तक लंच ब्रेक का ध्यान रखना है, तो प्रति दिन एक अंतराल के बजाय, दो करें:

  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 

अच्छी तरह से और इतने पर।

उत्पादकता

मैं प्रदर्शन के बारे में कुछ शब्द कहूंगा, क्योंकि हमेशा इसके बारे में सवाल होते हैं। मैं पहले से ही बहुत चबाने वाला नहीं हूं, यह एक तारांकन चिह्न वाला खंड है।

सामान्य तौर पर, समयपूर्व अनुकूलन बुराई है। मेरे कई वर्षों के अवलोकन के अनुसार, कोड की पठनीयता इसका सबसे महत्वपूर्ण लाभ है। यदि कोड अच्छी तरह से पढ़ा जाता है, तो इसे बनाए रखना और विकसित करना आसान है। अच्छी तरह से पठनीय कोड का अर्थ है एक अच्छा समाधान वास्तुकला, उचित टिप्पणी, और अच्छे चर नाम, दोनों की आवश्यकता है, पठनीयता का त्याग किए बिना कॉम्पैक्टनेस, आदि, यानी कोड के लिए अच्छा कहा जाता है।

इसलिए, अनुरोध हमेशा संभव के रूप में पठनीय के रूप में लिखा जाता है, और हम अनुकूलन करना शुरू करते हैं यदि और केवल अगर यह पता चलता है कि प्रदर्शन अपर्याप्त है। इसके अलावा, हम इसे ठीक से ऑप्टिमाइज़ करेंगे जहाँ प्रदर्शन अपर्याप्त है और वास्तव में इस हद तक कि यह पर्याप्त हो जाता है। यदि आप निश्चित रूप से अपने स्वयं के समय को महत्व देते हैं, और आपको कुछ करना है।

लेकिन अनुरोध में अनावश्यक काम नहीं करना सही है, आपको हमेशा इसे ध्यान में रखने की कोशिश करनी चाहिए।

इसके आधार पर, हम क्वेरी में एक ऑप्टिमाइज़ेशन को तुरंत शामिल करेंगे - प्रत्येक सोर्स पीरियड को केवल उन वर्किंग टाइम अंतरालों के साथ इंटरसेक्ट करने दें, जिनके साथ इसके कॉमन पॉइंट्स (रेंज सीमाओं पर लंबी शास्त्रीय स्थिति के बजाय, यह tsrange टाइप के लिए बिल्ट-इन && ऑपरेटर का उपयोग करना अधिक सुविधाजनक है)। यह अनुकूलन पहले ही अनुरोध में दिखाई दे चुका है, लेकिन इस तथ्य के कारण है कि प्रारंभिक अवधि जो पूरी तरह से काम के घंटों से बाहर हो गई थी, परिणामों से गायब हो गई।

इस अनुकूलन को वापस लाएं। ऐसा करने के लिए, LEFT JOIN का उपयोग करें, जो पीरियड्स टेबल के सभी रिकॉर्ड को बचाएगा। अब period_wrk सबकुछ इस तरह दिखेगा:

 , 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)) 

अनुरोध के विश्लेषण से पता चलता है कि परीक्षण डेटा पर समय लगभग आधा घट गया है। चूंकि रनटाइम इस बात पर निर्भर करता है कि सर्वर एक ही समय में क्या कर रहा था, इसलिए मैंने कुछ माप लिया और कुछ "विशिष्ट" परिणाम दिया, सबसे बड़ा नहीं, बीच से छोटा नहीं।

पुरानी क्वेरी:

 explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN ------------------------------------------------------------------------------------ HashAggregate (cost=334.42..338.39 rows=397 width=36) (actual time=10.724..10.731 rows=4 loops=1) ... 

नई:

 explain (analyse) with periods(id, start_time, stop_time) as ( ... QUERY PLAN ------------------------------------------------------------------------------------ HashAggregate (cost=186.37..186.57 rows=20 width=36) (actual time=5.431..5.440 rows=4 loops=1) ... 

लेकिन सबसे महत्वपूर्ण बात यह है कि इस तरह का अनुरोध भी बेहतर पैमाने पर होगा, कम सर्वर संसाधनों की आवश्यकता होती है, क्योंकि पूर्ण कार्टेशियन उत्पाद बहुत जल्दी बढ़ता है।

और इस पर मैं अनुकूलन के साथ रुक जाऊंगा। जब मैंने अपने लिए इस समस्या को हल किया, तो मेरे पास इस अनुरोध के बहुत अधिक भयानक रूप में भी पर्याप्त प्रदर्शन था, लेकिन वास्तव में अनुकूलन की कोई आवश्यकता नहीं थी। एक बार एक तिमाही में अपने डेटा पर एक रिपोर्ट प्राप्त करने के लिए, मैं एक अतिरिक्त दस सेकंड प्रतीक्षा कर सकता हूं। ऐसी परिस्थितियों में अनुकूलन पर बिताए गए अतिरिक्त घंटे का भुगतान कभी नहीं होगा।

लेकिन यह निर्बाध रूप से बदल जाता है, आइए अब भी विचार करें कि यदि निष्पादन समय के संदर्भ में अनुकूलन की आवश्यकता होती है, तो घटनाओं का विकास कैसे हो सकता है। उदाहरण के लिए, हम डेटाबेस में अपने प्रत्येक रिकॉर्ड के लिए वास्तविक समय में इस पैरामीटर की निगरानी करना चाहते हैं, अर्थात, प्रत्येक छींक के लिए यह अनुरोध कहा जाएगा। ठीक है, या अपने स्वयं के कारण के साथ आओ, आपको अनुकूलन करने की आवश्यकता क्यों होगी।

पहली बात जो दिमाग में आती है, उसे एक बार गिनना और डेटाबेस को काम के अंतराल के साथ एक तालिका में रखना है। मतभेद हो सकते हैं: यदि डेटाबेस को बदला नहीं जा सकता है, या ऐसी तालिका में प्रासंगिक डेटा के समर्थन से कठिनाइयों की उम्मीद की जाती है। फिर आपको अनुरोध के रूप में "उड़ने पर" समय की पीढ़ी को छोड़ना होगा, क्योंकि यह बहुत भारी उपश्रेणी नहीं है।

अगला और सबसे शक्तिशाली (लेकिन हमेशा लागू नहीं) दृष्टिकोण एल्गोरिदम अनुकूलन है। इनमें से कुछ दृष्टिकोण पहले ही समस्या की स्थिति के साथ लेख में टिप्पणियों में प्रस्तुत किए गए हैं।

मुझे यह सबसे ज्यादा पसंद है। यदि आप कैलेंडर के सभी (न केवल काम करने वाले) दिनों के साथ एक तालिका बनाते हैं और एक निश्चित "दुनिया के निर्माण" से प्रत्येक दिन कितने काम के घंटे की संचयी कुल की गणना करते हैं, तो आप एक सूक्ष्मता ऑपरेशन के साथ दो तिथियों के बीच काम के घंटे की संख्या प्राप्त कर सकते हैं। यह केवल पहले और अंतिम दिन के लिए काम के घंटे को सही ढंग से लेने के लिए रहता है - और आप कर रहे हैं। यहाँ इस दृष्टिकोण में मुझे क्या मिला है:

  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 

मैं यहां संक्षेप में बताऊंगा कि क्या हो रहा है। शेड्यूल_बेस सबक्वेरी में, हम कैलेंडर के सभी दिनों को दो वर्षों के लिए उत्पन्न करते हैं और प्रत्येक दिन हम यह निर्धारित करते हैं कि कार्य दिवस (= 1) है या नहीं (= 0)। इसके अलावा, शेड्यूल सबक्वेरी में, हम विंडो फ़ंक्शन को 2018-01-01 से काम के घंटे की कुल संख्या के रूप में मानते हैं। सबकुछ एक उप-वर्ग में करना संभव होगा, लेकिन यह अधिक बोझिल हो जाएगा, जो पठनीयता को प्रभावित करेगा। फिर, मुख्य अनुरोध में, हम अवधि के अंत और शुरुआत में काम के घंटों की संख्या के बीच के अंतर पर विचार करते हैं और कुछ हद तक, इस अवधि के पहले और आखिरी दिन के काम के घंटे को ध्यान में रखते हैं। कार्यदिवस की शुरुआत से पहले के समय को और इसकी समाप्ति के बाद कार्य दिवस की समाप्ति के बाद के समय को स्थानांतरित करने से जुड़ी हुई है। इसके अलावा, अगर shedule_base और शेड्यूल के साथ अनुरोध का हिस्सा एक अलग पूर्व-परिकलित तालिका (जैसा कि पहले सुझाव दिया गया है) में हटा दिया जाता है, तो यह अनुरोध पूरी तरह से एक तुच्छ में बदल जाएगा।

आइए एक बड़े नमूने पर निष्पादन की तुलना बेहतर ढंग से किए गए अनुकूलन को दिखाने के लिए करें, कार्य अवधि से चार अवधि के लिए एक कार्य समय बनाने में अधिक समय खर्च होता है।

मुझे लगभग 3 हजार पीरियड लगे। मैं EXPLAIN में केवल शीर्ष सारांश पंक्ति दूंगा, विशिष्ट मान इस प्रकार हैं।

मूल विकल्प:

 GroupAggregate (cost=265790.95..296098.23 rows=144320 width=36) (actual time=656.654..894.383 rows=2898 loops=1) ... 

अनुकूलित:

 Hash Join (cost=45.01..127.52 rows=70 width=36) (actual time=1.620..5.385 rows=2898 loops=1) ... 

समय का लाभ परिमाण के आदेशों का एक जोड़ा था। वर्षों में अवधि और उनकी लंबाई में वृद्धि के साथ, अंतर केवल चौड़ा होगा।

सब कुछ ठीक लग रहा था, लेकिन क्यों, इस तरह का अनुकूलन करने के बाद, मैंने खुद के लिए अनुरोध का पहला संस्करण छोड़ दिया जब तक कि इसका प्रदर्शन पर्याप्त नहीं था? हां, क्योंकि अनुकूलित संस्करण निस्संदेह तेज है, लेकिन यह समझने के लिए अधिक समय की आवश्यकता है कि यह कैसे काम करता है, अर्थात पठनीयता खराब हो गई है। यही है, अगली बार जब मुझे अपनी बदली हुई शर्तों के तहत अनुरोध को फिर से लिखना होगा, तो मुझे (या मुझे नहीं) यह समझने में अधिक समय बिताना होगा कि अनुरोध कैसे काम करता है।

आज के लिए बस इतना ही, टेंटलेस को गर्म रखो, और मैं अगले रेडियो एसक्यूएल रिलीज होने तक आपको अलविदा कहूंगा।