sql - 在 postgres 表中跨所有唯一阈值运行 "distinct on"

Question

我有一个名为 sample_a 的 Postgres 11 表，如下所示:

 time | cat | val
------+-----+-----
    1 |   1 |   5
    1 |   2 |   4
    2 |   1 |   6
    3 |   1 |   9
    4 |   3 |   2

我想为每个唯一时间步创建一个查询，获取该时间步或之前每个类别的最新值，并通过计算这些值的总和并除以这些值的计数来聚合这些值。

我相信我有针对给定时间步执行此操作的查询。例如，对于时间 3，我可以运行以下查询:

select sum(val)::numeric / count(val) as result from (
    select distinct on (cat) * from sample_a where time <= 3  order by cat, time desc
) x;

并获得 6.5。 (这是因为在时间 3，类别 1 中的最新项是 9，而类别 2 中的最新项是4。值的个数是2，它们加起来是13，和13/2 是 6.5。)

但是，理想情况下，我希望运行一个查询，该查询将为我提供表中每个唯一时间的所有结果。这个新查询的输出如下所示:

 time | result
------+----------
    1 |   4.5
    2 |   5
    3 |   6.5
    4 |   5

这个新查询理想情况下会尽可能避免添加另一个 subselect 子句；一个有效的查询将是首选。我可以通过在我的应用程序中为每个时间步运行先前的查询来获得这些先前的结果，但这对于大型 sample_a 来说似乎效率不高。

这个新查询会是什么样子？

Answer 1

通过这种方式查看性能是否可以接受。语法可能需要微调:

select t.time, avg(mr.val) as result
from (select distinct time from sample_a) t,
    lateral (
        select distinct on (cat) val
        from sample_a a
        where a.time <= t.time
        order by a.cat, a.time desc
    ) mr
group by t.time