This message was deleted.
# generals
Slackbot
12/27/2023, 5:37 AMThis message was deleted.
m
Mike Sherman
12/27/2023, 5:21 PMHey @Rahul Sharma without your dataset running locally or in hosted Druid, it's difficult to respond. I would suggest a few next steps:
(1) to answer your question about Druid Window functions, try to get a simple use case working to validate that.
(2) provide a CSV file with your data so others can duplicate what you're seeing. I'm assuming that your query is valid Druid SQL?
j
John Kowtko
12/27/2023, 11:18 PMHi Rahul, what version of Druid are you on? I saw that framing offset bug in prior releases but haven't noticed it recently ...
s
Soumyava Das
12/28/2023, 4:12 AMCan you please share the version and the dataset too, the window functions are experimental so far but I want to understand if we can reproduce it on the master
m
Mike Sherman
12/28/2023, 2:14 PMYa, we had implemented windowing functionality on the Druid result set returned from the query but that had a few issues. I'd be very interested in how robust the window functions are in Druid these days...
j
John Kowtko
12/28/2023, 3:04 PMI have not seen any of these issues with Window Functions in the most recent releases. One of the latest fixes/enhancements was to allow order by "descending" in the over() clause ... so if this works for you then you may have a late enough version to get past the framing issue.
m
Mike Sherman
12/28/2023, 4:50 PMI ran the example Window function query
Copy code
SELECT FLOOR(__time TO DAY) AS event_time,
channel,
ABS(delta) AS change,
RANK() OVER w AS rank_value
FROM wikipedia
WHERE channel in ('#kk.wikipedia', '#lt.wikipedia')
AND '2016-06-28' > FLOOR(__time TO DAY) > '2016-06-26'
GROUP BY channel, ABS(delta), __time
WINDOW w AS (PARTITION BY channel ORDER BY ABS(delta) ASC)Mike Sherman
12/28/2023, 4:50 PMI usually start with something that works and make changes to isolate issues. Using the example dataset for your query can help others to assist you.
Mike Sherman
12/28/2023, 4:55 PMThere's also an example here that exercises all the built-in window functions: https://docs.imply.io/latest/druid/querying/sql-window-functions/
r
Rahul Sharma
01/05/2024, 7:23 AMSorry, for late replying
I have run the below query in druid, Postgres on the same data (sharing the csv file) but getting different output
Also sharing the snapshot of below query
Druid version - 28.0.0
WITH virtual_table as (select ("customers_city(orders)") as vsum_dim1,sum("shipping_cost(orders)") as mes_col
from us_ecom_orders_com101 group by 1 order by mes_col DESC)
select vsum_dim1,mes_col,sum(mes_col) OVER (order by vsum_dim1 rows between 1 FOLLOWING and 2 FOLLOWING) as mes_sum
from virtual_table order by 1