Why 3 rather than 2, 4 or 5? I get the concept of “beginning, middle and end” but that sits awkwardly with trilogies because the middle is typically as large as the beginning and end together. So, short of making a 1.5 hour first movie, a 3 hour second movie and a 1.5 hour end movie, using the trilogy format doesn’t work well and results in overly long 1st and 3rd movies and overly short 2nd movies.
The midpoint of a story is often where a dramatic shift happens which would make for a good cliffhanger and would really show where things turned around. So, I’d expect series to be split into 2 or 4 more often than 3.
But that’s not the case. How come trilogies are so common?
In some cases it may simply be tradition. For example, Tolkien’s Lord of the Rings just happened to be published in three volumes, but its success may have given people the idea that “trilogies are cool!”
In some cases it may be somehow related to the Rule of Three (Wikipedia, TVTropes).
And in some cases it may indeed be that the trilogy echoes the “beginning, middle, end” structure of a story, as the OP noted.
Note that there are two kinds of “trilogies”: the kind made up of three separate but somehow related works, and the kind where the three together make up a single story.
I suspect that most of the popularity of trilogies over the past few decades comes from the original publication of The Lord of the Rings in three volumes, even though it’s actually just one novel. The following is not definite proof of that claim, but it’s interesting evidence. Look at the Google Ngrams for “trilogy”, “book trilogy”, and “trilogy of books”:
“Book trilogy” definitely took off at the point The Lord of the Rings became well known. The same may be true for “trilogy of books”. “Trilogy” has been slowly rising in popularity for quite a while.
Not only is a trilogy a classic three-act structure, with “1- introduction of characters and situation, 2 - put them in a difficult position, and 3 - get them out of it” but each book individually is largely that three act structure also, which in combination creates the best way for capturing a rising tension, hopefully with a satisfying denouement for each, even if there are cliffhangers.
Movie trilogies can do the same thing, though sometimes if a sequel is an afterthought that doesn’t apply organically. An example of it working, though, is arguably the Pirates of the Caribbean movies, or the original Star Wars trilogy.
Isaac Asimov’s three Foundation books were combined in an inexpensive version in 1963 as The Foundation Trilogy. J. R. R. Tolkien’s three books were combined in an inexpensive boxed set edition in 1966, informally referred to as a Trilogy. The 1960s were the first time that f&sf garnered huge sales, public notice, and respectability as paperback releases. Both spawned endless imitators. Trilogies go back to ancient times, but popular culture adopted the trilogy format as a sales enhancer after the 1960s. Wendell Wagner’s ngram charts confirm that, and if you check film trilogy and trilogy of films you get even steeper curves.
What’s ironic is that neither of the two base trilogies were designed as such. As Thudlow Boink said, the Rings are one novel split into three books for publishing ease and cost. Asimov wrote eight Foundation novelettes and they were gathered 3, 3, and 2 for the same reason. Trilogies had to exist and sell in physical form for publishers to see the light. After them, the deluge. Everything was stretched or bent into trilogies whether that was a good idea or not. That’s exactly the same reason why trilogies have turned into series, which are endless and worthless long before the later entries appear.