MariaDB按城市筛选max_wind或max_gust最高对应行的查询问题

原有查询的核心失效原因 #

你之前使用的INNER JOIN查询存在逻辑偏差，是导致结果漏返的根本原因：

• 子查询逻辑是分别独立计算每个城市的max_wind全局最大值、max_gust全局最大值，后续JOIN时要求单条原始记录必须同时满足「是城市内max_wind最高值」且「是城市内max_gust最高值」两个条件
• 实际业务数据中，这两个指标的峰值几乎不会落在同一条记录上，以Calgary城市为例：
  • max_wind的峰值70出现在hid=4789的记录
  • max_gust的峰值86出现在hid=4788、hid=4790的记录
  • 没有任何一条原始记录同时满足max_wind=70、max_gust=86，匹配过程中就会直接漏掉整个Calgary的目标记录，这也是仅返回2条结果的原因。

可正确返回预期结果的实现方案 #

方案1：窗口函数实现（MySQL 8.0+ 推荐写法） #

通过行号排序逻辑，按城市维度筛选符合取值规则的单条记录，规则完全匹配预期结果的判定逻辑：优先取max_gust最高的记录，max_gust相同时取max_wind更高的记录，同时兼容max_gust为NULL的场景。

SELECT hid, city, max_wind, max_gust, timestamp
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY city 
            ORDER BY 
                COALESCE(max_gust, -1) DESC, -- 阵风值为空时排序优先级置为最低
                max_wind DESC
        ) AS row_rank
    FROM high_wind
    WHERE timestamp > NOW() - INTERVAL 72 HOUR
) city_rank
WHERE row_rank = 1;

方案2：低版本MySQL兼容写法 #

对于不支持窗口函数的老版本MySQL，可以通过拼接排序键的方式调整JOIN逻辑，匹配效果与窗口函数方案完全一致：

SELECT a.*
FROM high_wind a 
INNER JOIN (
    SELECT 
        city,
        MAX(
            CONCAT(
                LPAD(COALESCE(max_gust, -1), 4, '0'),
                LPAD(max_wind, 4, '0')
            )
        ) AS sort_key
    FROM high_wind
    WHERE timestamp > NOW() - INTERVAL 72 HOUR
    GROUP BY city
) b 
ON a.city = b.city 
AND CONCAT(
    LPAD(COALESCE(a.max_gust, -1), 4, '0'),
    LPAD(a.max_wind, 4, '0')
) = b.sort_key
WHERE a.timestamp > NOW() - INTERVAL 72 HOUR;

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