sql - 选择最近的邻居

考虑以下数据：

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

我一直在玩窗口函数，我很确定它可以实现，但解决办法是逃避我。
有什么想法吗？

最佳答案

你说得对，你要找的是窗口功能。下面是它的实现方法（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部分。

关于sql - 选择最近的邻居，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/31738642/