An aggregate function that returns the minimum value from a set of numbers. Opposite of the
MAX function. Its single argument can be numeric column, or the numeric result of a function
or expression applied to the column value. Rows with a NULL value for the specified column
are ignored. If the table is empty, or all the values supplied to MIN are
NULL, MIN returns NULL.
Syntax:
MIN([DISTINCT | ALL] expression) [OVER (analytic_clause)]
When the query contains a GROUP BY clause, returns one value for each combination of
grouping values.
Restrictions: In Impala 2.0 and higher, this function can be used as an analytic
function, but with restrictions on any window clause. For MAX() and
MIN(), the window clause is only allowed if the start bound is
UNBOUNDED PRECEDING.
Return type: Same as the input value, except for CHAR and
VARCHAR arguments which produce a STRING result
Usage notes:
If you frequently run aggregate functions such as MIN(),
MAX(), and COUNT(DISTINCT) on partition key columns,
consider enabling the OPTIMIZE_PARTITION_KEY_SCANS query option, which
optimizes such queries. This feature is available in Impala 2.5
and higher. See OPTIMIZE_PARTITION_KEY_SCANS Query Option (Impala 2.5 or higher only) for the
kinds of queries that this option applies to, and slight differences in how partitions
are evaluated when this query option is enabled.
Complex type considerations:
To access a column with a complex type (ARRAY, STRUCT,
or MAP) in an aggregation function, you unpack the individual elements
using join notation in the query, and then apply the function to the final scalar item,
field, key, or value at the bottom of any nested type hierarchy in the column. See
Complex Types (Impala 2.3 or higher only) for details about using
complex types in Impala.
ARRAY of
STRUCT items). The array is unpacked inside the query using join
notation. The array elements are referenced using the ITEM
pseudocolumn, and the structure fields inside the array elements are referenced using
dot notation. Numeric values such as SUM() and AVG()
are computed using the numeric R_NATIONKEY field, and the
general-purpose MAX() and MIN() values are computed
from the string N_NAME field.
describe region;
+-------------+-------------------------+---------+
| name | type | comment |
+-------------+-------------------------+---------+
| r_regionkey | smallint | |
| r_name | string | |
| r_comment | string | |
| r_nations | array<struct< | |
| | n_nationkey:smallint, | |
| | n_name:string, | |
| | n_comment:string | |
| | >> | |
+-------------+-------------------------+---------+
select r_name, r_nations.item.n_nationkey
from region, region.r_nations as r_nations
order by r_name, r_nations.item.n_nationkey;
+-------------+------------------+
| r_name | item.n_nationkey |
+-------------+------------------+
| AFRICA | 0 |
| AFRICA | 5 |
| AFRICA | 14 |
| AFRICA | 15 |
| AFRICA | 16 |
| AMERICA | 1 |
| AMERICA | 2 |
| AMERICA | 3 |
| AMERICA | 17 |
| AMERICA | 24 |
| ASIA | 8 |
| ASIA | 9 |
| ASIA | 12 |
| ASIA | 18 |
| ASIA | 21 |
| EUROPE | 6 |
| EUROPE | 7 |
| EUROPE | 19 |
| EUROPE | 22 |
| EUROPE | 23 |
| MIDDLE EAST | 4 |
| MIDDLE EAST | 10 |
| MIDDLE EAST | 11 |
| MIDDLE EAST | 13 |
| MIDDLE EAST | 20 |
+-------------+------------------+
select
r_name,
count(r_nations.item.n_nationkey) as count,
sum(r_nations.item.n_nationkey) as sum,
avg(r_nations.item.n_nationkey) as avg,
min(r_nations.item.n_name) as minimum,
max(r_nations.item.n_name) as maximum,
ndv(r_nations.item.n_nationkey) as distinct_vals
from
region, region.r_nations as r_nations
group by r_name
order by r_name;
+-------------+-------+-----+------+-----------+----------------+---------------+
| r_name | count | sum | avg | minimum | maximum | distinct_vals |
+-------------+-------+-----+------+-----------+----------------+---------------+
| AFRICA | 5 | 50 | 10 | ALGERIA | MOZAMBIQUE | 5 |
| AMERICA | 5 | 47 | 9.4 | ARGENTINA | UNITED STATES | 5 |
| ASIA | 5 | 68 | 13.6 | CHINA | VIETNAM | 5 |
| EUROPE | 5 | 77 | 15.4 | FRANCE | UNITED KINGDOM | 5 |
| MIDDLE EAST | 5 | 58 | 11.6 | EGYPT | SAUDI ARABIA | 5 |
+-------------+-------+-----+------+-----------+----------------+---------------+
Examples:
-- Find the smallest value for this column in the table.
select min(c1) from t1;
-- Find the smallest value for this column from a subset of the table.
select min(c1) from t1 where month = 'January' and year = '2013';
-- Find the smallest value from a set of numeric function results.
select min(length(s)) from t1;
-- Can also be used in combination with DISTINCT and/or GROUP BY.
-- Return more than one result.
select month, year, min(purchase_price) from store_stats group by month, year;
-- Filter the input to eliminate duplicates before performing the calculation.
select min(distinct x) from t1;
MIN() in an analytic context. They use a table
containing integers from 1 to 10. Notice how the MIN() is reported for each input value, as
opposed to the GROUP BY clause which condenses the result set.
select x, property, min(x) over (partition by property) as min from int_t where property in ('odd','even');
+----+----------+-----+
| x | property | min |
+----+----------+-----+
| 2 | even | 2 |
| 4 | even | 2 |
| 6 | even | 2 |
| 8 | even | 2 |
| 10 | even | 2 |
| 1 | odd | 1 |
| 3 | odd | 1 |
| 5 | odd | 1 |
| 7 | odd | 1 |
| 9 | odd | 1 |
+----+----------+-----+
ORDER BY clause lets you experiment with results that are cumulative or apply to a moving
set of rows (the "window"). The following examples use MIN() in an analytic context
(that is, with an OVER() clause) to display the smallest value of X
encountered up to each row in the result set. The examples use two columns in the ORDER BY
clause to produce a sequence of values that rises and falls, to illustrate how the MIN()
result only decreases or stays the same throughout each partition within the result set.
The basic ORDER BY x clause implicitly
activates a window clause of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,
which is effectively the same as ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,
therefore all of these examples produce the same results:
select x, property, min(x) over (order by property, x desc) as 'minimum to this point'
from int_t where property in ('prime','square');
+---+----------+-----------------------+
| x | property | minimum to this point |
+---+----------+-----------------------+
| 7 | prime | 7 |
| 5 | prime | 5 |
| 3 | prime | 3 |
| 2 | prime | 2 |
| 9 | square | 2 |
| 4 | square | 2 |
| 1 | square | 1 |
+---+----------+-----------------------+
select x, property,
min(x) over
(
order by property, x desc
range between unbounded preceding and current row
) as 'minimum to this point'
from int_t where property in ('prime','square');
+---+----------+-----------------------+
| x | property | minimum to this point |
+---+----------+-----------------------+
| 7 | prime | 7 |
| 5 | prime | 5 |
| 3 | prime | 3 |
| 2 | prime | 2 |
| 9 | square | 2 |
| 4 | square | 2 |
| 1 | square | 1 |
+---+----------+-----------------------+
select x, property,
min(x) over
(
order by property, x desc
rows between unbounded preceding and current row
) as 'minimum to this point'
from int_t where property in ('prime','square');
+---+----------+-----------------------+
| x | property | minimum to this point |
+---+----------+-----------------------+
| 7 | prime | 7 |
| 5 | prime | 5 |
| 3 | prime | 3 |
| 2 | prime | 2 |
| 9 | square | 2 |
| 4 | square | 2 |
| 1 | square | 1 |
+---+----------+-----------------------+
RANGE syntax, this type of
moving window is possible with the ROWS BETWEEN clause but not the RANGE BETWEEN clause.
Because of an extra Impala restriction on the MAX() and MIN() functions in an
analytic context, the lower bound must be UNBOUNDED PRECEDING.
select x, property,
min(x) over
(
order by property, x desc
rows between unbounded preceding and 1 following
) as 'local minimum'
from int_t where property in ('prime','square');
+---+----------+---------------+
| x | property | local minimum |
+---+----------+---------------+
| 7 | prime | 5 |
| 5 | prime | 3 |
| 3 | prime | 2 |
| 2 | prime | 2 |
| 9 | square | 2 |
| 4 | square | 1 |
| 1 | square | 1 |
+---+----------+---------------+
-- Doesn't work because of syntax restriction on RANGE clause.
select x, property,
min(x) over
(
order by property, x desc
range between unbounded preceding and 1 following
) as 'local minimum'
from int_t where property in ('prime','square');
ERROR: AnalysisException: RANGE is only supported with both the lower and upper bounds UNBOUNDED or one UNBOUNDED and the other CURRENT ROW.
Related information: