Teradata报错：所选非聚合值需属关联分组，SQL分组排查求助

你触发报错的核心原因是：SELECT子句中引用了来自VT_BASELINE表的b.total_CPU、b.total_IOUsage、b.QRY_count三个非聚合字段，但这些字段没有出现在GROUP BY子句中。

Teradata的GROUP BY规则要求：SELECT列表中所有非聚合函数生成的字段，要么属于GROUP BY分组键，要么能被分组键唯一确定（但Teradata默认不会自动推断这种唯一性，必须显式声明或通过子查询规避）。

提供两种可行的修正方式：

方式一：将b表字段加入GROUP BY #

如果VT_BASELINE表中每个username对应的total_CPU、total_IOUsage、QRY_count是唯一值，直接把这些字段加到GROUP BY中即可：

SELECT 
    A.username,
    A.logdate,
    SUM(A.AMPCPUTIME) AS cpu,
    SUM(A.TOTALIOCOUNT) AS totalIO,
    COUNT(A.QUERYID) AS qrycount,
    (cpu / b.total_CPU) * 100 AS cpu_threshold_percentage,
    (totalIO / b.total_IOUsage) * 100 AS io_threshold_percentage,
    (qrycount / b.QRY_count) * 100 AS qry_threshold_percentage
FROM 
    pdcrinfo.DBQLOGTBL_HST A
INNER JOIN
    VT_BASELINE b ON A.username = b.username
WHERE 
    A.LOGDATE = DATE-1 
    AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
GROUP BY
    A.username, A.logdate, b.total_CPU, b.total_IOUsage, b.QRY_count;

方式二：先聚合再关联（更推荐） #

通过子查询先完成A表的聚合计算，再和VT_BASELINE表关联计算百分比，这样可以避免GROUP BY中加入过多字段，逻辑更清晰：

WITH agg_data AS (
    SELECT 
        A.username,
        A.logdate,
        SUM(A.AMPCPUTIME) AS cpu,
        SUM(A.TOTALIOCOUNT) AS totalIO,
        COUNT(A.QUERYID) AS qrycount
    FROM 
        pdcrinfo.DBQLOGTBL_HST A
    WHERE 
        A.LOGDATE = DATE-1 
        AND A.username NOT IN ('tdstatsmgr', 'tdstatsmgr_ms', 'sysadmin')
    GROUP BY
        A.username, A.logdate
)
SELECT 
    ad.username,
    ad.logdate,
    ad.cpu,
    ad.totalIO,
    ad.qrycount,
    (ad.cpu / b.total_CPU) * 100 AS cpu_threshold_percentage,
    (ad.totalIO / b.total_IOUsage) * 100 AS io_threshold_percentage,
    (ad.qrycount / b.QRY_count) * 100 AS qry_threshold_percentage
FROM agg_data ad
INNER JOIN VT_BASELINE b ON ad.username = b.username;

这种方式的优势是：聚合逻辑和关联逻辑分离，降低了GROUP BY子句的复杂度，同时保证只有分组键参与聚合计算，符合SQL的最佳实践。

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