在SQL中获取3月内amps三列总和的MAX()结果行

需求说明 #

需要获取ID为abcxyz的记录中，2025年3月内amps_a + amps_b + amps_c总和的最大值对应的所有行（若有多个相同最大值则全部返回）。

错误的SQL及报错信息 #

尝试的SQL语句：

SELECT 
    a.id, a.volts_a, a.volts_b, a.volts_c, 
    a.amps_a, a.amps_b, a.amps_c, 
    amps_a + amps_b + amps_c AS sum1, 
    a.phase_a_voltage_angle, a.phase_a_current_angle, 
    a.phase_b_voltage_angle, a.phase_b_current_angle, 
    a.phase_c_voltage_angle, a.phase_c_current_angle, 
    a.time_stamp, b.max1
FROM
    Table1 a
INNER JOIN 
    (SELECT id, MAX(sum1) max1, time_stamp
     FROM Table1
     WHERE id = "abcxyz"
       AND time_stamp = 202503
     GROUP BY id, time_stamp) b ON a.id = b.id 
                                AND a.amps_a = b.amps_a 
                                AND a.time_stamp = b.time_stamp

执行后报错：

AnalysisException: Could not resolve column/field reference: 'sum1'

问题原因 #

1. 别名作用域错误：子查询中引用的sum1是外部查询定义的别名，SQL中子查询无法识别外部查询的列别名，必须直接在子查询内计算求和表达式。
2. 连接条件错误：原连接条件用a.amps_a = b.amps_a匹配，这是错误逻辑——我们需要匹配的是该行的求和总和等于子查询得到的最大值，而非单独匹配amps_a列。

修正方案 #

方案1：修正子查询与连接条件 #

SELECT 
    a.id, a.volts_a, a.volts_b, a.volts_c, 
    a.amps_a, a.amps_b, a.amps_c, 
    (a.amps_a + a.amps_b + a.amps_c) AS sum1, 
    a.phase_a_voltage_angle, a.phase_a_current_angle, 
    a.phase_b_voltage_angle, a.phase_b_current_angle, 
    a.phase_c_voltage_angle, a.phase_c_current_angle, 
    a.time_stamp, b.max1
FROM
    Table1 a
INNER JOIN 
    (SELECT id, MAX(amps_a + amps_b + amps_c) AS max1, time_stamp
     FROM Table1
     WHERE id = "abcxyz"
       AND time_stamp = 202503
     GROUP BY id, time_stamp) b ON a.id = b.id 
                                AND (a.amps_a + a.amps_b + a.amps_c) = b.max1 
                                AND a.time_stamp = b.time_stamp

方案2：使用窗口函数（更简洁推荐） #

利用Hive/Impala支持的RANK()窗口函数，无需自连接即可直接筛选出最大值对应的行：

SELECT *
FROM (
    SELECT 
        id, volts_a, volts_b, volts_c, 
        amps_a, amps_b, amps_c, 
        (amps_a + amps_b + amps_c) AS sum1, 
        phase_a_voltage_angle, phase_a_current_angle, 
        phase_b_voltage_angle, phase_b_current_angle, 
        phase_c_voltage_angle, phase_c_current_angle, 
        time_stamp,
        -- 按ID和时间分组，对求和总和降序排名
        RANK() OVER (PARTITION BY id, time_stamp ORDER BY (amps_a + amps_b + amps_c) DESC) AS rnk
    FROM Table1
    WHERE id = "abcxyz"
      AND time_stamp = 202503
) t
WHERE rnk = 1

• RANK()会为相同最大值的行分配相同的排名（均为1），因此能保留所有最大值对应的行；若使用ROW_NUMBER()则只会随机保留一行，不符合需求。

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