teradata-无法嵌套聚合操作min(avg)

1rhkuytd 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(447)

我想得到一家商店平均收入最低的月份。我要么得到一个所有商店的列表（下面的代码给了我一个商店12个月的时间），要么当我在内部选择中尝试min（avg\u rev）时，它会显示“teradata-cannotnest aggregate operations”。请帮忙。

| 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)

sql teradata

来源：https://stackoverflow.com/questions/63208805/teradata-cannot-nest-aggregate-operations-minavg

1条答案

按热度按时间

aydmsdu91#

如果我理解正确，你可以用 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

赞(0）回复(0）举报 2021-07-24

我来回答

teradata-无法嵌套聚合操作min(avg)

1条答案

相关问题

热门标签

最新问答