SQL查询在发生日期获取最大值

Question

所以我们正在我们的部门做一些交通报告。因此，我们得到了一个名为traffic_report的表，它构建如下

╔════════════════╦═══════════╦═════════════════════╦═════════════╦═════════════╗
║    hostname    ║ interface ║      date_gmt       ║ intraf_mpbs ║ outraf_mbps ║
╠════════════════╬═══════════╬═════════════════════╬═════════════╬═════════════╣
║ my-machine.com ║ NIC-5     ║ 2013-09-18 09:55:00 ║          32 ║          22 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-17 08:25:00 ║          55 ║          72 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-16 05:12:00 ║          65 ║           2 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-15 04:46:00 ║          43 ║           5 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-14 12:02:00 ║          22 ║          21 ║
║ my-machine.com ║ NIC-5     ║ 2013-09-13 22:13:00 ║          66 ║          64 ║
╚════════════════╩═══════════╩═════════════════════╩═════════════╩═════════════╝

我想取最大的流量和流量在发生的日期。我的方法是这样的

SELECT hostname, interface, date_gmt, max(intraf_mbps) as max_in, max(outtraf_mbps) as max_out
FROM traffic_report
GROUP by hostname, interface

该方法生成这样的表

╔════════════════╦════════════╦═════════════════════╦════════╦═════════╗
║    hostname    ║ interface  ║      date_gmt       ║ max_in ║ max_out ║
╠════════════════╬════════════╬═════════════════════╬════════╬═════════╣
║ my-machine.com ║ NIC-5      ║ 2013-09-18 09:55:00 ║     66 ║      72 ║
╚════════════════╩════════════╩═════════════════════╩════════╩═════════╝

问题是，所显示的date_gmt只是输入到表的第一条记录的日期。

如何指示SQL显示最大( date_gmt )发生时的intraf_mbps？

Answer 1

你的问题是关于mysql隐藏字段

MySQL扩展了GROUP BY的用法，以便select列表可以引用GROUP BY子句中未命名的非聚合列。这意味着前面的查询在MySQL中是合法的。通过避免不必要的列排序和分组，您可以使用此特性获得更好的性能。但是，--这主要是当组中未命名的每个非聚合列中的所有值对于每个组都是相同的时候。

Mysql既没有对特性进行排序，也没有对分析函数进行排序，为了获得结果，一种可读的方法是，但是性能非常差的是：

SELECT hostname, 
       interface, 
       date_gmt, 
       intraf_mbps, 
       outtraf_mbps
FROM traffic_report T
where intraf_mbps + outtraf_mbps =
      ( select 
           max(intraf_mbps + outtraf_mbps) 
        FROM traffic_report T2
        WHERE T2.hostname = T.hostname and
              T2.interface = T.interface 
        GROUP by hostname, interface
      )

当然，您可以使用更友好的索引方法来处理解决方案，或者避免相关的子查询。

请注意，我已经加了两个费率，进出。根据你的需要调整解决方案。

Answer 2

这两种方法中的任何一种都应有效：

第一个查询返回与最大值匹配的行，因此，如果多个记录共享最大值或最小值，则可以返回多个行。

SELECT * from traffic_report 
WHERE intraf_mpbs = (SELECT MAX(intraf_mpbs) FROM traffic_report) 
   or outraf_mpbs = (SELECT MAX(outraf_mpbs) FROM traffic_report)

第二个查询返回更多的MI样式结果，如果需要添加其他字段。

SELECT "MAX IN TRAFFIC" AS stat_label,date_gmt AS stat_date, traffic_report.intraf_mpbs
  FROM traffic_report,(select MAX(intraf_mpbs) AS max_traf FROM traffic_report) as max_in
 WHERE traffic_report.intraf_mpbs = max_in.max_traf
 UNION
SELECT "MAX OUT TRAFFIC" AS stat_label,date_gmt AS stat_date, traffic_report.outraf_mpbs
  FROM traffic_report,(SELECT MAX(outraf_mpbs) AS max_traf FROM traffic_report) AS max_out
 WHERE traffic_report.outraf_mpbs = max_out.max_traf

希望这能有所帮助。