SQL中UNION的正确使用：计算不同年代电影评分均值差

Let's break down what's wrong with your original query first:

• UNION merges rows from your two subqueries into a single column. This means you end up with a list of all movie averages (both pre-1980 and post-1980) in one column, and you can't separate them to calculate the difference between their group averages.
• Using HAVING to filter by year works here (since each movie title maps to one year), but it's more efficient to use WHERE to filter rows before grouping.

Solution 1: Separate Subqueries for Each Group #

This approach calculates the average of averages for each group independently, then subtracts them directly:

SELECT
    -- Calculate average of pre-1980 movie ratings
    (SELECT AVG(movie_avg)
     FROM (SELECT AVG(stars) AS movie_avg
           FROM Rating
           JOIN Movie ON Rating.mID = Movie.mID
           WHERE year < 1980
           GROUP BY title) AS before_1980)
    -
    -- Calculate average of post-1980 movie ratings
    (SELECT AVG(movie_avg)
     FROM (SELECT AVG(stars) AS movie_avg
           FROM Rating
           JOIN Movie ON Rating.mID = Movie.mID
           WHERE year >= 1980
           GROUP BY title) AS after_1980) AS rating_difference;

Solution 2: Conditional Aggregation (More Concise) #

This method uses a single inner subquery to get every movie's average rating and its year, then uses conditional logic in the outer query to compute the group averages and their difference:

SELECT
    AVG(CASE WHEN year < 1980 THEN movie_avg END) AS avg_before_1980,
    AVG(CASE WHEN year >= 1980 THEN movie_avg END) AS avg_after_1980,
    -- Calculate the difference between the two group averages
    AVG(CASE WHEN year < 1980 THEN movie_avg END) - AVG(CASE WHEN year >= 1980 THEN movie_avg END) AS rating_difference
FROM (
    -- Get each movie's average rating and its release year
    SELECT AVG(stars) AS movie_avg, year
    FROM Rating
    JOIN Movie ON Rating.mID = Movie.mID
    GROUP BY title, year
) AS movie_ratings;

Key Notes: #

• Both approaches first compute the average rating per movie (grouped by title), then compute the average of those values for each year group. This matches your original requirement exactly.
• Using WHERE instead of HAVING in the first solution filters out irrelevant rows early, which improves query performance.

内容的提问来源于stack exchange，提问作者Annalix