Grouping statistics by periods

[devopg]

Hello. I’m a newbie in Materialize . I am inspired by this software and I’m going to learn all the documentation 😅

I need to make statistics on periods (day|week|month|year) for example like this (but not the percentages , but the values):

What is the optimal query I should make ?
I didn't find the LAG, LEAD function for materialize views in the documentation to take the last value of last month.

[bobbyiliev]

Hi there @devopg,

We did have a tracking issue for adding support forLEAD() and LAG() here: #10601

This is already fixed in #11891, #11785 but note that those will be available in future releases.

In the meantime as suggested here, you can use a temporal filter to create a view that has a sliding window of the last 24 hours of data and etc. this temporal filter will evolve continuously, and keep a finite amount of state, while elegantly sliding with time.

We've also had a similar discussion regarding window functions like LAG() and LEAD() in our community Slack here:

Window functions are difficult in incremental systems because one update can produce a huge amount of changes in output. You can get the same functionality as many of the window functions using an idiom called "Top K by Group" documented here https://materialize.com/docs/guides/top-k/

Here is also another interesting read that you might find helpful for your usecase:

https://materialize.com/slicing-up-temporal-aggregates-in-materialize/

Hope that this helps!

[devopg]

okey cool , will be w8 future releases

[bobbyiliev]

Hi there @devopg,

The lead/lag functions are now available in v0.26.3. For the complete release notes you can visit the documentation here:

https://materialize.com/docs/release-notes/#v0.26.3