如何在SQLite中按自定义起始年份的十年区间分组统计电影数量

Got it, let's sort out this decade grouping issue once and for all. The problem with your original query is that you're using every distinct year as a decade start—so 1931, 1932, 1933, etc., all get their own "decade" buckets, which is definitely not what you want. Instead, we need to map every movie's year to its correct decade start (like 1931 for 1931-1940, 1941 for 1941-1950, etc.) first, then group by those start years.

Solution 1: Hardcode the minimum year (since you know it's 1931) #

Since you confirmed the lowest year in the table is 1931, we can use that as our baseline to calculate each movie's decade start. Here's the optimized query:

SELECT 
    dec_start,
    dec_start + 9 AS dec_end,
    COUNT(DISTINCT m.MID) AS num_movies
FROM (
    SELECT 
        -- Calculate the correct decade start for each movie
        1931 + ((year - 1931) // 10) * 10 AS dec_start,
        MID
    FROM Movie
) m
GROUP BY dec_start
ORDER BY dec_start;

Solution 2: Dynamically get the minimum year (for future flexibility) #

If you want the query to adapt automatically if the lowest year changes later, use a CTE to fetch the minimum year first, then use that in the calculation:

WITH min_year AS (
    SELECT MIN(year) AS min_yr FROM Movie
)
SELECT 
    dec_start,
    dec_start + 9 AS dec_end,
    COUNT(DISTINCT grouped.MID) AS num_movies
FROM (
    SELECT 
        (SELECT min_yr FROM min_year) + ((m.year - (SELECT min_yr FROM min_year)) // 10) * 10 AS dec_start,
        m.MID
    FROM Movie m
) grouped
GROUP BY dec_start
ORDER BY dec_start;

How this works #

The key part is the decade start calculation:

• (year - min_yr) gives the number of years since the earliest movie year.
• // 10 does integer division (truncates the remainder), so 0-9 years becomes 0, 10-19 becomes 1, etc.
• Multiply by 10 and add back the minimum year, and you get the start of the decade the movie belongs to (1931 for 1931-1940, 1941 for 1941-1950, etc.).

This approach avoids the messy Cartesian product from your original query, runs more efficiently, and gives you exactly the decade groups you need.

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