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

С индексом mydate вы на правильном пути. Большинство решений, скорее всего, будут включать в себя либо соединение цикла и поиск по индексу (зондирование) в этом индексе, либо сканирование диапазона соответствующей части индекса (лучше, чем сканирование таблицы), которое передается логике, которая ищет пробелы.

Самым очевидным и, возможно, наиболее эффективным решением было бы генерировать диапазон значений данных/времени, который затем фильтруется с помощью предложения where not exists(...). Что-то вроде:

declare @FromDate datetime = '2024-07-01'
declare @ToDate datetime = dateadd(month, 1, @FromDate) -- Exclusive
declare @DiffHours int = datediff(hour, @FromDate, @ToDate)

-- Using not exists
-- Loop join and index seek (probe)
select h.Hour
from generate_series(0, @DiffHours - 1) s
cross apply (select dateadd(hour, s.value, @FromDate) as Hour) h
where not exists (
    select *
    from a
    where a.mydate >= h.Hour
    and a.mydate < dateadd(hour, 1, h.Hour)
)
order by h.Hour

В течение одного месяца индекс будет проверяться по пункту exists примерно 750 раз.

Другой подход — сканировать диапазон дат, сводить его к набору отдельных значений часов, а затем вычитать эти значения из сгенерированного списка всех часов. Операцию вычитания можно выполнить с помощью except или конструкции left join ... where right-side is null.

declare @FromDate datetime = '2024-07-01'
declare @ToDate datetime = dateadd(month, 1, @FromDate) -- Exclusive
declare @DiffHours int = datediff(hour, @FromDate, @ToDate)

-- Using except
-- Index seek (range scan) and merge join (or spool + loop join)
select h.Hour
from generate_series(0, @DiffHours - 1) s
cross apply (select dateadd(hour, s.value, @FromDate) as Hour) h
except
select distinct datetrunc(hour, a.mydate) as Hour
from a
where a.mydate >= @FromDate
and a.mydate < @ToDate
order by Hour

-- Using left join
-- Index seek (range scan) and merge join (or spool + loop join)
-- (Slighly different from the "except" plan)
select h.Hour
from generate_series(0, @DiffHours - 1) s
cross apply (select dateadd(hour, s.value, @FromDate) as Hour) h
left join (
    select distinct datetrunc(hour, a.mydate) as Hour
    from a
    where a.mydate >= @FromDate
    and a.mydate < @ToDate
) h2
    on h2.Hour = h.Hour
where h2.Hour is null
order by H.Hour

Наконец, можно выполнить упорядоченное сканирование диапазона дат, проверяя наличие пробелов с помощью оконной функции LAG() для сравнения текущего значения с предыдущим. Знак UNION ALL включен, чтобы гарантировать, что мы охватываем конечные случаи (первый и последний час).

Может быть небольшой выигрыш в производительности, если операции UNION переходят на следующий внешний уровень запроса, поскольку данные уже отсортированы, но цена — это еще два поиска по индексу. Обе версии следуют далее.

declare @FromDate datetime = '2024-07-01'
declare @ToDate datetime = dateadd(month, 1, @FromDate) -- Exclusive

-- Index seek (range scan), using lag() to check for gaps.
-- A union-all is used to ensure we have the end-cases covered.
select
    dd.MissingHours,
    dateadd(hour, 1, dp.PriorHour) as FromHour,
    dateadd(hour, -1, Hour) as ThruHour
from (
    select
        d.Hour,
        lag(d.Hour) over(order by d.Hour) as PriorHour 
    from (
        select dateadd(hour, -1, @FromDate) as Hour
        union all 
        select @ToDate as Hour
        union all 
        select datetrunc(hour, a.mydate) as Hour
        from a
        where a.mydate >= @FromDate
        and a.mydate < @ToDate
    ) d
) dp
cross apply (select datediff(hour, dp.PriorHour, dp.Hour) - 1 as MissingHours) dd
where dd.MissingHours >= 1
order by dp.PriorHour

-- Index seek (range scan), using lag() to check for gaps.
-- Factoring out this union /might/ yield a slight performance gain,
-- since data is already sorted, but the cist is two more index seeks.
select
    dd.MissingHours,
    dateadd(hour, 1, dp.PriorHour) as FromHour,
    dateadd(hour, -1, Hour) as ThruHour
from (
    select
        d.Hour,
        lag(d.Hour) over(order by d.Hour) as PriorHour 
    from (
        select datetrunc(hour, a.mydate) as Hour
        from a
        where a.mydate >= @FromDate
        and a.mydate < @ToDate
    ) d
    union all
    select
        datetrunc(hour, min(a.mydate)) as Hour,
        dateadd(hour, -1, @FromDate) as PriorHour
    from a
    where a.mydate >= @FromDate
    and a.mydate < @ToDate
    union all
    select
        @ToDate as Hour,
        datetrunc(hour, max(a.mydate)) as PriorHour
    from a
    where a.mydate >= @FromDate
    and a.mydate < @ToDate
) dp
cross apply (select datediff(hour, dp.PriorHour, dp.Hour) - 1 as MissingHours) dd
where dd.MissingHours >= 1
order by dp.PriorHour

Вышеупомянутые диапазоны доходности вместо отдельных строк для каждого недостающего часа. Это может быть желательно. Если нет, то вышеприведенное можно изменить с помощью GENERATE_SERIES() и DATEADD(), чтобы расширить каждый полученный диапазон.

select dateadd(hour, s.value, dp.PriorHour) as Hour
from (
    ...
) dp
cross apply (select datediff(hour, dp.PriorHour, dp.Hour) - 1 as MissingHours) dd
cross apply generate_series(1, dd.MissingHours) s
where dd.MissingHours >= 1
order by Hour

См. эту db<>fiddle для демонстрации каждого из вышеперечисленных действий с планами выполнения с использованием некоторых сгенерированных тестовых данных с промежутками в 1, 3 и 5 часов.

Вы можете запустить тесты производительности на своих реальных данных, чтобы увидеть, что лучше всего подходит для вас.

Примеры результатов (отдельные часы и диапазоны часов):

СТАРЫЕ ВЕРСИИ SQL-СЕРВЕРА

Вышеупомянутые решения SQL используют функции DATE_TRUNC() и/или GENERATE_SERIES(), доступные в SQL Server 2022 и более поздних версиях.

В более ранних версиях SQL Server функцию DATE_TRUNC(hour, xxx) можно заменить вычислением dateadd(hour, datediff(hour, 0, xxx), 0). Функцию GENERATE_SERIES() можно заменить генератором чисел, завернутым в CTE или cross apply. Существует множество методов, но для этого приложения вы можете использовать следующие варианты:

cross apply (
    select top (n) -- 1..n
        row_number() over(order by (select null)) as value
    from (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n1(n) -- Up to 10
    cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n2(n) -- Up to 100
    cross join (values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) n3(n) -- Up to 1000
) s

Функция row_number() выдаёт числа, начинающиеся с 1. Вычитание 1 и корректировка значения top (n) необходимы для диапазонов, отсчитываемых от нуля или других оснований. Добавьте больше перекрестных соединений, если требуется более 1000 значений.

См. эту db<>fiddle для демонстрации, которая работает для более ранних версий SQL Server, по крайней мере, до 2014 года.