SQL技术求助：按年份统计姓名总数并找出年度最受欢迎姓名

Alright, let's work through this SQL problem together. You're trying to get two key metrics per year: the total sum of all COUNT values, plus the most popular name (the one with the highest total count across all counties that year). Here's a clear, step-by-step solution that should work for you:

Step 1: Break Down the Requirements #

First, let's clarify what we need to compute:

• TOTAL: Sum of all COUNT values for each year (regardless of name, county, or gender)
• MOST_POPULAR: The name(s) with the highest total COUNT when aggregated across all counties for that year

Step 2: SQL Query Using CTEs (Easy to Read and Debug) #

We'll use Common Table Expressions (CTEs) to split the problem into manageable parts—just replace your_table_name with your actual table name:

WITH yearly_totals AS (
    -- Calculate total count for each year
    SELECT
        YEAR,
        SUM(COUNT) AS TOTAL
    FROM your_table_name
    GROUP BY YEAR
),
name_yearly_totals AS (
    -- Calculate total count per name per year, then rank names by their total
    SELECT
        YEAR,
        NAME,
        SUM(COUNT) AS name_total,
        -- Rank names within each year by their total count (descending)
        RANK() OVER (PARTITION BY YEAR ORDER BY SUM(COUNT) DESC) AS popularity_rank
    FROM your_table_name
    GROUP BY YEAR, NAME
)
-- Combine the two CTEs to get the final result
SELECT
    yt.YEAR,
    yt.TOTAL,
    -- Uncomment the line matching your SQL dialect to list all tied names:
    -- PostgreSQL: STRING_AGG(nyt.NAME, ', ') AS MOST_POPULAR
    -- MySQL: GROUP_CONCAT(nyt.NAME SEPARATOR ', ') AS MOST_POPULAR
    nyt.NAME AS MOST_POPULAR
FROM yearly_totals yt
JOIN name_yearly_totals nyt
    ON yt.YEAR = nyt.YEAR
WHERE nyt.popularity_rank = 1
-- Uncomment below if you only want one name per year (even if tied)
-- GROUP BY yt.YEAR, yt.TOTAL, nyt.NAME

Key Explanations: #

1. yearly_totals CTE: This is straightforward—we group by YEAR and sum all COUNT values to get the annual total.
2. name_yearly_totals CTE: Here, we first calculate how many times each name was used in a year (summing across all counties). Then we use the RANK() window function to assign a rank to each name within its year—rank 1 means it's the most popular (or tied for most popular).
3. Final Join: We combine the yearly totals with the ranked names, filtering only for rank 1 to get the most popular name(s) per year.

Handling Ties: #

If multiple names have the same highest total count in a year, you can adjust the query to list all of them using dialect-specific string aggregation functions (the commented lines in the final select block).

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