选择最近的邻居 [英] select nearest neighbours

问题描述

请考虑以下数据：

category | index | value
-------------------------
cat 1    | 1     | 2
cat 1    | 2     | 3
cat 1    | 3     |  
cat 1    | 4     | 1
cat 2    | 1     | 5
cat 2    | 2     |  
cat 2    | 3     |  
cat 2    | 4     | 6
cat 3    | 1     |  
cat 3    | 2     |  
cat 3    | 3     | 2 
cat 3    | 4     | 1

我正在尝试填补空缺，因此 hole = avg （值）类别中具有2个非空值的2个最近的邻居：

I am trying to fill in the holes, so that hole = avg(value) of 2 nearest neighbours with non-null values within a category:

category | index | value
-------------------------
cat 1    | 1     | 2
cat 1    | 2     | 3
cat 1    | 3     | 2*
cat 1    | 4     | 1
cat 2    | 1     | 5
cat 2    | 2     | 5.5*
cat 2    | 3     | 5.5* 
cat 2    | 4     | 6
cat 3    | 1     | 1.5*
cat 3    | 2     | 1.5* 
cat 3    | 3     | 2 
cat 3    | 4     | 1

我一直在使用窗口函数，并且可以肯定可以实现，但是解决方案是

I've been playing with window functions and am pretty sure it can be achieved but the solution is eluding me.

有什么想法吗？

推荐答案

您是对的，窗口功能是您要寻找的。这是完成的方式（和部分用于定义表，所以您可能不需要它）：

You are correct, window function is what you're looking for. Here's how it can be done (with part is used to define table, so you probably won't need it):

with dt as
(
    select * from
    (
        values
            ('cat 1', 1, 2),
            ('cat 1', 2, 3),
            ('cat 1', 3, null),
            ('cat 1', 4, 1),
            ('cat 2', 1, 5),
            ('cat 2', 2, null),
            ('cat 2', 3, null),
            ('cat 2', 4, 6),
            ('cat 3', 1, null),
            ('cat 3', 2, null),
            ('cat 3', 3, 1),
            ('cat 3', 4, 2)

    ) tbl ("category", "index", "value")
)
select
        "category",
        "index",
        case
            when "value" is null then (avg("value") over (partition by "category") )
            else "value"
        end
    from dt
    order by "category", "index";

请参阅WINDOW子句部分href = http://www.postgresql.org/docs/9.3/static/sql-select.html rel = nofollow>此页面以获取有关窗口函数的更多信息。

refer to WINDOW Clause section of this page for further info on window functions.

这篇关于选择最近的邻居的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！