将具有空值或缺少值的第一行和最后一行替换为Postgresql12中的上一个/下一个可用值

WITH lag_lead_values AS (
  SELECT 
    column_name,
    LAG(column_name) OVER (ORDER BY column_name) AS lag_value,
    LEAD(column_name) OVER (ORDER BY column_name) AS lead_value
  FROM table_name
)
SELECT 
  column_name,
  COALESCE(column_name, lag_value) AS replaced_value
FROM lag_lead_values
WHERE column_name IS NULL
UNION ALL
SELECT 
  column_name,
  COALESCE(column_name, lead_value) AS replaced_value
FROM lag_lead_values
WHERE column_name IS NULL
ORDER BY column_name;