CODEWITHSUNDEEP
sqlamazon-athena
lea
lea

Reputation: 143

SYNTAX_ERROR: line 1:1: DISTINCT in window function parameters not yet supported

I have a table below :

Blockquote

I am writing a code below to get the next timestamp using T5 and then calculate the duration. However for the last record where T5 and T6 are equal I want to fill in the average of the previous durations. However I am getting an error in doing so. What am i doing wrong?

    SELECT
T1,
T2,
T3,
T4,
T5,
T6,
case 
when T5=T6 then sum(date_diff('second',T5,T6)) over (partition by T1, T2, T3, 
date(T5)) / (count(DISTINCT T6)over (partition by T1, T2, T3, date(T6) )) 
else date_diff('second',T5,T6) end as duration
from
(
 SELECT T1,
T2,
T3,
T4,
T5,
lead(T5, 1, T5) over (partition by T1, T2, T3, date(T5) order by T5) as T6
FROM TABLE_X 
)

Upvotes: 0

Views: 1990

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can emulate count(*) distinct with this logic:

(dense_rank() over (partition by T1, T2, T3, date(T6) order by T6 asc) +
 dense_rank() over (partition by T1, T2, T3, date(T6) order by T6 desc) -
 1
)

That is, the sum of the dense ranks in ascending and descending order is the number of distinct values in the partition.

Upvotes: 1

Related Questions