CODEWITHSUNDEEP
sqlhivehiveqlwindow-functionspartitioning
Talia9
Talia9

Reputation: 13

Hive lag window partition

Here is my table :

sensor_name, ext_value, int_value, growth
47ACXVMACSENS01, 238, 157, 1
47ACXVMACSENS01, 157, 256, 2
47ACXVMACSENS01, 895, 345, 3
47ACXVMACSENS01, 79, 861, 3
91DKCVMACSENS02, 904, 858, 1
91DKCVMACSENS02, 925, 588, 1
91DKCVMACSENS02, 15, 738, 1
91DKCVMACSENS02, 77, 38, 2

The 3 first columns (sensor_name, ext_value, int_value) are given data, the 4th column is a calculated column that I would like to have, this growth column is based on the value of columns (ext_value, int_value) for each group of sensor_name.

The result of the growth column is calculated as follow : for each group of sensor_name, the int_value of each row is compared with the ext_value of the previous row, if no previous row then its ext_value is 0, if the int_value of the current row is higher than the ext_value of the previous row then the growth value increases of 1. If the current int_value is lower than the previous row ext_value then the growth remains at the same value than the previous value of growth.

In the example above, 

for the very first row, 157 is compared with the previous row ext_value that doesn't exist so it's 0, 
157 > 0 then growth value increase of 1 from 0.
on the 2nd row, 256 > 238 then growth = 1+1=2
on the 3rd row, 345 > 159 then growth = 2+1=3
on the 4th row, 861 < 895 then growth remains at the same previous value, so 3.

then the logic is re-applied to the second set of sensor_name :
1st row, 858 > 0 (because there is now previous row for this sensor_name) then growth = 1
2nd row, 588 < 904 then growth = 1
3rd row, 738 < 925 then growth = 1
4th row, 38 > 15 then growth = 1+1=2

I've tried by using the lag window over sensor_name partition but it doesn't give me correct result until now.

How can I solve this ?

Upvotes: 1

Views: 884

Answers (1)

leftjoin
leftjoin

Reputation: 38290

Use lag to get previous ext_value, calculate growth flag and use running count to calculate growth. I added rcv_time column as you said in the comment:

with your_table as ( --use your table instead of this
select stack(8,
'47ACXVMACSENS01', 238, 157, '2019-11-01 10:10:01',
'47ACXVMACSENS01', 157, 256, '2019-11-01 10:10:02',
'47ACXVMACSENS01', 895, 345, '2019-11-01 10:10:03',
'47ACXVMACSENS01', 79, 861,  '2019-11-01 10:10:04',
'91DKCVMACSENS02', 904, 858, '2019-11-01 10:10:05',
'91DKCVMACSENS02', 925, 588, '2019-11-01 10:10:06',
'91DKCVMACSENS02', 15, 738,  '2019-11-01 10:10:07',
'91DKCVMACSENS02', 77, 38,  '2019-11-01 10:10:08'
) as (sensor_name, ext_value, int_value, rcv_time )
)

select sensor_name, ext_value, int_value, 
       count(case when int_value>prev_ext_value then true end) over(partition by sensor_name order by rcv_time) growth
from
(
select sensor_name, ext_value, int_value, rcv_time, 
       lag(ext_value,1,0) over(partition by sensor_name order by rcv_time) prev_ext_value
  from your_table
)s;

Result:

47ACXVMACSENS01 238     157     1
47ACXVMACSENS01 157     256     2
47ACXVMACSENS01 895     345     3
47ACXVMACSENS01 79      861     3
91DKCVMACSENS02 904     858     1
91DKCVMACSENS02 925     588     1
91DKCVMACSENS02 15      738     1
91DKCVMACSENS02 77      38      2

The result produced is exactly as in your example

Upvotes: 1

Related Questions