Convert SQL Lag query to Druid

[Jaytimbadia7]

Hey Druid!

We have a query written in SQL which contains LAG function. We need that query in Druid SQL. Can you please lead us?

Example SQL query:

SELECT
  time_col,
  id,
  (time_col - lag (time_col, 1) over ( partition by id order by time_col)) as time_elapsed
FROM datatable

Can you please let us know its Druid conversion?

[Jaytimbadia7]

Also, is Druid insertion of data from kafka in order or is it random insertion with no proper row ordering.
If that's the case, Lag/Lead functions won't be achievable, right?

https://groups.google.com/g/druid-user/c/5kbu34ONKTA @petermarshallio

[abhishekagarwal87]

Druid doesn't support window functions yet. There is work in progress here #13458.

[Jaytimbadia7]

That's good to hear. But is data ingestion to druid non-orderly.
Will Lag function work with development in work?

[Jaytimbadia7]

Will this simple query work in druid?

Below is SQL query. Need Druid Equivalent.

SELECT
  (time_col - lag (time_col, 1)  as time_elapsed
FROM datatable

Will it support in future since data ingestion is non-orderly? Any suggestions?

[alvaro-ponce]

Hi!
I'm interested in this kind of queries too.
As I can see, the PR mentioned above is closed, but I can not easily find if this functionality is finally added in last Druid Version or if it will be added in the next one.

Can you @abhishekagarwal87 give us some information please?

Thank you

[abhishekagarwal87]

It will be available in next release (26.0.0). If you want to try it out locally, you can download the source code from master branch and build a distribution locally. https://github.com/apache/druid/blob/master/docs/development/build.md

[github-actions]

This issue has been marked as stale due to 280 days of inactivity.
It will be closed in 4 weeks if no further activity occurs. If this issue is still
relevant, please simply write any comment. Even if closed, you can still revive the
issue at any time or discuss it on the dev@druid.apache.org list.
Thank you for your contributions.

[github-actions]

This issue has been closed due to lack of activity. If you think that
is incorrect, or the issue requires additional review, you can revive the issue at
any time.