Как найти пропущенные часы и показать данные как 0

Этот запрос извлекает счетчики для каждого часа каждого дня, но пропускает все дни и часы, для которых нет счетчиков. Как я могу заполнить пропущенные даты и часы между датами, выбранными с 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]

Вам понадобится набор данных для LEFT JOIN. Вы можете использовать Tally для создания всех необходимых временных диапазонов.

Larnu 17.05.2022 15:09
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
1
23
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Вам нужна справочная таблица, в которой хранятся все даты и соответствующий час. Это будет своего рода таблица календаря с добавленным к ней часом.

Шаг 1:

Создание часов стола.

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

Шаг 2:

Создайте таблицу, в которой будут обязательные поля даты и часа.

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 joinCounts с ней на основе даты и часа.

Ниже запрос должен дать вам желаемый результат.

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;

Выход:

DayHourDay of the weekHour of the day
2022-04-01 00:00:00.000Friday0
2022-04-01 01:00:00.000Friday1
2022-04-01 02:00:00.000Friday2
... 714 more rows ...
2022-04-30 21:00:00.000Saturday21
2022-04-30 22:00:00.000Saturday22
2022-04-30 23:00:00.000Saturday23

Теперь нам просто нужно оставить внешнее соединение тот с вашей существующей таблицей:

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, но неправильно понял свои соединения. Это намного чище и именно то, что я ищу. еще раз спасибо.

idnarbjm 17.05.2022 15:55

Другие вопросы по теме