hi!! I have a question about querying the database, We are working with time series and we want to define different kind of time windows in our queries. Are windowing options implemented out of the box or do we need to implement them by ourselves?

Hi @User are you referring to

window

functions in general? If so, Pinot doesn't have that yet today. But if are talking about rollups on different time granularities that can be done. Could you give an example query of what you are looking for?

@User I’m talking about the current supported

window

options in general (tumbling, hopping, sliding or cascading) in the same way that is supported on Calcite SQL https://calcite.apache.org/docs/stream.html#tumbling-windows. On the other hand, being more specific we want to implement for instance a rolling window where we have a span of time that we move across two different points in time.

We don't have that yet, we have had some initial conversations on adding those. Hopefully we can put it on the roadmap soon.

@User Thanks for your quick answer, using the current query support is it possible to implement the rolling window query that I commented by pinot itself or do we need to implement it manually for instance performing multiple queries moving the window manually?

Would help to see the actual query you have in mind. Also cc @User for any thoughts

let see if this simple example can help: Suppose we have records of selling orders each one with it own timestamp and the order amount. We want to compute the

sum(amount)

that we are receiving per hour during last 24 hours In this example we have a span of one hour and we move the window on steps of 1 hour (00am-01am, 01am-02am, … and so on) during the last 24 hours and for each step we compute the

sum(amount)

thats a simple group by query and can be achieved in Pinot

Is it a group by? How do you implement the moving window with the group by?

select toHour(time) as hour, sum(amount) from T where time < (now() - 24 * 60 *60 *1000) group by hour

@User yup the tumbling window can be done with a group by. Is there a way to implement a sliding window in Pinot? Query would be similar to the one listed here https://calcite.apache.org/docs/stream.html#sliding-windows

Sliding window is also possible with a udf but advanced window functions will get complicated.. we are planning to add match recognize which will allow more complex functions