在PostgreSQL中按窗口函数过滤结果 [英] Filtering by window function result in Postgresql

问题描述

好吧，起初这只是和我的一个朋友开玩笑，但后来变成了有趣的技术问题：）

Ok, initially this was just a joke we had with a friend of mine, but it turned into interesting technical question :)

我有以下东西表：

CREATE TABLE stuff
(
    id serial PRIMARY KEY,
    volume integer NOT NULL DEFAULT 0,
    priority smallint NOT NULL DEFAULT 0,
);

该表包含我所有物品的记录，并分别包含数量和优先级（我需要多少

The table contains the records for all of my stuff, with respective volume and priority (how much I need it).

我有一个指定容量的袋子，例如 1000 。我想从表中选择我可以放进袋子的所有东西，首先包装最重要的东西。

I have a bag with specified volume, say 1000. I want to select from the table all stuff I can put into a bag, packing the most important stuff first.

这似乎是使用窗口函数的情况，所以这里是我想出的查询：

This seems like the case for using window functions, so here is the query I came up with:

select s.*, sum(volume) OVER previous_rows as total
 from stuff s
 where total < 1000
 WINDOW previous_rows as
  (ORDER BY priority desc ROWS between UNBOUNDED PRECEDING and CURRENT ROW)
 order by priority desc

但是，问题在于Postgres抱怨：

The problem with it, however, is that Postgres complains:

ERROR:  column "total" does not exist
LINE 3:  where total < 1000

如果我删除此过滤器，则会正确计算总列数，对结果进行正确排序，但全部东西被选中，这不是我想要的。

If I remove this filter, total column gets properly calculated, results properly sorted but all stuff gets selected, which is not what I want.

那么，我该怎么做？我该如何选择仅适合放入袋子的物品？

So, how do I do this? How do I select only items that can fit into the bag?

推荐答案

我没有使用PostgreSQL。但是，我最好的猜测是使用内联视图。

I haven't worked with PostgreSQL. However, my best guess would be using an inline view.

SELECT a.*
FROM (
    SELECT s.*, sum(volume) OVER previous_rows AS total
    FROM stuff AS s
    WINDOW previous_rows AS (
         ORDER BY priority desc
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    )
    ORDER BY priority DESC
) AS a
WHERE a.total < 1000;

这篇关于在PostgreSQL中按窗口函数过滤结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！