The video remains pinned above the desktop and apps, including in full-screen mode and split view, so long as the Safari tab or iTunes window where the video originates remains open. The picture-in-picture window has buttons to close or unpin the video, and play and pause controls, but no rewind, fast forward, or scrubbing options.
In Safari, picture-in-picture mode currently only works on certain websites, such as YouTube, CNN, and The Wall Street Journal, but the feature should become more widely available as other websites implement it. In the meantime, it does not work on websites like Netflix, Facebook, Twitter, AMC, or The New York Times.
Even on YouTube, enabling picture-in-picture mode currently requires somewhat of a workaround. Since there is no picture-in-picture mode button in the video player itself, like in the CNN screenshot above, enabling the feature on YouTube requires right-clicking the video player twice until a contextual menu pops up with the option.
macOS Sierra was released today as a free download on the Mac App Store.