SQL查询引发高CPU占用与高延迟，求等效改写方案

首先你的原SQL存在语法错误，JOIN子句写法不正确，正确的关联逻辑需要指定关联表与ON条件。另外原查询通过子聚合再关联的方式，会对视图mfh.view_mats进行两次扫描，这大概率是CPU占用过高、延迟严重的核心原因之一，尤其是当视图数据量较大时。

改写方案1：使用ROW_NUMBER()窗口函数 #

SELECT qsw, mngy
FROM (
    SELECT 
        qsw, 
        mngy,
        ROW_NUMBER() OVER (PARTITION BY mngy ORDER BY nonUnixjdjf DESC) rn
    FROM mfh.view_mats
    WHERE jdjf <= '2022-10-19 10:47:25.000000'
) t
WHERE rn = 1;

该写法通过窗口函数在一次扫描中，为每个mngy分组内的记录按nonUnixjdjf降序排号，取排号为1的记录（即每个mngy对应最大nonUnixjdjf的那条数据），避免了子查询关联带来的二次扫描。

改写方案2：使用MAX() OVER()窗口函数过滤 #

SELECT DISTINCT
    qsw, 
    mngy
FROM (
    SELECT 
        qsw, 
        mngy,
        nonUnixjdjf,
        MAX(nonUnixjdjf) OVER (PARTITION BY mngy) max_nonUnixjdjf
    FROM mfh.view_mats
    WHERE jdjf <= '2022-10-19 10:47:25.000000'
) t
WHERE nonUnixjdjf = max_nonUnixjdjf;

这个思路是先计算每个mngy分组的最大nonUnixjdjf值，再过滤出当前记录的nonUnixjdjf等于该分组最大值的行，最后用DISTINCT去重（如果同一mngy有多个记录的nonUnixjdjf等于最大值时）。

额外优化建议 #

• 检查视图mfh.view_mats的定义，如果视图本身包含复杂关联或计算，考虑优化视图底层的查询逻辑，或者将视图结果物化（业务允许数据有一定延迟的前提下）。
• 确保jdjf字段有合适的索引，因为查询用到了该字段的范围过滤；如果能创建(mngy, nonUnixjdjf DESC, jdjf)的组合索引，窗口函数的分组排序操作会更高效。

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