Group by range of values and remove duplicate rows in Hql / Sql
I have example data like this. What I need is that for every two rows where the difference in values is 0 <= A
<= 1 (for example 0 <= row1.A - row1.B <=1
) reduces the duplicate values by B
and C
, keep a smaller A
row. Can this be done in Hql without using defiend user functions?
A B C
1 2 3
4 2 3
2 1 1
3 1 1
2 2 3
3 0 2
Results:
A B C
1 2 3
2 1 1
3 0 2
4 2 3
+3
source to share
1 answer
If I understand correctly, you need lines where the value of A in line B / C differs from the previous value of A by at least 2.
You can do it using lag()
and then some logic:
select t.*
from (select t.*, lag(A) over (partition by B, C order by A) as prev_a
from t
) t
where prev_a is null or A > prev_a + 1;
+2
source to share