gpt4 book ai didi

algorithm - 单个查询中两个值之间的插值

转载 作者:塔克拉玛干 更新时间:2023-11-03 06:04:14 26 4
gpt4 key购买 nike

我想通过插入两个最近邻居之间的值来计算一个值。我有一个子查询,它以包含两个元素的两列的形式返回邻居的值及其相对距离。

假设:

(select ... as value, ... as distance 
from [get some neighbours by distance] limit 2) as sub

如何通过线性插值计算点的值?是否可以在单个查询中执行此操作?

示例:我的点在距离 1 处有值为 10 的邻居 A,在距离 4 处有值为 20 的邻居 B。该函数应返回值 10 * 4 + 20 * 1/5 = 12 我的观点。

我尝试了明显的方法

select sum(value * (sum(distance)-distance)) / sum(distance)

这将失败,因为您不能在组子句中使用组子句。使用另一个返回总和的子查询也是不可能的,因为这样我就不能同时转发各个值。

最佳答案

这是一个丑陋的 hack(基于滥用的 CTE ;)。关键在于

value1 * distance2 + value2 * distance1

除以distance1*distance2,可以重写为

value1/distance1 + value2/distance2

因此,产品(或部门)可以保留在它们的行内。求和后,乘以 (distance1*distance2) 将结果重新调整为所需的输出。泛化到两个以上的邻居作为练习留给读者。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 子句)

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
;

关于algorithm - 单个查询中两个值之间的插值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/7885526/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com