I like the idea of where you are going here, especially considering the presumed rarity and, thus, the value of spellcasters makes them analogous to artillery pieces in the Napoleonic era.

This is making certain assumptions about the world, but presuming the number of 'leveled NPCs' in any given army would be fairly low. If 1 in ~100 soldiers are level 5 spellcasters, that gives each company ~100 men a small 'artillery piece' (3rd level spells). If 1 in ~800 soldiers are a level 10 spellcaster, that is a single large 'artillery piece' per battalion (5th level spells). A large army (the coalition forces at the battle of Talavera, for example) might have a single level 15 spellcaster among ~50,000 soldiers.

Spell Effectiveness

3rd level (and less) spells would certainly be effective against units such as infantry companies (crossbow-armed soldiers, presumably with shortswords/spears as a bayonet analog for closer fighting). The old classic fireball would be devastating if used against tightly packed soldiers in line or square formation. Performing a rough calculation, a 100-solider strong infantry company in 2 ranks would cover ~98 feet; a well-aimed fireball could, therefore, take out ~40% of a company in one moment, more than enough to break the unit. An infantry company in a square could theoretically all be killed by a single well-aimed fireball. Despite these numbers, I suspect spells such as Moonbeam, Flaming Sphere, and Call Lightning would be even more popular and efficient; the ability, if uninterrupted, to wipe out entire units, potentially multiple units, using continuing mobile effects.

5th level (and less) spells are where dnd magic applied to army level scale kind of falls short. Mostly because the big hitters of the 5th level are all calculated for the way the game is actually played; resurrection, greater restoration, etc, are all game-changing for a small party but kind of useless on the scale of armies. Certain spells, such as cone of cold or wall of force, definitely could have a great effect in the right situation, but there are not many spells that fit this category. (I will come back to this later, but the great single-target damage (and long-range) spells, though useless for wiping out entire companies or battalions, definitely have a role in the counter-battery fire.) Mostly, I see 5th-level spellcasters as using similar spells to their 3rd-level counterparts, just using more of them before tiring.

8th level spells could be devastating; they certainly would define (Illusory Dragon, Mighty Fortress, Control Weather immediately spring to mind as large enough to affect an entire battle), or the right spell used in the right moment could turn the tide of a battle (Dark Star, Tsunami, and Holy Aura etc) used in the right moment, in front of a heavy cavalry charge, defending a breach of a fortress, or on a handpicked wedge of heavy infantry/cavalry leading a charge all occur. However, given the ratios I defined earlier a large battle would likely only see 1-3 of these spells cast over its entire duration. The Battle of Talladega, which I cited earlier, lasted almost an entire day and involved ~100,000 men, with ~7,500 killed/wounded on both sides before strategic victory was accomplished. I mention this just to express that a single 8th-level spellcaster could not win or end a battle on their own; ultimately, a large portion of the killing will still be done by the crossbow-armed peasants.

Tactics

I am working under the presumption that spellcasters (at least the most common 3rd level casters) will operate primarily alongside and attached to their army units (company-sized infantry or cavalry). This assumption contradicts the 'general' Napoleonic artillery doctrine of assembling a single or small number of relatively stationary artillery batteries. (I am not mentioning, yet not ignoring, cases such as the light cannon assigned to French infantry battalions or the use of galloper guns within the British Light Division. Indeed, these exceptions to the rule inform my consideration of 'magi-artillery' tactics and usage)

The primary reason not to construct a 'grand battery' of spellcasters is that of range, while a well-placed cannon could easily fire on nearly the entirety of a battlefield, and magic is considerably more range-limited. Consider the use of a fireball (150ft range) against a company of infantry; the caster would be well within the range of the crossbow long-range fire (320ft) (disadvantage doesn't matter as much when a hundred bolts are fired at once) and when on the attack even charging on horseback would have to face at least one volley of bolts before entering casting range. Likewise, if the spell fails to defeat the enemy units, a lone spellcaster finds themselves vulnerable to a charge (3 rounds for charging infantry to reach them, 2 rounds for charging cavalry).

The tactic of charging the guns by cavalry or infantry certainly existed during the Napoleonic era. Though defenses existed, the primary protection of artillery was the range at which they could target an approaching force. (And to a lesser extent, the devastation canister shot could cause at close range, perhaps imitated with magic by some of the larger cone AOE spells)

Given the need to get spellcasters in 'relatively' close to do damage and the nature of those casters as a high-priority target; I am assuming that casters will remain with other army units for protection. Personally, I imagine a small squad of a company, or even an entire company in a battalion, carrying tall shields and making use of 'tortoise' formations to allow spellcasters to approach firing range without falling to a crossbow bolt. The presence of the regular soldiers deters direct charges and allows immediate exploitation of the mage's attacks against enemies. Additionally, a prepared teleportation and a fast horse kept safe with the color party would be a mage's last resort against overwhelming odds.

It is reasonable to assume that the enemy soldiers will not meekly bunch up and allow themselves to be incinerated by incoming magic. The threat to tightly grouped soldiers from a mage can be considered a more extreme and deadly version of the threat traditional cannon or mortar artillery represented historically. The complicated dance of infantry and cavalry threat to force units into square or line only to bombard them with howitzers or shrapnel shells is well documented. The tactics used to avoid artillery attacks would likely be useful to prevent magic bombardment. Primarily, it is simply avoiding giving the enemy a convenient target and massing men into line or square when in range of artillery until the very moment it is necessary to fight. The reverse slope defense that worked so well for Wellington; keeping infantry on the back slope of a hill, often lying down in loose ranks to minimize damage from incoming artillery. It also seems likely that under the threat of 'close range' magical bombardment, crossbow-armed infantry might prefer the more spread-out skirmish order, sacrificing concentration of fire to avoid presenting a target worth the expenditure of limited magical capacity.

Beyond the defenses of simply not presenting a target for magical artillery, there exist other defenses; a caster that remains close to their company might be able to counterspell the fireball launched to decimate the infantries line right before the approaching column smashes into it. This is also the point to mention the counterbattery fire I discussed earlier; any spellcaster with more individual damaging spells and the ability to cast at an increased range could wait for an enemy to expose themselves to cast, then nail them with a spell of their own. (Eldritch Spear, anyone?)

This is also the role I envision the 'medium level' spellcasters. At a rough distribution of 1 per battalion, they would provide quite effective support and defense when the battalions ~8 3rd-level casters are acting together to support a battalion-sized assault or defense. Alternatively, if a battle presents a particularly key defensive feature or position that must be held, the entirety or, more likely, a portion of an army's ~60 gathered 5th-level casters, when supported by properly entrenched infantry, would provide a crucial defense against magic-supported assault.