My goal is to get 25th number. For instance I have 4 row, such as 3,4,5 and 7. My goal is to get 1.25th(=(4+1)0.25). Expected result is 3.25 which is obtained by interpolating(3+0.25(4-3)).
I have tried as below. But is there any other efficient way?
WITH DATASET AS (
SELECT 3 C1 FROM DUAL
UNION
SELECT 4 FROM DUAL
UNION
SELECT 5 FROM DUAL
UNION
SELECT 7 FROM DUAL
)
SELECT
--RNK, C1, NEXTC1-C1, FIRSTQLOCAION, FIRSTQLOCAION-RNK, C1+(NEXTC1-C1)*(FIRSTQLOCAION-RNK)
C1+(NEXTC1-C1)*(FIRSTQLOCAION-RNK)
FROM(
SELECT C1,
LEAD(C1, 1) OVER (ORDER BY C1) as NEXTC1 ,
RANK() OVER (ORDER BY C1) AS RNK,
((SUM(1) OVER (PARTITION BY NULL)) +1) * 0.25 AS FIRSTQLOCAION
FROM DATASET
)
WHERE
FIRSTQLOCAION>=RNK AND FIRSTQLOCAION<=RNK+1;
You can use analytical function as follows:
Select c1,
c1 + (
(((Count(1) over () + 1)*0.25) - 1) * (lead(c1) over (order by c1) - c1)
) as calculated_number from
From your_table t
In this solution last record will have calculated value null as lead value will be null and you will have to adjust its value as per your requirement.
If your expectation is a single number from query then usw following:
Select min(c1) +
0.25 * (min(case when rn = 2 then c1 end)
- min(case when rn = 1 then c1 end)) as calculated_number
from
(Select t.*,
Row_number() over (order by c1)
From t)