I've flown a few times in my life, both in economy and executive class. Every time I fly in executive, during takeoff, there is a different noise from the engines. In economy class (behind the engines) the wind sound dominates, but in the executive class you hear the noise in the video below until you're high in the skies. Why is that?
That buzzing sound is from the tips of the fan blades breaking the sound barrier. When you're sitting farther back in the airplane, that sound is overpowered by the sound of the exhaust, which is the roaring part of a jet engine's sound.
So in general, sit forward of the wing, you'll hear the fan sound (the buzz), sit aft of the wing and you'll hear the exhaust sound (roar). It'll vary from airplane to airplane of course, and when thrust is reduced and the fan speed slows, the buzz will subside.