oracle 获取 count(*) 列的 min()
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6259497/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting the min() of a count(*) column
提问by Mark Robinson
I have a table called Vehicle_Location containing the columns (and more):
我有一个名为 Vehicle_Location 的表，其中包含列（以及更多）：
ID NUMBER(10)
SEQUENCE_NUMBER NUMBER(10)
TIME DATE
and I'm trying to get the min/max/avg number of records per day per id.
我正在尝试获取每个 ID 每天的最小/最大/平均记录数。
So far, I have
到目前为止，我有
select id, to_char(time), count(*) as c
from vehicle_location
group by id, to_char(time), min having id = 16
which gives me:
这给了我：
ID TO_CHAR(TIME) COUNT(*)
---------------------- ------------- ----------------------
16 11-05-31 159
16 11-05-23 127
16 11-06-03 56
So I'd like to get the min/max/avg of the count(*) column. I am using Oracle as my RDBMS.
所以我想获得 count(*) 列的最小值/最大值/平均值。我使用 Oracle 作为我的 RDBMS。
回答by Matthew
I don't have an oracle station to test on but you should be able to just wrap the aggregator around your SELECTas a subquery/derived table/inline view
我没有要测试的 oracle 站，但您应该能够将聚合器SELECT作为子查询/派生表/内联视图包装在您的周围
So it would be (UNTESTED!!)
所以它会是（未经测试！！）
SELECT
AVG(s.c)
, MIN(s.c)
, MAX(s.c)
, s.ID
FROM
--Note this is just your query
(select id, to_char(time), count(*) as c from vehicle_location group by id, to_char(time), min having id = 16) as s
GROUP BY s.ID
Here's some reading on it:
http://www.devshed.com/c/a/Oracle/Inserting-SubQueries-in-SELECT-Statements-in-Oracle/3/
这里有一些关于它的阅读：http:
//www.devshed.com/c/a/Oracle/Inserting-SubQueries-in-SELECT-Statements-in-Oracle/3/
EDIT:Though normally it is a badidea to select boththe MINand MAXin a single query.
编辑：尽管通常这是一个糟糕的主意，选择两者中MIN，并MAX在一个单一的查询。
EDIT2:The min/max issue is related to how some RDBMS (including oracle) handle aggregations on indexed columns. It may not affect this particular query but the premise is that it's easy to use the index to find either the MINorthe MAXbut not both at the same time because any index may not be used effectively.
Here's some reading on it:
http://momendba.blogspot.com/2008/07/min-and-max-functions-in-single-query.html
EDIT2：最小/最大问题与某些 RDBMS（包括 oracle）如何处理索引列上的聚合有关。这可能不会影响这个特定的查询，但前提是，它很容易使用索引找到要么MIN还是在MAX，但不能同时在同一时间，因为任何指标可能无法有效地使用。
这里有一些关于它的阅读：http:
//momendba.blogspot.com/2008/07/min-and-max-functions-in-single-query.html
