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
minfunction ignores NULL values. If you need to consider NULL values, you might need to usecoalesceor other NULL-handling techniques.- When used with floating-point numbers, be aware of potential precision issues.
Best practices
- Use
minin combination with other aggregation functions likemax,avg, orcountfor more comprehensive analysis. - Consider using
argMinif 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.
