时间戳

首页 > 解决方案 > Teradata - 不能嵌套聚合操作 min(avg)

sql - Teradata - 不能嵌套聚合操作 min(avg)

问题描述

我想知道商店平均收入最低的月份。我要么得到所有商店的列表（下面的代码给了我所有 12 个月的商店），要么当我在内部选择中尝试 min(avg_rev) 时，它显示“Teradata - 无法嵌套聚合操作”。请帮忙。

| store | yearmonth | min(avg_rev)|
| 102   | 2004 9    | $2000       |
| 103   | 2004 8    | $30000      |
etc


SELECT STORE, month_num||year_num AS yearmonth, min(avg_rev)
FROM (SELECT store, EXTRACT(year from saledate) AS year_num, 
   EXTRACT(month from saledate) AS month_num,
   sum(amt)/ COUNT (distinct saledate) AS avg_rev
   FROM trnsact
   WHERE stype='p'
   GROUP BY year_num, month_num,store
   HAVING NOT(year_num=2005 AND month_num=8) AND COUNT (distinct saledate)>20) AS clean_data

GROUP BY store, yearmonth, avg_rev
ORDER BY store asc, min(avg_rev)

标签： sqlteradata

解决方案

如果我理解正确，您可以使用qualify来选择月份：

SELECT store, EXTRACT(year from saledate) AS year_num, 
       EXTRACT(month from saledate) AS month_num,
       sum(amt)/ COUNT(distinct saledate) AS avg_rev
FROM trnsact
WHERE stype='p'
GROUP BY year_num, month_num, store
QUALIFY ROW_NUMBER() OVER (PARTITION BY store ORDER BY avg_rev ASC) = 1

推荐阅读