The min
function in ClickHouse is an aggregation function used to find the minimum value in a set of values. It's commonly used in GROUP BY queries to determine the lowest value within each group or across an entire column.
Syntax
min(column)
For official documentation, visit the ClickHouse Aggregation Functions page.
Example usage
SELECT
user_id,
min(order_total) AS lowest_order_amount
FROM orders
GROUP BY user_id;
This query will return the lowest order amount for each user.
Common issues
min
function ignores NULL values. If you need to consider NULL values, you might need to usecoalesce
or other NULL-handling techniques.- When used with floating-point numbers, be aware of potential precision issues.
Best practices
- Use
min
in combination with other aggregation functions likemax
,avg
, orcount
for more comprehensive analysis. - Consider using
argMin
if you need to find the row with the minimum value in a certain column.
Frequently Asked Questions
Q: Can min be used with string columns?
A: Yes, min
can be used with string columns. It will return the lexicographically smallest string.
Q: How does min handle NULL values?
A: The min
function ignores NULL values. If all values in the set are NULL, the result will also be NULL.
Q: Is there a way to get the minimum non-zero value?
A: Yes, you can use a combination of min
and where
clause: min(column) WHERE column != 0
.
Q: Can min be used in a window function?
A: Yes, min
can be used as a window function in ClickHouse, allowing you to find the minimum value within a specified window of rows.
Q: How does min perform with large datasets?
A: min
is generally very efficient, even with large datasets, as ClickHouse is optimized for such aggregations. However, performance can depend on data distribution and query complexity.