问Teradata:选定的非聚合值必须是关联组的一部分。
EN

Stack Overflow用户

提问于 2022-09-15 04:10:43

回答 3查看 54关注 0票数 2

这是我的SQL语句：

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;

我不知道我遗漏了哪一列。在select部分中只有两个非聚合列。

sql

teradata

回答 3

Stack Overflow用户

发布于 2022-09-15 04:39:28

您的聚合是CPU、TotalIO和QryCount。

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;

粗体的字段(注意，参见** )需要在这个当前查询中按组或聚合在组中。CPU、TotalIO和QryCount应该可以工作，因为Teradata允许在select语句下面引用内联聚合。

见total_CPU，total_IOUsage，QRY_count

我不知道你的数据，但你能像你以前的聚合一样把它们放在select中吗？用，数，最多，敏还是森？然后在你的公式中引用它们？

票数 1

Stack Overflow用户

发布于 2022-09-15 08:17:00

来自b的列不是按组分组的，它们要么添加它们，要么应用聚合函数，例如MAX(b.total_CPU)。

但是，在加入之前的聚合应该更有效：

select a.*,
   (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
 (
    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
 ) as a
INNER JOIN VT_BASELINE b 
ON A.username = b.username
;

票数 1

Stack Overflow用户

发布于 2022-09-15 04:55:29

Teradata不允许您使用同一查询中的聚合。你需要像这样重新计算它们：

SELECT 
    A.username,
    A.logdate,
    SUM(A.AMPCPUTIME) AS cpu,
    SUM(A.TOTALIOCOUNT) AS totalIO,
    COUNT(A.QUERYID) AS qrycount,
    (SUM(A.AMPCPUTIME)/ b.total_CPU) * 100 AS cpu_threshold_percentage,
    (SUM(A.TOTALIOCOUNT)/ b.total_IOUsage) * 100 AS io_threshold_percentage,
    (COUNT(A.QUERYID)/ 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;