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

BAD: Error on using nested aggregate function
SELECT max(avg(number)) as max_avg_number FROM my_datasource
BAD: Error on using nested aggregate function with alias
SELECT avg(number) avg_number, max(avg_number) max_avg_number FROM my_datasource

Instead, you should use a subquery:

GOOD: Using aggregate functions in a subquery
SELECT
  avg_number as number,
  max_number
FROM (
  SELECT
    avg(number) as avg_number,
    max(number) as max_number
  FROM numbers(10)
)
GOOD: Nesting aggregate functions using a subquery
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_KEY in 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 ENGINE_SORTING_KEY in the WHERE clause

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.

  • A massive GROUP BY: try to filter out rows before executing the GROUP BY.

If the problem persists, just reach us at support@tinybird.co to see if we can help you improving the query.