Why is this stream aggregate necessary?
You can see the role of this aggregate if no rows match the WHERE clause.

SELECT MAX(Revision)
FROM   dbo.TheOneders
WHERE  Id = 1
       AND 1 = 1 /*To avoid auto parameterisation*/
       AND Id%3 = 4  /*always false*/


In that case zero rows go into the aggregate but it still emits one as the correct semantics are to return NULL in this case.



This is a scalar aggregate as opposed to a vector one. 

Your "logically equivalent" query is not equivalent. Adding GROUP BY Id would make it a vector aggregate and then the correct behaviour would be to return no rows.

See Fun with Scalar and Vector Aggregates for more about this.