mysql - 查找给定工作日的最大出现次数

Question

我有一个包含测试数据的表，其中包含我正在使用的两列:“日期”和“象限”。象限列是一个枚举值，有 4 个可能的值(1、2、3 或 4)。

一周中的每一天(包含 4 个月的日期)都有一个与其关联的枚举值。

枚举值在任何一天都会定期出现多次。

我正在尝试计算整个表中给定工作日的象限枚举值最常见的出现次数。

例如，星期一出现了 20 个枚举值“1”和 19 个枚举值“2”。

我只想看看最常见的，在本例中是“1”。

类似这样的事情:

Monday.........1
Tuesday........3
Wednesday......4
Thursday.......1
Friday..........2

到目前为止，我已经尝试了以下方法，但它只给出了每个工作日象限列具有枚举值的总次数。

SELECT DAYNAME(Date) AS Weekday
     , COUNT(quadrant) AS 'Most Likey Damaged Quadrant' 
  FROM damage_tbl 
 WHERE quadrant IS NOT NULL 
 GROUP 
    BY quadrant 
 ORDER
    BY WEEKDAY

这里是该表和 rextester 上所有数据的链接:https://rextester.com/UQDSB56921

Answer 1

您可以查看this post查看实现此目的的不同方法(在 MySQL 中获取 argmax)

其中一种方法(在评论中提到)是自反连接:

SELECT a.Weekday, a.quadrant FROM (
    SELECT DAYNAME(Date) AS Weekday, quadrant, COUNT(*) AS 'cnt' 
      FROM damage_tbl 
     WHERE quadrant IS NOT NULL 
     GROUP 
        BY WEEKDAY, quadrant
     ORDER
        BY WEEKDAY, count(*) desc ) a LEFT JOIN (
    SELECT DAYNAME(Date) AS Weekday, quadrant, COUNT(*) AS 'cnt' 
      FROM damage_tbl 
     WHERE quadrant IS NOT NULL 
     GROUP 
        BY WEEKDAY, quadrant
     ORDER
        BY WEEKDAY, count(*) desc ) b 
ON a.Weekday = b.Weekday and a.cnt < b.cnt  
WHERE b.Weekday is null