通过 SUM 的 MySQL 嵌套 AVG

Question

我有包含合作伙伴、帖子、后变量列的表格

现在我想知道每个合作伙伴平均每个帖子有多少个后变量。我尝试了以下方法，但是它不起作用

SELECT partner, 
COUNT(DISTINCT post), 
COUNT(DISTINCT postvariation), 
AVG(COUNT(DISTINCT post,postvariation)) 
FROM posts 
GROUP BY partner 
ORDER BY `id` DESC;

Answer 1

这是您要查找的查询:

SELECT P.partner
    , COUNT(DISTINCT P.post) AS nb_post
    , COUNT(DISTINCT P.postvariation) AS nb_postvariation
    , COUNT(DISTINCT P.postvariation) / COUNT(DISTINCT P.post) AS avg_postvariation
FROM posts P
GROUP BY P.partner
ORDER BY P.id DESC;

下面是相同的查询，但当天有一个额外的 GROUP BY 子句:

SELECT P.partner
    , DATE_FORMAT(P.datefield, '%Y-%m-%d') AS pivot_date
    , COUNT(DISTINCT P.post) AS nb_post
    , COUNT(DISTINCT P.postvariation) AS nb_postvariation
    , COUNT(DISTINCT P.postvariation) / COUNT(DISTINCT P.post) AS avg_postvariation
FROM posts P
GROUP BY P.partner, DATE_FORMAT(P.datefield, '%Y-%m-%d')
ORDER BY P.id DESC;

希望对您有所帮助。