Obtaining zero statistical information in the absence of source data

Very often there is the problem of providing some statistics for a specified period. For example, how many efficiencies have been committed by the system user over the last week. It would seem that nothing is simpler:

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


For the week we expect to get five lines - one for each working day. Everything is working. As long as users perform at least one action per day. If the table does not have a single row with the date, then as a result of this date will not. And instead of the expected five lines in the report there will be four ... or three ... or none at all. And users want to see five, even with zeros!
')

The solution is to associate a table with data with another one that lists the dates. You can list the dates, but now the customer wanted a summary report by month, and tomorrow, and by year, he wants. Again to create tables, already with months and years? Not very convenient, and not interesting. And what if you try to "emulate" these tables on the fly? After all at us and functions are able to return, suitable for use in pure SQL '. For this we need an auxiliary type:

CREATE OR REPLACE TYPE TDate_Sequence AS TABLE OF DATE;
/


and, actually, the function itself:

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;
/


We associate the available data with the generated 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


... and get so desired by the customer zeros!