每当您开始考虑要本地化的GROUP BY时,都应该开始考虑窗口函数。
我认为您正在追求这样的事情:
select unit_id, time, diag
from (
select unit_id, time, diag,
rank() over (partition by unit_id order by time desc) as rank
from diagnostics.unit_diag_history
) as dt
where rank = 1