如何为GROUP BY查询中的每个组返回某个列的最频繁值？

debugcn 发表于 Dev

dodecafonico

我有这个示例表：

  sort_order  product      color    productid   price
  ----------  -------      ------   ---------   -----
      1       bicycle       red      2573257     50
      2       bicycle       red      0983989     40
      3       bicycle       red      2093802     45
      4       bicycle       blue     9283409     55
      5       bicycle       blue     3982734     60
      1       teddy bear    brown    9847598     20
      2       teddy bear    black    3975897     25
      3       teddy bear    white    2983428     30
      4       teddy bear    brown    3984939     35
      5       teddy bear    brown    0923842     30
      1       tricycle      pink     2356235     25
      2       tricycle      blue     2394823     30
      3       tricycle      blue     9338832     35
      4       tricycle      pink     2383939     30
      5       tricycle      blue     3982982     35

我想查询一个返回产品，平均价格和最常见颜色的查询。

因此，本例中的查询将返回：

product      most_frequent_color     average_price
-------      -------------------     -------------
bicycle      red                     50
teddy bear   brown                   28
tricycle     blue                    31

仅按产品分组并使用avg（price），平均部分似乎很容易，但是我该如何解决最频繁的颜色部分呢？

这是我到目前为止可以弄清楚的查询，但是我不知道如何为每个组获取most_frequent_color：

SELECT product, avg(price) AS average_price from products
WHERE sort_order <= 5
GROUP BY product

在我的真实世界表中，通常每个组的行数都比我感兴趣的多，因此我只能使用sort_order字段获得有限的行数

对于在“颜色”的所有行中都为null或具有一种以上最频繁的颜色的稀有组，我想在返回的most_frequent_color列中返回null

感谢您对此的任何帮助！

议会

您可以在SELECT子句中使用其他查询，以对相同数据有效地执行汇总查询：

SELECT   t.product,
         Avg ( t.price ) AS average_price,
         (
                  SELECT   IF ( Count(*) = t4.count, NULL, t2.color ) 'color'
                  FROM     products t2
                  JOIN
                           (
                                    SELECT   t3.product,
                                             t3.color,
                                             count(*) 'count'
                                    FROM     products t3
                                    GROUP BY t3.product ,
                                             t3.color
                                    ORDER BY count(*) DESC
                           ) t4
                  ON       t2.product = t4.product
                           AND t2.color <> t4.color
                  WHERE    t2.product = t.product
                  GROUP BY t2.color
                  ORDER BY count(*) DESC limit 1
         ) AS most_frequent_color
FROM     products t
WHERE    t.sort_order <= 5
GROUP BY t.product

因此，我们链接products使用product列的第二个副本，在列表顶部选择最频繁使用的每种颜色（针对该产品）的计数，然后仅选择第一行-因此该产品的最频繁使用的颜色值。

这与内联视图（放置在FROM查询的子句中）不同。

注意：这将与MySQL一起使用，但是与数据库无关。

更新：现在检查具有相同频率的一种以上颜色，并返回null。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。