选择最近邻居

Question

考虑以下数据：

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(value)是与两个最近的非空值的平均值相等的。

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

我一直在研究窗口函数，相信可以实现，但解决方案仍然让我无法捉摸。

有什么想法吗？

- pstanton

---

为什么在2和6之间的值平均为1.5而不是4？ - Tim Biegeleisen

另外，你必须只使用Postgres来解决这个问题吗？如果是的话，我会很难在像R或Matlab这样的工具中完成这个任务，因为它们都是为此类问题而设计的。 - Tim Biegeleisen

“6”不在“cat 3”中。在cat 3中，最近的邻居是2和1。 - pstanton

仅限于PostgreSQL，谢谢。 - pstanton

Answer 1

您是正确的，窗口函数就是您要找的。以下是如何实现的（with 部分用于定义表格，所以您可能不需要它）：

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";

请参考this页面的WINDOW Clause部分，了解有关窗口函数的更多信息。

- Nick

---

我不得不做一些稍微不同的事情，涉及内部查询和 row_number()，但你的答案非常好。 - pstanton

Answer 2

我正在为您解决方案，但是SQLfiddle目前出现(内部)错误，所以我无法完成它。

像这样的语句应该可以为您更新：

update table1 as t1
set value = 
  (select avg(value)
   from 
   (select value
    from table1 as t3
    where t1.category = t3.category
    and   t3.index in (t1.index - 1, t1.index + 1)
    ) AS T2
   )
where value is null
;

我正在处理的代码片段在这里：http://sqlfiddle.com/#!15/acbc2/1。

- Turophile

---

索引+1和索引-1不总是最近的邻居，因为缺失的行可能是类别中的第一行，或者空洞可能是2x行。 - pstanton

解决方案是使用 MIN() 和 MAX() 函数。如果 sqlfiddle 可以使用，我稍后可能会进行修改。目前我无法进行实验。 - Turophile

Answer 3

虽然我相信可能有一些非常复杂和嵌套的语句可以做到你想要的，但我想说，有时候最好使用常规编程语言（如Python/Ruby/Java）编写脚本，迭代数据库表并进行所需的任何更改。

这将更易于维护，您不需要每次需要进行任何更改（例如使用3个最近邻居，或更改“最近邻居”的定义）时重新设计整个系统。

- Aditya Mukherji

---

但也非常低效！ - pstanton