minSimpleState
Description
The SimpleState combinator can be applied to the min
function to return the minimum value across all input values. It returns the
result with type SimpleAggregateState.
Example Usage
Let's look at a practical example using a table that tracks daily temperature
readings. For each location, we want to maintain the lowest temperature recorded.
Using the SimpleAggregateFunction type with min automatically updates the
stored value when a lower temperature is encountered.
Create the source table for raw temperature readings:
Create the aggregate table that will store the min temperatures:
Create an Incremental Materialized View that will act as an insert trigger for inserted data and maintains the minimum, maximum temperatures per location.
Insert some initial temperature readings:
These readings are automatically processed by the Materialized View. Let's check the current state:
Insert some more data:
View the updated extremes after new data:
Notice above that we have two inserted values for each location. This is because
parts have not yet been merged (and aggregated by AggregatingMergeTree). To get
the final result from the partial states we need to add a GROUP BY:
We now get the expected result: