mysql - 如何在 GROUP BY 查询中为每个组返回特定​​列的最频繁值？

Question

我有这个示例表:

  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_colum 列中返回 null

感谢您对此的任何帮助!

Answer 1

您可以在 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

因此我们使用 product 列链接 products 的第二个副本，选择列表顶部出现频率最高的每种颜色(对于该产品)的计数，然后只取第一行 - 因此是该产品最常见的颜色值。

这与内联 View (位于查询的 FROM 子句中)不同。

注意:这将适用于 MySQL，但它与数据库无关。

更新:现在检查超过 1 种具有相同频率的颜色并返回 null。