Sex is arousing in both the general and specific sense of the term. It's exciting. It's a big, important thing for us humans, and it's something we rarely see because its culturally taboo.

From an evolutionary psychology perspective (and speaking very generally), men get physically aroused when they are in a position to compete for sex, and women get physically aroused when it appears likely they will have sex. Seeing other people having sex triggers those responses.

This is also common across other mammals, so it's likely to go far back from an evolutionary perspective and not something that we can rationalize in terms of human behavior.