Блог

Я хочу вычислить значение путем интерполяции значения между двумя ближайшими соседями. У меня есть подзапрос, который возвращает значения соседей и их относительное расстояние в виде двух столбцов с двумя элементами.

Допустим:

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

Как я могу вычислить значение точки с помощью линейной интерполяции? Возможно ли это сделать в одном запросе?

Пример: у моей точки есть сосед A со значением 10 на расстоянии 1 и сосед B со значением 20 на расстоянии 4. Функция должна возвращать значение 10 * 4 20 * 1 / 5 = 12 для моей точки.

Я попробовал очевидный подход

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

который завершится неудачей, потому что вы не можете работать с групповыми предложениями внутри групповых предложений. Использование другого подзапроса, возвращающего сумму, также невозможно, потому что тогда я не могу пересылать отдельные значения одновременно.

Это уродливый взлом (основанный на злоупотреблении 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
    ;
 

Если вы действительно хотите point промежуточное звено, есть встроенный способ сделать это (но не агрегатная функция).:

 SELECT center(box(x.mypoint,y.mypoint))
FROM   ([get some neighbours by distance] order by value limit 1) x
      ,([get some neighbours by distance] order by value offset 1 limit 1) y;
 

Если вам нужно среднее расстояние:

 SELECT avg(x.distance)
FROM   ([get some neighbours by distance] order by value limit 2) as x
 

См. раздел Геометрическая функция и агрегатные функции в руководстве.

Редактировать:

Для добавленного примера запрос может выглядеть следующим образом:

 SELECT (x.value * 4   y.value) / 5 AS result
FROM   ([get some neighbours by distance] order by value limit 1) x
      ,([get some neighbours by distance] order by value offset 1 limit 1) y;
 

Я добавил пропущенное () , чтобы получить результат, который вы ожидаете!

Или мой последний удар в этом направлении:

 SELECT y.x, y.x[1], (y.x[1] * 4   y.x[2]) / 5 AS result
FROM  (
    SELECT ARRAY(
        SELECT value FROM tbl WHERE [some condition] ORDER BY value LIMIT 2
        ) x
    ) y
 

Это было бы намного проще, если бы вы предоставили полный запрос и определения таблиц.