如何用SQL实现按州整体及州内城市的温度分级排名？

看起来你需要完成双层温度排名的需求：先给所有州按平均温度做全局排名，再在每个州内部给下属城市按平均温度做排名对吧？我帮你完善并解释这段SQL逻辑：

SELECT 
    -- 所有州的全局温度排名（按平均温度降序）
    RANK() OVER(ORDER BY AVG(t.Average_Temp) DESC) AS State_Rank_Temp,
    s.State_Name,
    -- 各州内部的城市温度排名（按州分区，再按平均温度降序）
    RANK() OVER(PARTITION BY s.State_Name ORDER BY AVG(t.Average_Temp) DESC) AS City_Rank,
    s.City_Name,
    -- 计算每个城市的平均温度
    AVG(t.Average_Temp) AS Average_Temp
FROM 
    aqs_sites s
INNER JOIN 
    Temperature t 
    ON (s.State_Code = t.State_Code 
        AND s.County_Code = t.County_Code  -- 补全截断的关联条件，确保数据匹配准确
        AND s.City_Name = t.City_Name)
GROUP BY 
    s.State_Name, s.City_Name  -- 按州+城市分组，保证聚合计算的正确性
ORDER BY 
    State_Rank_Temp, City_Rank;  -- 最终结果按州排名、州内城市排名排序

关键逻辑解释： #

• 表关联：用INNER JOIN把州城市表(aqs_sites)和温度表(Temperature)通过州代码、郡代码、城市名称关联，确保每个城市的温度数据匹配准确。
• 州级全局排名：RANK() OVER(ORDER BY AVG(t.Average_Temp) DESC) 没有指定PARTITION BY，所以是对所有州的平均温度做降序排名，温度越高排名越靠前。如果有多个州平均温度相同，会获得相同排名（比如并列第1）。
• 州内城市排名：RANK() OVER(PARTITION BY s.State_Name ORDER BY AVG(t.Average_Temp) DESC) 用PARTITION BY s.State_Name把数据按州拆分，每个州内部独立计算城市的温度排名，同样是降序规则。
• 聚合与分组：因为用到了AVG()聚合函数，必须通过GROUP BY s.State_Name, s.City_Name确保每个城市的平均温度是基于该城市的所有温度记录计算的。

可选调整： #

如果需要处理并列排名的间隙问题（比如两个州并列第1后，下一个州是第3），可以把RANK()换成DENSE_RANK()，这样并列后下一个排名会连续（比如并列第1后下一个是第2）。

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