Nested Aggregate Functions¶
It’s not possible to nest aggregate functions or to use an alias of an aggregate function that is being used in another aggregate function
SELECT max(avg(number)) as max_avg_number FROM my_datasource
SELECT avg(number) avg_number, max(avg_number) max_avg_number FROM my_datasource
Instead, you should use a subquery:
SELECT avg_number as number, max_number FROM ( SELECT avg(number) as avg_number, max(number) as max_number FROM numbers(10) )
SELECT max(avg_number) as number FROM ( SELECT avg(number) as avg_number, max(number) as max_number FROM numbers(10) )
Avoiding Full Scans¶
The less data you read in your queries, the faster they are. There are different strategies you could follow to avoid doing full scans in your queries:
Always filter first
Use indices by setting a proper
ENGINE_SORTING_KEYin the Data Source. - The column names should be the ones you will use for filtering - The order of the columns is important: from left to right ordered by relevance (the more important ones for filtering) and cardinality (less cardinality goes first)
You can improve performance by filtering using any column present in the
Memory limit reached¶
Sometimes, you reach the memory limit when running a query. This is usually because:
Lot of columns are used: try to reduce the amount of columns used in the query. This is not always possible, so try to change data types or merge some columns.
A cross join or some operation that generates a lot of rows: It might happen if the cross join is done with two tables with a large amount of rows, so try to rewrite the query to avoid the cross join.
GROUP BY: try to filter out rows before executing the
If the problem persists, just reach us at firstname.lastname@example.org to see if we can help you improving the query.