(Oracle)使用内插获得第25个数字

Question

我的目标是得到第25个号码。例如，我有4行，如3、4、5和7。我的目标是得到1.25(=(4+1)0.25)。预期结果为3.25，通过插值(3+0.25(4-3))得到。

我试过如下。但还有其他有效的方法吗？

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;

Answer 1

您可以使用如下分析功能：

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

在此解决方案中，最后一条记录将具有计算值null，而铅值将为null，您必须根据您的需求调整它的值。

如果您的期望是查询中的单个数字，那么usw如下所示：

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)

Answer 2

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;