sql - 清点清楚后如何计算平均值？

Question

我想清点后取平均值。

这是我当前的查询:

SELECT product_id,
         COUNT(DISTINCT shop_id) AS shops,
         year,
         month,
         day
FROM X
GROUP BY  product_id, year, month, day

但现在，我想取商店的平均值。我试过这样的事情，但我收到了这个错误:invalidrequestexception

SELECT product_id,
         AVG(COUNT(DISTINCT shop_id) AS shops) as avg_shops,
         year,
         month,
         day
FROM X
GROUP BY  product_id, year, month, day

Answer 1

像下面这样尝试

with cte as
(
SELECT product_id,
         COUNT(DISTINCT shop_id) AS shops,
         year,
         month,
         day
FROM X
GROUP BY  product_id, year, month, day
) select product_id,
         AVG(shops) AS  as avg_shops,
         year,
         month,
         day
FROM cte
GROUP BY  product_id, year, month, day