单个查询中两个值之间的插值 [英] Interpolation between two values in a single query
问题描述
我想通过在两个最近的邻居之间插值来计算值。
我有一个子查询,以两个列和两个元素的形式返回邻居的值及其相对距离。
I want to calculate a value by interpolating the value between two nearest neighbours. I have a subquery that returns the values of the neighbours and their relative distance, in the form of two columns with two elements.
让我们说:
(select ... as value, ... as distance
from [get some neighbours by distance] limit 2) as sub
如何通过线性插值计算点的值?
How can I calculate the value of the point by linear interpolation? Is it possible to do that in a single query?
示例:我的点的邻居A的距离为1,距离为1,距离为4的邻居B的值为20。该函数应该为我的点返回值 10 * 4 + 20 * 1/5 = 12
。
Example: My point has the neighbour A with value 10 at distance 1, and the neighbour B with value 20 at distance 4. The function should return a value 10 * 4 + 20 * 1 / 5 = 12
for my point.
我尝试了显而易见的方法
I tried the obvious approach
select sum(value * (sum(distance)-distance)) / sum(distance)
这将失败,因为您无法在组子句中使用组子句。也不可能使用另一个子查询返回总和,因为那样我便无法同时转发各个值。
which will fail because you cannot work with group clauses inside group clauses. Using another subquery returning the sum is not possible either, because then I cannot forward the individual values at the same time.
推荐答案
此是一个丑陋的黑客行为(基于滥用的CTE;)。关键是
This is an ugly hack (based on a abused CTE ;). The crux of it is that
value1 * distance2 + value2 * distance1
可以通过除以distance1 * distance2来重写为
Can, by dividing by distance1*distance2, be rewritten to
value1/distance1 + value2/distance2
因此,产品(或部门)可以保留在其内部行。求和后,乘以(distance1 * distance2)可将结果重新缩放为所需的输出。 YMMV
So, the products (or divisions) can stay inside their rows. After the summation, multiplying by (distance1*distance2) rescales the result to the desired output. Generalisation to more than two neighbors is left as an exercise to the reader.YMMV
DROP TABLE tmp.points;
CREATE TABLE tmp.points
( pname VARCHAR NOT NULL PRIMARY KEY
, distance INTEGER NOT NULL
, value INTEGER
);
INSERT INTO tmp.points(pname, distance, value) VALUES
( 'A' , 1, 10 )
, ( 'B' , 4, 20 )
, ( 'C' , 10 , 1)
, ( 'D' , 11 , 2)
;
WITH RECURSIVE twin AS (
select 1::INTEGER AS zrank
, p0.pname AS zname
, p0.distance AS dist
, p0.value AS val
, p0.distance* p0.value AS prod
, p0.value::float / p0.distance AS frac
FROM tmp.points p0
WHERE NOT EXISTS ( SELECT * FROM tmp.points px
WHERE px.distance < p0.distance)
UNION
select 1+twin.zrank AS zrank
, p1.pname AS zname
, p1.distance AS dist
, p1.value AS val
, p1.distance* p1.value AS prod
, p1.value::float / p1.distance AS frac
FROM tmp.points p1, twin
WHERE p1.distance > twin.dist
AND NOT EXISTS ( SELECT * FROM tmp.points px
WHERE px.distance > twin.dist
AND px.distance < p1.distance
)
)
-- SELECT * from twin ;
SELECT min(zname) AS name1, max(zname) AS name2
, MIN(dist) * max(dist) *SUM(frac) / SUM(dist) AS score
FROM twin
WHERE zrank <=2
;
结果:
CREATE TABLE
INSERT 0 4
name1 | name2 | score
-------+-------+-------
A | B | 12
更新:这有点清洁了……纽带仍未处理(需要在外部查询中使用窗口函数或LIMIT 1子句)
Update: this one is a bit cleaner ... ties are still not handled (need a window function or a LIMIT 1 clause in the outer query for that)
WITH RECURSIVE twin AS (
select 1::INTEGER AS zrank
, p0.pname AS name1
, p0.pname AS name2
, p0.distance AS dist
FROM tmp.points p0
WHERE NOT EXISTS ( SELECT * FROM tmp.points px
WHERE px.distance < p0.distance)
UNION
select 1+twin.zrank AS zrank
, twin.name1 AS name1
, p1.pname AS name2
, p1.distance AS dist
FROM tmp.points p1, twin
WHERE p1.distance > twin.dist
AND NOT EXISTS ( SELECT * FROM tmp.points px
WHERE px.distance > twin.dist
AND px.distance < p1.distance
)
)
SELECT twin.name1, twin.name2
, (p1.distance * p2.value + p2.distance * p1.value) / (p1.distance+p2.distance) AS score
FROM twin
JOIN tmp.points p1 ON (p1.pname = twin.name1)
JOIN tmp.points p2 ON (p2.pname = twin.name2)
WHERE twin.zrank =2
;
这篇关于单个查询中两个值之间的插值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!