使用分析函数获取开始\u id和最后\u id，然后聚合：

with PUBLISH as ( --Use your_table instead of this CTE
select stack(6,
'20200101','14','A','2020-01-01 14:18:53.016 GMT','ID_111',
'20200101','14','A','2020-01-01 14:18:53.012 GMT','ID_222',
'20200101','14','A','2020-01-01 14:18:53.016 GMT','ID_111',
'20200101','14','A','2020-01-01 14:18:53.019 GMT','ID_333',
'20200101','15','C','2020-01-01 15:18:53.016 GMT','ID_444',
'20200102','00','A','2020-01-01 15:18:53.016 GMT','ID_444'
) as (DT, HOUR, SOURCE, COL_TIMESTAMP, ID)
)

select DT, HOUR, SOURCE,
       min(COL_TIMESTAMP) as MIN_TIMESTAMP,
       START_ID,
       max(COL_TIMESTAMP) as MAX_TIMESTAMP,
       END_ID,
       sum(case when rn=1 then 1 else 0 end) as RECORD_CNT --unique records have rn=1
 from
     (
      select DT, HOUR, SOURCE, COL_TIMESTAMP, ID,
             first_value(ID) over(partition by DT, HOUR, SOURCE order by COL_TIMESTAMP)      as START_ID, 
             first_value(ID) over(partition by DT, HOUR, SOURCE order by COL_TIMESTAMP desc) as END_ID,
             row_number() over(partition by DT, HOUR, SOURCE, COL_TIMESTAMP, ID)             as rn
        from PUBLISH p
     ) s
 group by DT, HOUR, SOURCE, START_ID, END_ID;

结果：

dt  hour    source  min_timestamp   start_id    max_timestamp   end_id  record_cnt
20200101    14  A   2020-01-01 14:18:53.012 GMT ID_222  2020-01-01 14:18:53.019 GMT ID_333  3
20200101    15  C   2020-01-01 15:18:53.016 GMT ID_444  2020-01-01 15:18:53.016 GMT ID_444  1
20200102    00  A   2020-01-01 15:18:53.016 GMT ID_444  2020-01-01 15:18:53.016 GMT ID_444  1

您应该能够使用子查询来确定给定小时的最小和最大时间戳以及不同行的计数，然后将其连接回主表以获取 id 这些时间的值：

SELECT DISTINCT P.DATE, P.HOUR, P.SOURCE,
       P.MIN_TIMESTAMP, p1.ID AS START_ID,
       P.MAX_TIMESTAMP, p2.ID AS END_ID
       P.COUNT
FROM (
    SELECT DATE, HOUR, SOURCE, 
           MIN(COL_TIMESTAMP) AS MIN_TIMESTAMP,
           MAX(COL_TIMESTAMP) AS MAX_TIMESTAMP,
           COUNT(DISTINCT DATE, HOUR, SOURCE, COL_TIMESTAMP, ID) AS COUNT
    FROM PUBLISH
    WHERE DATE = '20200101'
      AND HOUR = 14
      AND SOURCE = 'A'
    GROUP BY DATE, HOUR, SOURCE
) P
JOIN PUBLISH P1 ON P1.DATE = P.DATE AND P1.HOUR = P.HOUR AND P1.SOURCE = P.SOURCE AND P1.COL_TIMESTAMP = P.MIN_TIMESTAMP
JOIN PUBLISH P2 ON P2.DATE = P.DATE AND P2.HOUR = P.HOUR AND P2.SOURCE = P.SOURCE AND P2.COL_TIMESTAMP = P.MAX_TIMESTAMP

只要你有索引就行 (DATE, HOUR, SOURCE) 这应该表现得很好。