Вставьте даты в ответ на запрос, где их нет
Мы строим запрос для подсчета количества событий в час в день. В большинстве дней есть часы, в которых нет активности, и поэтому при выполнении запроса отображается количество операций в час, но есть пробелы, и запрос исключает их. Мы по-прежнему хотим показывать часы, в которых нет активности, и отображать ноль, чтобы затем можно было отобразить нулевое значение. Используемый нами запрос выглядит следующим образом...
select datepart(Year, dev_time) as Year,
datepart(Month, dev_time) as Month,
datepart(Day, dev_time) as Day,
datepart(Hour, dev_time) as Hour,
count(tdm_msg) as Total_ACTIVITES
from TCKT_ACT
where tdm_msg = ‘4162′ and dev_time >= DATEADD(day, - 1, GETDATE())
group by datepart(Year, dev_time) ,
datepart(Month, dev_time) ,
datepart(Day, dev_time),
datepart(Hour, dev_time)
order by datepart(Year, dev_time) asc,
datepart(Month, dev_time) asc,
datepart(Day, dev_time) asc,
datepart(Hour, dev_time) asc
5 ответов
Сначала я создал табличную функцию, основанную на рекурсивном общем запросе к таблице, описанном Дейвом Марклом (спасибо, что показал мне этого Дейва!). Это очень мило, потому что мне нужно сделать функцию только один раз, и я могу использовать ее для анализа любых интервалов.
if exists (select * from dbo.sysobjects where name = 'fn_daterange') drop function fn_daterange;
go
create function fn_daterange
(
@MinDate as datetime,
@MaxDate as datetime,
@intval as datetime
)
returns table
--**************************************************************************
-- Procedure: fn_daterange()
-- Author: Ron Savage
-- Date: 12/16/2008
--
-- Description:
-- This function takes a starting and ending date and an interval, then
-- returns a table of all the dates in that range at the specified interval.
--
-- Change History:
-- Date Init. Description
-- 12/16/2008 RS Created.
-- **************************************************************************
as
return
WITH times (startdate, enddate, intervl) AS
(
SELECT @MinDate as startdate, @MinDate + @intval - .0000001 as enddate, @intval as intervl
UNION ALL
SELECT startdate + intervl as startdate, enddate + intervl as enddate, intervl as intervl
FROM times
WHERE startdate + intervl <= @MaxDate
)
select startdate, enddate from times;
go
Поэтому, если вы сделаете выбор из этой функции самостоятельно, вы получите таблицу временных интервалов, например:
fn_daterange ('12 / 14/2008 10:00:00 ', '12 / 14/2008 20:00:00', '01: 00: 00 ')
возвращает:
startdate enddate intervl
----------------------- ----------------------- -----------------------
2008-12-14 10:00:00.000 2008-12-14 10:59:59.997 1900-01-01 01:00:00.000
2008-12-14 11:00:00.000 2008-12-14 11:59:59.997 1900-01-01 01:00:00.000
2008-12-14 12:00:00.000 2008-12-14 12:59:59.997 1900-01-01 01:00:00.000
2008-12-14 13:00:00.000 2008-12-14 13:59:59.997 1900-01-01 01:00:00.000
2008-12-14 14:00:00.000 2008-12-14 14:59:59.997 1900-01-01 01:00:00.000
2008-12-14 15:00:00.000 2008-12-14 15:59:59.997 1900-01-01 01:00:00.000
2008-12-14 16:00:00.000 2008-12-14 16:59:59.997 1900-01-01 01:00:00.000
2008-12-14 17:00:00.000 2008-12-14 17:59:59.997 1900-01-01 01:00:00.000
2008-12-14 18:00:00.000 2008-12-14 18:59:59.997 1900-01-01 01:00:00.000
2008-12-14 19:00:00.000 2008-12-14 19:59:59.997 1900-01-01 01:00:00.000
2008-12-14 20:00:00.000 2008-12-14 20:59:59.997 1900-01-01 01:00:00.000
Затем я сделал пример таблицы данных событий:
eventdate eventnote
----------------------- --------------------
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:01:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 10:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 11:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 14:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:23:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
2008-12-14 19:00:00.000 oo! an event!
22 Row(s) affected
Затем я подключил их к левому внешнему соединению примерно так:
select
dr.startdate,
dr.enddate,
count(me.eventdate) as eventcount
from
fn_daterange('12/14/2008 10:00:00', '12/14/2008 20:00:00', '01:00:00' ) dr
LEFT OUTER JOIN myevents me
on ( me.eventdate between dr.startdate and dr.enddate)
group by
dr.startdate,
dr.enddate
startdate enddate eventcount
----------------------- ----------------------- ----------
2008-12-14 10:00:00.000 2008-12-14 10:59:59.993 7
2008-12-14 11:00:00.000 2008-12-14 11:59:59.993 5
2008-12-14 12:00:00.000 2008-12-14 12:59:59.993 0
2008-12-14 13:00:00.000 2008-12-14 13:59:59.993 0
2008-12-14 14:00:00.000 2008-12-14 14:59:59.993 3
2008-12-14 15:00:00.000 2008-12-14 15:59:59.993 0
2008-12-14 16:00:00.000 2008-12-14 16:59:59.993 0
2008-12-14 17:00:00.000 2008-12-14 17:59:59.993 0
2008-12-14 18:00:00.000 2008-12-14 18:59:59.993 0
2008-12-14 19:00:00.000 2008-12-14 19:59:59.993 7
2008-12-14 20:00:00.000 2008-12-14 20:59:59.993 0
11 Row(s) affected
СВЯТЫЙ КРЕП, это мило - я могу использовать это для всех видов анализа на работе!:-)
Спасибо Фреду за вопрос и Дейву за информацию о распространенных табличных запросах!
Рон
Вам как-то понадобится таблица дней и часов, а затем вам придется выполнить внешнее соединение между этой таблицей и вашим запросом. Вот как бы я это сделал. Обратите внимание, что это решение будет работать только в SQL Server 2005 и 2008. Если у вас нет этих платформ, вам придется создать таблицу времени в вашей базе данных, из которой вы можете объединиться из:
DECLARE @MinDate DATETIME;
SET @MinDate = CONVERT(varchar, GETDATE(), 101);
WITH times AS (
SELECT @MinDate as dt, 1 as depth
UNION ALL
SELECT DATEADD(hh, depth, @MinDate), 1 + depth as depth
FROM times
WHERE DATEADD(hh, depth, @MinDate) <= GETDATE())
SELECT DATEPART(YEAR, t.dt) as [Year],
DATEPART(MONTH, t.dt) as [Month],
DATEPART(DAY, t.dt) as [Day],
DATEPART(HOUR, t.dt) as [Hour],
COUNT(tdm_msg) as Total_ACTIVITES
FROM times t
LEFT JOIN (SELECT * FROM TCKT_ACT WHERE tdm_msg = '4162' and dev_time >= @MinDate) a
ON DATEPART(HOUR, t.dt) = DATEPART(HOUR, a.dev_time)
AND MONTH(t.dt) = MONTH(a.dev_time)
AND DAY(t.dt) = DAY(a.dev_time)
AND YEAR(t.dt) = YEAR(a.dev_time)
GROUP BY DATEPART(YEAR, t.dt) ,
DATEPART(MONTH, t.dt) ,
DATEPART(DAY, t.dt),
DATEPART(HOUR, t.dt)
ORDER BY DATEPART(YEAR, t.dt) asc,
DATEPART(MONTH, t.dt) asc,
DATEPART(DAY, t.dt) asc,
DATEPART(HOUR, t.dt) asc
OPTION (MAXRECURSION 0); /* Just in case you want a longer timespan later on... */
Обратите внимание, что оператор WITH в верхней части называется рекурсивным общим табличным выражением и является хорошим способом генерации последовательных таблиц с относительно небольшим количеством элементов, как у вас здесь.
У нас была похожая проблема с некоторым программным обеспечением для мониторинга производительности, но, находясь в магазине мэйнфреймов DB2/z, мы совершенно не желаем выполнять гимнастику SQL для получения таких результатов. Запросы SQL, которые выполняют "функции" в каждой строке, которую они извлекают, общеизвестно не масштабируются, и администраторы баз данных будут смеяться над нами, если мы попытаемся их использовать.
Вместо этого мы обнаружили, что проще реорганизовать схему базы данных, включив в нее число событий в каждой строке (по-видимому, наши администраторы баз данных не против использования большего дискового пространства, просто больше нагрузки на процессор). В вашем случае это будет добавление столбца с именем tdm_quant который вы бы установили на 1 для каждой вставляемой строки (т. е. для каждого события).
Тогда пятое поле вашего запроса изменится с count(tdm_msg) в sum(tdm_quant) который достигнет того же результата.
В дополнение к этому вы можете вставить специальную запись (один раз в час, или 24 из них в начале каждого дня или заполнить целые годы на 1 января, если хотите), где tdm_quant поле установлено в ноль. Будучи нулевым, эти записи не будут влиять на sum(tdm_quant) но вы получите желаемое поведение, строка возвращается для каждого часа дня, который будет иметь ноль как Total_ACTIVITIES где в этот час не произошло никаких событий.
Остальной ваш запрос не нужно будет менять.
Звучит так, как будто вы можете использовать "левое внешнее соединение", используя другую таблицу с номерами от 1 до 24...
Основной ответ здесь включает в себя левое внешнее соединение (LOJ) и явное COUNT(column) так как это не считает нули, но COUNT(*) считает все строки. Тяжелая часть - это создание таблицы, в которой нужно делать LOJ. Предложение WITH и рекурсивное решение будут работать во многих СУБД (по-видимому, MS SQL Server и почти наверняка DB2 - возможно, и другие).
Многие СУБД поддерживают временные таблицы и хранимые процедуры; комбинацию можно использовать для заполнения таблицы соответствующим набором значений для поля даты / времени, а затем выполнить LOJ для этой таблицы (или, точнее, FROM temp_table LEFT OUTER JOIN main_table ...). Не так аккуратно и аккуратно, но работает в большинстве мест.