This message was deleted.

Hi Sriramdas, yes Window Functions are being implemented in Druid, currently in an undocumented experimental state. To try them out, set query context

windowsAreForClosers:true

Please note that this implementation requires that the query contains a Group By ... which it looks like yours does. Let us know if you have any issues with this.

@Hellmar Becker gives some tips and walkthroughs for window functions (but not RANK in particular): https://blog.hellmar-becker.de/2023/03/26/druid-26-sneak-peek-window-functions/

Currently we are using druid 25, I dont think that feature would work here. Do you suggest any other possible ways ?

There’s also usually a way to emulate window functions using self joins. I have a few examples here https://blog.hellmar-becker.de/2022/11/05/druid-data-cookbook-cumulative-sums-in-druid-sql/ but the real expert for this is @John Kowtko.

@John Kowtko could you please provide any recommendations here to solve the problem ?

Hi Sriramdas, here is an example of computing Rank on the wikipedia dataset using the self-join method:

with tmp as (
 select channel column1, flags column2, sum(added) count1, sum(deleted) count2
   from "wikipedia"
  group by 1, 2
)
 select a.column1, a.column2, a.count1, a.count2,
        sum(case when b.count1 > a.count1 then 1 else 0 end)+1 "rank"
   from tmp a 
   JOIN tmp b ON b.column1 = a.column1 
  group by a.column1, a.column2, a.count1, a.count2
  order by 1, 5

there is a variation of this when you add the inequality filter in a WHERE clause, the difference being in how you treat ties:

with tmp as (
 select channel column1, flags column2, sum(added) count1, sum(deleted) count2
   from "wikipedia"
  group by 1, 2
)
 select a.column1, a.column2, a.count1, a.count2,
        count(b.column1) "rank"
   from tmp a 
   JOIN tmp b ON b.column1 = a.column1 
  where b.count1 >= a.count1
  group by a.column1, a.column2, a.count1, a.count2
  order by 1, 5

You might use

>

instead of

>=

, and/or

count(*)+1

instead of just

count(*)

, depending on how you want the rank numbers to end up. Go ahead and try this, if you can't get to the exact result that you are looking for, please post what you have and we can go from there. Thanks. John

The experimental feature docs are in review: https://github.com/apache/druid/pull/14739

Thanks @John Kowtko for sharing the sample query. It helped me a lot.

Is there any way of persisting the cumulative sum of the window function as a new column to my table? Using this example https://github.com/apache/druid/pull/14739/files#diff-75198317a3fa944a4bfc7ce636924bfc67f02f00b51f0be4a86c914eb4371d44R191 , I want to be able to just say select cum_changes from wikipedia

MSQ might be able to do the reindexing, with a SELECT of all fields, plus the new one, to replace into the table. (Haven't tried, just think it might be possible.)

i think that should work too as long as MSQ support the window function

Not sure, but I wouldn't be surprised if it can't. But maybe one the SQL alternatives, as in Hellmar's blog post?

Hellmar’s blog post describe how we can get it in a query. I am looking for way to persist the cum as a new column so that subsequent query can just select the column with any agg, etc

Right, I thought that SQL might be usable in MSQ; not sure.

Hi All -- Yes, it can be done using MSQ SQL ingestion. Here's an example using wikipedia: First, I will prepare a simple version of the table containing the "Added" field which we want to sum up cumulatively by channel:

insert into wiki_cumulative select __time, channel, page, added from wikipedia partitioned by ALL

Then you can run the self-join query as a REPLACE/SELECT to add the cumulative field:

replace into wiki_cumulative overwrite all
 select a.channel, a.__time, a.page, a.added, sum(b.added) cumAdded 
   from wiki_cumulative a join wiki_cumulative b on a.channel = b.channel
  where b.__time <= a.__time
  group by 1, 2, 3, 4
partitioned by all

... and select out the results:

select channel, __time, page, added, cumAdded from wiki_cumulative group by 1, 2, 3, 4, 5 order by 1, 2

Results should look like in the screenshot below. (Fyi the group by in this select statement is added to allow ordering by channel first.)

Thanks!! Btw Does MSQ not support the window function ?

Hi Maytas, I just tried it and it looks like Window Fns are not supported yet in MSQ ... that may be one of the reasons they have not been released yet in alpha/experimental form. If you want to try yourself, here is the query I just tested with, which works fine against the sql-native engine (but not sql-msq-task):

select channel, __time, page, added, 
       sum(added) over (partition by channel order by __time rows unbounded preceding) cumAdded
  from "wikipedia"
 group by 1, 2, 3, 4

(... and corresponding results screenshot ...)

Thanks for getting back to me @John Kowtko The window fns would have made the query nicer compare to the self join. I’ll try the self join in MSQ for now. Thanks

Yes it will be nicer when the Window Functions are available. One other thing to keep in mind here is the join type ... by default MSQ will use the Broadcast join (

sqlJoinAlgorithm:broadcast

) ... but if the cardinality is high here and up hit up against broadcast limits on the self-join you may need to switch to

sortMerge

ref: https://druid.apache.org/docs/latest/multi-stage-query/reference/#joins