There are too many overlapping criteria in many cases to give you a simple answer.
The simpler PIC and AVR chips are quite similar in capabilities and often the choice is simply personal preference. Both are designed with more I/O control capabilities than actual processing power. PIC32 are newer though and have a 32 bit MIPS core that is much more capable if you need faster processing. dsPIC has additional DSP logic in the core. Small PICs and AVRs are simple to develop for and are cheap.
ARM is a 32-bit RISC core and is generally used where you need far more processing capability than the simpler PIC and AVR chips can manage. For instance, running a small graphics-based tablet or a broadband modem/router. The downside is that they are more complicated to develop for and are more expensive.
DSPs are MCU's with additional, mathematical logic built in the core to allow much faster processing of linear/digitised signals. With such devices it is possible to make real-time digital filters and perform such things as FFT. While all of this could be done in software on standard MCU's, that is usually too slow to be useful.
CPLDs and FPGAs are not MCUs. They are programmable/configurable logic. Think of them as a few tens, up to millions, of programmable logic cells, with latches, gates and sometimes memory that can have all of the interconnections made and re-made by a design. CPLDs are smaller and used to replace a lot of logic chips on a board. FPGAs go up to HUGE (and huge cost!) and are big enough to design a whole microprocessor inside to interact with the rest of the logic. They are amazing!