Teradata:选定的非聚合值必须是关联组的一部分。
Teradata:选定的非聚合值必须是关联组的一部分。
提问于 2022-09-15 12:10:43
这是我的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部分中只有两个非聚合列。
回答 3
Stack Overflow用户
发布于 2022-09-15 12: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中吗？用，数，最多，敏还是森？然后在你的公式中引用它们？
Stack Overflow用户
发布于 2022-09-15 16: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
;Stack Overflow用户
发布于 2022-09-15 12: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;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/73730903
复制相关文章
相似问题
腾讯云开发者
