Specific fuel consumption in [g/kWh] is an indicator for the engine efficiency. In general, the effect mentioned in this question is larger for gasoline engines than for diesel engines, but is occurs for both technologies.
In case of gasoline engines, the engine runs throttled at part load, which decreases the engine efficiency. These throtteling losses have a main impact on the efficiency behavior, besides other rpm- and load-point related effects, e.g. friction behavior, head transfer, combustion efficiency.
Diesel engines are not throttled by air flow (that means the intake air mass flow is not restricted by a throttle). In this way, diesel engines run with a certain air excess combustion in nearly all operating points. But the best efficiency point of diesel engines is also in the area of maximum torque (often a certain torque value below the maximum torque operation point). The reason are engine friction behavior, heat loss behavior and combustion -related effects.
For detailed research in engine efficiency behavior I recomment to study the loss distribution analysis of combustion engines. There is several literature available, e.g. Internal Combustion Engine Fundamentals by John B. Heywood.
Under low RPM, the efficiency of the engine is usually low, easy to imagine why the SFC is high here.
Under high RPM, the friction and inertia of the piston and rod becomes a major major factor here. So even though the total brake power might increase when RPM goes even higher, the SFC will increase by a lot.