(Oracle)Getting 25th number using interpolating

Question

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 have set two expectation. Can you pease clearly mention the expected output? Should your output will be a single number or a number per record? — Popeye, Jul 05 '20 at 07:37
1. What you described as your explanation of "interpolation" makes no sense. You need to explain it better. For example, what is (4+1)0.25? In particular, where does 0.25 come from? 2. Interpolation is for points in two dimensions: given several pairs (x_n, y_n) and one more value x, compute a corresponding value y by interpolation. Your problem doesn't seem to be of this nature - but please explain either way. — , Jul 05 '20 at 13:12
By the way, what's with the `quartile` tag? Do you mean "25th **percentile**" in your question? If so, what does that have to do with "interpolation"? — , Jul 05 '20 at 13:15
I refered to Method4 at https://en.wikipedia.org/wiki/Quartile — Soon, Jul 07 '20 at 10:21

Popeye · Accepted Answer · 2020-07-07T11:14:36.620

0

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)

edited Jul 07 '20 at 11:14

answered Jul 05 '20 at 07:44

Popeye

35,427
4
10
31

Thank you for your reply.The first code shows 3.25 4.25 5.5.Thank you. However, the second code shows ORA-00905.May I know where to fix? – Soon Jul 07 '20 at 10:19
Updated second query. check now!! – Popeye Jul 07 '20 at 11:14
Thank you for your reply, I've modified and it shows expected result – Soon Jul 12 '20 at 08:23

score 0 · Answer 2 · answered Jul 12 '20 at 08:23

WITH t AS (
SELECT 3 C1 FROM DUAL
UNION 
SELECT 4  FROM DUAL
UNION 
SELECT 5  FROM DUAL
UNION 
SELECT 7  FROM DUAL
)
SELECT rn,location, calculated 
FROM ( 
Select rn, c1, 
C1 +(Count(1) over () + 1)*0.25 
-trunc( (Count(1) over () + 1)*0.25 ) *(lead(c1) over (order by c1) - c1) as calculated, --
 trunc( (Count(1) over () + 1)*0.25 ) as location --
 From (Select t.*, Row_number() over (order by c1) rn From t) ) WHERE rn=location;

(Oracle)Getting 25th number using interpolating

2 Answers2