In SQL, window functions are a powerful tool for producing calculations that are dependent on data in other rows.
They allow you to attach data to the record without having to perform complex aggregations and joins.
In general, window functions are performed across a partition of records and return some computation about the record relative to the partition overall.
Currently, Arroyo supports the
ROW_NUMBER() window function,
and requires the partition include a time window so that there is a guarantee of completeness.
For example, to calculate the most common auction in a dataset over the last 10 minutes every second, you could use the following query:
SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY window ORDER BY count DESC) as row_number, auction, count FROM ( SELECT auction, hop(INTERVAL '1' second, INTERVAL '10' minute ) as window, count(*) as count FROM bids GROUP BY 1, 2)) where row_number = 1