如何平均按一组的最低限度

Question

如果我选择每个组的最低限度：

SELECT MIN(price) FROM Products GROUP BY category

如何获得此最小值的平均值？我知道那

SELECT AVG(MIN(price)) FROM Products GROUP BY category

行不通。这里有帮助吗？

Answer 1

您必须找到所有类别的最低限度，然后将平均功能应用于它们。

select avg(m) as AvgOfAllMins from 
(
select category,min(price) as m from products
group by category) T;

请参阅 db-fiddle 的结果。

Answer 2

您需要分两个步骤进行此操作：

WITH min_prices as 
(
  SELECT category, MIN(price) as min_price 
  FROM Products 
  GROUP BY category
)
SELECT category, AVG(min_price) as avg_min_price
FROM min_prices
GROUP BY category