How to read only two rows at a time by ordering it

Analytics
Highlighted
Enthusiast

How to read only two rows at a time by ordering it

HOw could I take an average of two values by arranging the data in ascending order using SQL. For example

For the Col "Var1" with data

TrackId Var1
1 1.2
2 1.1
3 1.5
4 1.6
5 1.3

The result should be first order the sorted data as var1=[1.1, 1.2, 1.3, 1.5, 1.6] and then take an average of two values, i.e.,
The result will be evaluated as:

Result
-------------
(1.1+1.2)/2
(1.2+1.3)/2
(1.3+1.5)/2
(1.5+1.6)/2

Regards,

Prakash
2 REPLIES

Re: How to read only two rows at a time by ordering it

You can try using ordered analytical function ie AVG with OVER clause ie

AVG (var1) over (order by var1 ROWS BETWEEN current row PRECEDING AND
1 FOLLOWING)
Fan

Re: How to read only two rows at a time by ordering it

I'm trying to rank calls through our IVR based on the number of calls that occurred before it in the previous 30 days (ie is this the 1st, 2nd, 3rd .... call in the last 30 days?) 

I can do this for a fixed 30 day window, but I need to rank calls wihtin a rolling 30 day window over a long period of time.  I've tried using the RANK () OVER(PARTITION .... approach, however it will not accept a variable partition.

SELECT

call_ID ,

end_dt,

end_tm,

CUST_TN,

RANK() OVER (PARTITION BY BTN, [Rolling 30 day partition here] ORDER BY end_dt, end_tm ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) Prev_Rec

FROM ivr.vivr_calls

WHERE end_dt BETWEEN '2012-09-01' AND '2012-10-31'

QUALIFY end_dt BETWEEN '2012-10-01' AND '2012-10-31'

Any ideas?

Thanks  - Steve