你需要走几步。首先，对于每个记录，您需要查看它有多少小时的连续的前一个数据。这就是 grouped_hour_data 子句在下面的解决方案中不起作用。
然后，您需要从该结果中进行选择，只获取具有完整24小时连续的前一个数据的行。然后只取其中的前24行。
此解决方案经过简化，以利用以下事实：所有日期都被截断为小时，并且没有重复的日期。如果你的问题比这更复杂，这个解决方案仍然可以支持它，但需要修改。
在这个例子中，我们创建了几天以前的测试数据，但是删除了16日和17日个别时间的数据，这样连续的第一个24小时周期就在16日提前结束了。

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

with hour_data_raw AS (
SELECT to_date('17-JUN-2020 17:00:00','DD-MON-YYYY HH24:MI:SS') - ( INTERVAL '1' HOUR ) * rownum dte
FROM dual
CONNECT BY rownum <= 200 ),
hour_data AS ( SELECT dte 
               FROM hour_data_raw
               WHERE NOT TRUNC(dte,'HH') = to_date('17-JUN-2020 02:00:00','DD-MON-YYYY HH24:MI:SS')
               AND NOT TRUNC(dte,'HH') = to_date('16-JUN-2020 02:00:00','DD-MON-YYYY HH24:MI:SS') ),
-- SOLUTION BEGINS HERE... everything above is just test data
-- WITH...
   grouped_hour_data AS (
     SELECT h.*, count(trunc(h.dte,'HH')) OVER ( ORDER BY dte desc RANGE BETWEEN CURRENT ROW AND INTERVAL '1' DAY - INTERVAL '1' SECOND FOLLOWING ) cnt
     FROM hour_data h
     ORDER BY dte)
SELECT * FROM grouped_hour_data
WHERE cnt = 24
ORDER BY dte desc
FETCH FIRST 24 ROWS ONLY;

+----------------------+-----+
|         DTE          | CNT |
+----------------------+-----+
| 16-JUN-2020 01:00:00 |  24 |
| 16-JUN-2020 00:00:00 |  24 |
| 15-JUN-2020 23:00:00 |  24 |
| 15-JUN-2020 22:00:00 |  24 |
| 15-JUN-2020 21:00:00 |  24 |
| 15-JUN-2020 20:00:00 |  24 |
| 15-JUN-2020 19:00:00 |  24 |
| 15-JUN-2020 18:00:00 |  24 |
| 15-JUN-2020 17:00:00 |  24 |
| 15-JUN-2020 16:00:00 |  24 |
| 15-JUN-2020 15:00:00 |  24 |
| 15-JUN-2020 14:00:00 |  24 |
| 15-JUN-2020 13:00:00 |  24 |
| 15-JUN-2020 12:00:00 |  24 |
| 15-JUN-2020 11:00:00 |  24 |
| 15-JUN-2020 10:00:00 |  24 |
| 15-JUN-2020 09:00:00 |  24 |
| 15-JUN-2020 08:00:00 |  24 |
| 15-JUN-2020 07:00:00 |  24 |
| 15-JUN-2020 06:00:00 |  24 |
| 15-JUN-2020 05:00:00 |  24 |
| 15-JUN-2020 04:00:00 |  24 |
| 15-JUN-2020 03:00:00 |  24 |
| 15-JUN-2020 02:00:00 |  24 |
+----------------------+-----+

编辑：处理类别字段

处理额外的 category 在你添加的字段中，你需要做一些事情。
第一， PARTITION BY category 当你在计算 cnt 现场。这将导致在计算此值时，每个类别的数据都被单独处理。因此，例如，类别a在第2小时的值将不会计为类别b在第2小时的值。
第二，你不能再使用 FETCH FIRST 24 ROWS ONLY 获取所需的数据，因为现在需要每个类别中的前24行。所以，你需要一个额外的步骤( ordered_groups ，对每个类别中前面连续24小时有数据的行进行排序。这叫命令 rn 然后，在最后一个查询中，只需选择 where rn <= 24 .

WITH grouped_hour_data AS (
     SELECT h.*, count(trunc(h.dte,'HH')) OVER ( 
              PARTITION BY category 
              ORDER BY dte desc 
              RANGE BETWEEN CURRENT ROW 
                  AND INTERVAL '1' DAY - INTERVAL '1' SECOND FOLLOWING ) cnt
     FROM hour_data h
     ORDER BY dte),
 ordered_groups AS (
   SELECT ghd.*, row_number() over ( partition by ghd.category order by ghd.dte desc ) rn
   FROM   grouped_hour_data
   WHERE  ghd.cnt = 24 )
SELECT * FROM ordered_groups
WHERE rn <= 24;
ORDER BY category, dte desc;

披露：我没有测试这个更新的逻辑，所以可能有一些错误。

看起来你实际上是在你的每小时表的最后24行。如果是这样，可以使用行限制子句：

select date, value
from mytable
order by date desc
fetch first 24 rows only

或者如果每小时可能有多个记录，那么另一个选项是 dense_rank() :

select date, value
from (
    select t.*, dense_rank() over(order by trunc(date, 'hh24') desc) rn
    from mytable t
) t
where rn <= 24

[编辑]以下内容适用于您：

IF OBJECT_ID('tempdb..#hours') IS NOT NULL 
    DROP TABLE #hours

create table #hours ([Hour] int)

insert into #hours select 1
insert into #hours select 2
insert into #hours select 3
insert into #hours select 4
insert into #hours select 5
insert into #hours select 6
insert into #hours select 7
insert into #hours select 8
insert into #hours select 9
insert into #hours select 10
insert into #hours select 11
insert into #hours select 12
insert into #hours select 13
insert into #hours select 14
insert into #hours select 15
insert into #hours select 16
insert into #hours select 17
insert into #hours select 18
insert into #hours select 19
insert into #hours select 20
insert into #hours select 21
insert into #hours select 22
insert into #hours select 23
insert into #hours select 24

-- step 1 --
IF OBJECT_ID('tempdb..#temp1') IS NOT NULL 
    DROP TABLE #temp1

select
    t.[Date]
    ,convert(date,t.[Date]) [Day]
    ,datepart(hour,t.[Date]) [Hour]
    ,t.Value

into
    #temp1

from
    #yourtable t

-- step 2 --
IF OBJECT_ID('tempdb..#temp2') IS NOT NULL 
    DROP TABLE #temp2

select
    max(t.[Day]) [MostRecentDay]

into
    #temp2

from
    #temp1 t
    cross apply (
        select
            count(distinct i.[Hour]) [HrCt]
        from
            #temp1 i
        where
            t.[Day] = i.[Day]
    ) hc

where
    hc.HrCt <> 24

-- step 3 --
IF OBJECT_ID('tempdb..#temp3') IS NOT NULL 
    DROP TABLE #temp3

select
    min(t1.[Hour]) [FirstBlank]

into
    #temp3

from
    #temp2 t2
    inner join #temp1 t1
        on t2.[MostRecentDay] = t1.[Day]
    left outer join #hours h
        on t1.[Hour] = h.[Hour]

where
    h.[Hour] is null

-- final select --
select top 24
    t1.[Date]
    ,t1.[Value]

from
    #temp1 t1
    cross join #temp2 t2
    cross join #temp3 t3

where
    t1.[Date] < convert(datetime,concat(t2.[MostRecentDay],' ',t3.[FirstBlank]))

order by
    t1.[Date] desc