Tilda Database functions: Aggregates

<-- Common Helper Database Functions

Source code in PostgreSQL.helpers-start.sql

Aggregates

Postgres lacks some common aggregates out of the box. However, implementing them is easy!

First/Last

An implementation is provided for FIRST/LAST aggregates.

select company
     , FIRST("dateOfBirth") as "eldestDob"
     , LAST("dateOfBirth) "youngestDob"
  from Employee
group by company
order by "dateOfBirth" desc

Array Concatenation

Postgres support powerful arrays and so, when you use it, you may want to aggregate arrays together. The standard aggregate returns an array of arrays. This one returns a flat array that contains all the elements from the aggregated arrays.

select company
     , ARRAY_CAT_AGG(distinct "hireDates") as "hireDates"
  from Employee
group by company
order by "hireDates" desc