如何将SQL中每季度每小时值的下一行扩展/填充为每小时值(MSSQL)

WITH cte AS (
  SELECT 
    DATEPART(QUARTER, datetime) AS quarter,
    DATEPART(HOUR, datetime) AS hour,
    value,
    ROW_NUMBER() OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime) AS rn
  FROM table1
)
SELECT 
  quarter,
  hour,
  value
FROM cte
WHERE rn = 1

WITH cte AS (
  SELECT 
    DATEPART(QUARTER, datetime) AS quarter,
    DATEPART(HOUR, datetime) AS hour,
    value,
    LAG(value) OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime) AS prev_value,
    DATEDIFF(HOUR, LAG(datetime) OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime), datetime) AS hour_diff
  FROM table1
)
SELECT 
  quarter,
  hour,
  prev_value AS value
FROM cte
WHERE hour_diff = 1

WITH cte1 AS (
  SELECT 
    DATEPART(QUARTER, datetime) AS quarter,
    DATEPART(HOUR, datetime) AS hour,
    value,
    ROW_NUMBER() OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime) AS rn
  FROM table1
),
cte2 AS (
  SELECT 
    DATEPART(QUARTER, datetime) AS quarter,
    DATEPART(HOUR, datetime) AS hour,
    value,
    LAG(value) OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime) AS prev_value,
    DATEDIFF(HOUR, LAG(datetime) OVER (PARTITION BY DATEPART(QUARTER, datetime), DATEPART(HOUR, datetime) ORDER BY datetime), datetime) AS hour_diff
  FROM table1
)
SELECT 
  quarter,
  hour,
  value
FROM cte1
WHERE rn = 1
UNION
SELECT 
  quarter,
  hour,
  prev_value AS value
FROM cte2
WHERE hour_diff = 1