Получение нулевой статистической информации при отсутствии исходных данных

Очень часто возникает задача предоставления некоей статистики за указанный период. Например, сколько было совершено полезных действий пользователем системы за последнюю неделю. Казалось бы, нет ничего проще:

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


… и получаем так желанные заказчиком нули!