mysql - 使用 max() 查询表会产生奇怪的结果

Question

我有一个结构如下的表格

id, href(string), active_users(integer), measured(datetime)

此表包含给定时间给定站点的活跃用户数量的测量值(每小时，即有历史数据，并且您可以有多个具有相同 href 的行，但它将有一个测量值)。

我想获取给定时间间隔内每个 href 的最大活跃用户数以及何时达到峰值(即，在本月，告诉我每个 href 何时达到活跃用户峰值以及何时)。

我最初编写的查询如下所示:

SELECT href,
       max(active_users),
       measured AS peak_date
FROM users_peak
WHERE measured >= DATE('2016-09-07')            
      AND measured <= DATE('2016-10-07') 
GROUP BY href

问题在于，结果中的日期不是达到该峰值的日期。如果我进行手动检查( SELECT * FROM users_peak WHERE href='one_of_the_hrefs_from_the_result' AND measured >= DATE('2016-09-07') AND measured <= DATE('2016-10-07') 并按 active_users 值排序)，记录中对应于峰值的日期会有所不同。当我将查询修改为如下所示时:

SELECT od.href,
       od.active_users,
       oddate.measured AS measured
     FROM users_peak AS oddate
     JOIN
         (SELECT href,
                 max(active_users) AS active_users
          FROM users_peak
          WHERE measured >= DATE('2016-09-07')
            AND measured <= DATE('2016-10-07')
          GROUP BY href) AS od ON od.href = oddate.href
     WHERE oddate.active_users = od.active_users
         AND measured >= DATE('2016-09-07')
         AND measured <= DATE('2016-10-07')
     GROUP BY od.href

它返回正确的结果。为什么初始查询返回不对应的日期？

Answer 1

您真正想要的是为每个带有峰值的 HREF 获取一行

这可以通过保留您的查询来完成，但添加一个 INNER JOIN 子句，该子句只会返回具有最大值的行(因此消除/过滤掉不代表该值的行)最大限度).

SELECT href,
       active_users,
       measured AS peak_date
FROM users_peak a
INNER JOIN (
    SELECT href, MAX(active_users) max
    FROM users_peak
    GROUP BY href
) b ON a.href = b.href AND a.max = b.max
WHERE 
    measured >= DATE('2016-09-07')            
    AND measured <= DATE('2016-10-07')