根据另一列的最小值选择一列 [英] selecting a column based on a minimum value of another column

问题描述

我有下表。

test_type |  brand  | model  | band | firmware_version | avg_throughput
-----------+---------+--------+------+-----------------+----------------
 1client   | Linksys | N600   | 5ghz | 1               |          66.94
 1client   | Linksys | N600   | 5ghz | 2               |          94.98
 1client   | Linksys | N600   | 5ghz | 4               |         132.40
 1client   | Linksys | EA6500 | 5ghz | 1               |         216.46
 1client   | Linksys | EA6500 | 5ghz | 2               |         176.79
 1client   | Linksys | EA6500 | 5ghz | 4               |         191.44

我想选择 avg_throughput firmware_version 版本最低的模型。

I'd like to select the avg_throughput of each model that has the lowest firmware_version.

当我执行 SELECT test_type，model，min（firmware_version）FORM temp_table GROUP BY test_type，model 时，我得到了想要的，但是一旦添加 avg_throughput 列，它还要求我将其添加到GROUP BY子句，当我只需要 avg_throughput 时，它就返回所有行。每个模型类型的最低 firmware_version 。

When I do SELECT test_type, model, min(firmware_version) FORM temp_table GROUP BY test_type, model I get what I want but once I add the avg_throughput column it requires me to also add it to the GROUP BY clause which makes it return all the rows when all I need is only the avg_throughput for the lowest firmware_version for each model type.

推荐答案

在标准SQL中，可以使用窗口函数来完成

In standard SQL this can be done using a window function

select test_type, model, firmware_version, avg_throughput
from (
  select test_type, model, firmware_version, avg_throughput, 
         min(firmware_version) over (partition by test_type, model) as min_firmware
  from temp_table
) t
where firmware_version = min_firmware;

Postgres的区别于运算符通常比具有窗口函数的相应解决方案要快：

Postgres however has the distinct on operator which is usually faster than the corresponding solution with a window function:

select distinct on (test_type, model) 
       test_type, model, firmware_version, avg_throughput
from temp_table
order by test_type, model, firmware_version;

SQLFiddle示例： http://sqlfiddle.com/#!15/563bd/1

SQLFiddle example: http://sqlfiddle.com/#!15/563bd/1

这篇关于根据另一列的最小值选择一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！