I'm not a native speaker but in my opinion, "knock AT the door" implies the location of the person doing the knocking. In this case, it is somewhere close to the door or "at the door".
Therefore, I tend to think "he knocks at the door" as equivalent to "he stands in front of the door and he knocks"
As for "knock on the door", to me, it has a literal meaning of knocking the hand against the door. This phrase only describes the action of knocking. In common sense, it should therefore imply the location of the person albeit indirectly.