mysql对子分组匹配if子句的数据进行分组

Question

我确实有以下分组查询:

查询

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

Fiddle here

输出与我想要得到的结构相匹配。

然而，数据中的某些帖子在哪里每个帖子只有一个变体。我只想计算一个帖子有多个变体的帖子和变体。我想到了这样的查询:

SELECT P.partner, COUNT(DISTINCT P.postid) AS nb_post,
COUNT(DISTINCT P.postvariationid) AS nb_postvariation,
COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) AS avg_postvariation
FROM posts P
GROUP BY P.partner,P.postid
HAVING COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) > 1
ORDER BY P.id DESC;

但是这个 COUNTS 只适用于整体分组，我不确定如何将它应用到子分组。我还为每个帖子获得多行(这很明显，因为我添加了第二个分组)

非常感谢您的专业知识和帮助!

Answer 1

下面的查询应该适合你

SELECT  P.partner
        ,COUNT(DISTINCT P.postid) AS nb_post
        ,COUNT(DISTINCT P.postvariationid) AS nb_postvariation
        ,COUNT(DISTINCT P.postvariationid) / COUNT(DISTINCT P.postid) AS avg_postvariation
FROM    posts P 
        INNER JOIN (
            SELECT  P.postid
                    ,COUNT(DISTINCT P.postvariationid) AS nb_postvariation
            FROM posts P
            GROUP BY P.postid
            HAVING COUNT(DISTINCT P.postvariationid) > 1
        ) Q
        ON P.postid = Q.postid
GROUP BY P.partner
ORDER BY P.id DESC;

SQLFiddle Demo

编辑:试试这个只访问主表一次的优化查询。但是，此查询假定 nb_postvariation 的值不会在 postid

的多个值之间重复

另外，为了获得更好的性能，您可以在 posts(partner,postid,postvariationid)

上添加覆盖索引

SELECT  P.partner
        ,SUM(nb_post) AS nb_post
        ,SUM(nb_postvariation) AS nb_postvariation
        ,SUM(nb_postvariation)/SUM(nb_post) AS nb_postvariation
FROM    (
        SELECT  P.partner,P.postid
                ,COUNT(DISTINCT P.postid) AS nb_post
                ,COUNT(DISTINCT P.postvariationid) AS nb_postvariation
        FROM    posts P  
        GROUP BY P.partner,P.postid 
        HAVING COUNT(DISTINCT P.postvariationid) > 1
       )P;

SQLFiddle Demo

希望这对您有所帮助。