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显示最大intraf_mbps发生的date_gmt？

- Ben Matheja

---

如果max_in在另一天而max_out在另一天，会怎么样？ - juergen d

这很可能是真的。在这一点上，只要获得一个正确的时间戳就足够了。 - Ben Matheja

可能没有一个唯一的“最大数据”，也许有几个日期具有相同的最大流量。您可以在相关子查询中获取最大流量，并筛选出具有该流量的记录。这是一个不太理想的解决方案，但是MySQL也不支持分析函数或排名。 - dani herrera

这暂时是可行的方法，我该如何构建这样的子查询？ - Ben Matheja

Answer 1

您的问题与mysql hidden fields有关：

MySQL扩展了GROUP BY的使用，以便选择列表可以引用未在GROUP BY子句中命名的非聚合列。这意味着在MySQL中前面的查询是合法的。您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。然而，只有当每个组中未在GROUP BY中命名的非聚合列的所有值都相同时，此功能才有用。 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
      )

当然，您可以采用更加索引友好的方法或避免相关子查询来解决问题。

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

- dani herrera

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

希望这能帮到您。

- st0kes