Этот запрос извлекает счетчики для каждого часа каждого дня, но пропускает все дни и часы, для которых нет счетчиков. Как я могу заполнить пропущенные даты и часы между датами, выбранными с 0 для подсчета?
SELECT CAST(RecordTime AS date) AS Date, DATENAME(dw, RecordTime) AS [Day of the week], DATEPART(hour, RecordTime) AS [Hour of the day], COUNT(*) AS [Hourly Count]
FROM Counts
WHERE (RecordTime >= CONVERT(DATETIME, '2022-04-01 00:00:00', 102)) AND (RecordTime < CONVERT(DATETIME, '2022-05-01 00:00:00', 102)) AND (MachineNum = 11) AND (Cavity = 1)
GROUP BY CAST(RecordTime AS date), DATEPART(hour, RecordTime), DATENAME(dw, RecordTime)
ORDER BY Date, [Hour of the day]
Вам нужна справочная таблица, в которой хранятся все даты и соответствующий час. Это будет своего рода таблица календаря с добавленным к ней часом.
Создание часов стола.
CREATE TABLE hours
(
hour_key INTEGER
);
INSERT INTO hours
VALUES (0),
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10),
(11),
(12),
(13),
(14),
(15),
(16),
(17),
(18),
(19),
(20),
(21),
(22),
(23);
Создайте таблицу, в которой будут обязательные поля даты и часа.
SELECT Cast(recordtime AS DATE) AS Date_key,
Datename(dw, recordtime) week_key,
hour_key
FROM hours
CROSS JOIN counts
GROUP BY Cast(recordtime AS DATE),
Datename(dw, recordtime),
hour_key
Примечание. Использование таблицы Считает для получения поля даты и часа является плохим решением, поскольку это может быть таблица транзакций и иметь огромное количество записей. Вместо этого используйте таблицу календаря.
Используйте таблицу, созданную в Шаг 2, в качестве основной таблицы (или используйте в качестве подзапроса) и left join
Counts
с ней на основе даты и часа.
Ниже запрос должен дать вам желаемый результат.
SELECT date_key AS Date,
week_key AS [Day of the week],
hour_key AS [Hour of the day],
Count(c.recordtime) AS [Hourly Count]
FROM (SELECT Cast(recordtime AS DATE) AS Date_key,
Datename(dw, recordtime) week_key,
hour_key
FROM hours
CROSS JOIN counts
GROUP BY Cast(recordtime AS DATE),
Datename(dw, recordtime),
hour_key)cal
LEFT JOIN counts c
ON cal.date_key = Cast(c.recordtime AS DATE)
AND cal.hour_key = Datepart(hour, c.recordtime)
GROUP BY date_key,
week_key,
hour_key
ORDER BY date,
[hour of the day]
SQL-скрипт: Попробуйте здесь
Как предложил Ларну, вам нужно создать полный набор данных со всеми комбинациями дня и часа в диапазоне, чтобы присоединиться к левому соединению. По моим расчетам вам нужно 30 дней * 24 часа = 720 строк. Если у вас еще нет таблица чисел, или календарный стол, или функция генерации последовательности, вы можете сгенерировать это с помощью рекурсивных CTE следующим образом:
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH days(d) AS
(
SELECT 0 UNION ALL SELECT d+1 FROM days
WHERE d < DATEDIFF(DAY, @StartDate, @AfterLastDate) - 1
), hours(h) AS
(
SELECT 0 UNION ALL SELECT h+1 FROM hours WHERE h<23
),
dates(DayHour, h) AS
(
SELECT DATEADD(HOUR, hours.h, DATEADD(DAY, days.d, @StartDate)), hours.h
FROM days CROSS JOIN hours
)
SELECT d.DayHour, DATENAME(WEEKDAY, DayHour), d.h
FROM dates AS d
ORDER BY d.DayHour;
Выход:
DayHour Day of the week Hour of the day 2022-04-01 00:00:00.000 Friday 0 2022-04-01 01:00:00.000 Friday 1 2022-04-01 02:00:00.000 Friday 2 ... 714 more rows ... 2022-04-30 21:00:00.000 Saturday 21 2022-04-30 22:00:00.000 Saturday 22 2022-04-30 23:00:00.000 Saturday 23
Теперь нам просто нужно оставить внешнее соединение тот с вашей существующей таблицей:
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH days(d) AS
(
SELECT 0 UNION ALL SELECT d+1 FROM days
WHERE d < DATEDIFF(DAY, @StartDate, @AfterLastDate) - 1
), hours(h) AS
(
SELECT 0 UNION ALL SELECT h+1 FROM hours WHERE h<23
),
dates(DayHour, h) AS
(
SELECT DATEADD(HOUR, hours.h, DATEADD(DAY, days.d, @StartDate)),
hours.h FROM days CROSS JOIN hours
)
SELECT [Date] = CONVERT(date, d.DayHour),
[Day of the week] = DATENAME(WEEKDAY, d.DayHour),
[Hour of the day] = d.h,
[Hourly Count] = COUNT(c.RecordTime)
FROM dates AS d
LEFT OUTER JOIN dbo.Counts AS c
ON c.RecordTime >= d.DayHour
AND c.RecordTime < DATEADD(HOUR, 1, d.DayHour)
AND c.MachineNum = 11
AND c.Cavity = 1
GROUP BY CONVERT(date, d.DayHour), DATENAME(WEEKDAY, DayHour), d.h
ORDER BY [Date], [Hour of the day];
Если у вас есть таблица чисел, генерация дат немного проще. Это простой пример, который содержит только 1000 строк как самый большой диапазон дат, который вы ожидаете, и использует рекурсивное CTE — существует несколько способов первоначально заполнить таблицу чисел, и производительность здесь не важна.
CREATE TABLE dbo.Numbers(n int PRIMARY KEY);
;WITH x(x) AS
(
SELECT 0 UNION ALL SELECT x+1 FROM x
WHERE x < 1000
)
INSERT dbo.Numbers(n)
SELECT x FROM x OPTION (MAXRECURSION 0);
Теперь запрос, чтобы получить все даты в диапазоне:
DECLARE @StartDate datetime = '20220401',
@AfterLastDate datetime = '20220501';
;WITH dates(d) AS
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @AfterLastDate)) n
FROM dbo.Numbers ORDER BY n
),
hours(h) AS
(
SELECT TOP (24) n FROM dbo.Numbers ORDER BY n
)
SELECT DayHour = DATEADD(HOUR, hours.h,
DATEADD(DAY, dates.d, @StartDate))
FROM dates CROSS JOIN hours
ORDER BY DayHour;
Затем вы можете использовать его в качестве основного набора данных для левого соединения, как в приведенных выше примерах.
Большое спасибо за поддержку. Я попытался сделать это с таблицей #temp, но неправильно понял свои соединения. Это намного чище и именно то, что я ищу. еще раз спасибо.
Вам понадобится набор данных для
LEFT JOIN
. Вы можете использовать Tally для создания всех необходимых временных диапазонов.