स्रोत डेटा की अनुपस्थिति में शून्य सांख्यिकीय जानकारी प्राप्त करना

बहुत बार समस्या एक निर्दिष्ट अवधि के लिए कुछ आंकड़े प्रदान करने से उत्पन्न होती है। उदाहरण के लिए, सिस्टम के उपयोगकर्ता द्वारा पिछले एक सप्ताह में कितने उपयोगी कार्य किए गए हैं। ऐसा लगेगा कि कुछ भी आसान नहीं है:

SELECT Data, COUNT(*)
FROM tbl
WHERE Data BETWEEN SYSDATE-7 AND SYSDATE
GROUP BY Data
ORDER BY Data


एक सप्ताह में हम पांच पंक्तियों को प्राप्त करने की उम्मीद करते हैं - प्रत्येक कार्य दिवस के लिए एक। सब कुछ काम करता है। जब तक उपयोगकर्ता प्रति दिन कम से कम एक क्रिया करते हैं। यदि तालिका में एक तारीख के साथ एक भी पंक्ति नहीं है, तो परिणामस्वरूप कोई परिणाम नहीं होगा। और रिपोर्ट में अपेक्षित पांच लाइनों के बजाय चार ... या तीन ... या कोई भी नहीं होगा। और उपयोगकर्ता पांच देखना चाहते हैं, फिर भी शून्य के साथ!


समाधान तालिका को डेटा के साथ लिंक करने के लिए है जो तिथियों को सूचीबद्ध करता है। तिथियां सूचीबद्ध की जा सकती हैं, लेकिन ग्राहक महीनों, और कल और वर्षों के लिए एक समेकित रिपोर्ट चाहता था। फिर से टेबल बनाने के लिए, पहले से ही महीनों और वर्षों के साथ? बहुत सुविधाजनक नहीं है, और दिलचस्प नहीं है। लेकिन क्या होगा अगर आप मक्खी पर इन तालिकाओं को "अनुकरण" करने की कोशिश करते हैं? आखिरकार, हमारे कार्य शुद्ध SQL में उपयोग के लिए उपयुक्त डेटासेट वापस करने में सक्षम हैं। ऐसा करने के लिए, आपको एक सहायक प्रकार की आवश्यकता है:

CREATE OR REPLACE TYPE TDate_Sequence AS TABLE OF DATE;
/


और, वास्तव में, समारोह ही:

CREATE OR REPLACE FUNCTION Generate_Date_Sequence( -- - .
Data_Beg DATE, /* .*/
Data_End DATE, /* .*/
Step CHAR) /* : D - , M - , Y - .*/
RETURN TDate_Sequence
AS
data_curr DATE;
stp CHAR;
Result TDate_Sequence := TDate_Sequence();
BEGIN
stp := UPPER(Step);
IF stp = 'D' THEN
data_curr := TRUNC(Data_Beg, 'DD');
ELSIF stp = 'M' THEN
data_curr := TRUNC(Data_Beg, 'MM');
ELSIF stp = 'Y' THEN
data_curr := TRUNC(Data_Beg, 'YYYY');
END IF;
-- .
WHILE data_curr <= Data_End LOOP
Result.EXTEND;
Result(Result.LAST) := data_curr;
IF stp = 'D' THEN
data_curr := data_curr + 1;
ELSIF stp = 'M' THEN
data_curr := ADD_MONTHS(data_curr, 1);
ELSIF stp = 'Y' THEN
data_curr := ADD_MONTHS(data_curr, 12);
END IF;
END LOOP;
-- .
RETURN Result;
END Generate_Date_Sequence;
/


हम उपलब्ध डेटा को उत्पन्न अनुक्रम से जोड़ते हैं:

SELECT data_sequence.column_value Data, COUNT(tbl.Data)
FROM TABLE(CAST(Generate_Date_Sequence(SYSDATE-30,SYSDATE,'D') AS TDate_Sequence)) data_sequence, tbl
WHERE tbl.Data(+) = data_sequence.column_value
GROUP BY data_sequence.column_value
ORDER BY data_sequence.column_value


... और हम ग्राहक द्वारा वांछित शून्य प्राप्त करते हैं!