PostgreSQL窗口函数中的LIMIT功能

Question

PostgreSQL窗口函数中的LIMIT功能

3

我想知道是否有一种简单的方法来限制查询结果只返回前n条。

比如说，我有这样一个查询：

SELECT field1
      ,field2
      ,field3
      ,sum(field2) over (partition by field1) sum2
      ,sum(field3) over (partition by field1) sum3
FROM table1
GROUP BY field1, field2, field3
ORDER BY sum2 DESC LIMIT 100

上述查询返回的是前100条记录，而不是前100个窗口（这很合理）。

我想要获取的是前100个sum2窗口，即使该窗口内可能有多行数据。因此，我可能会得到400条记录，但只需要前100个窗口。

希望这样表述清楚了。

- Phil Freeman

为什么你没有按列分组？ - Teja

我需要每个记录中的细节保持不变。我试图在同一个结果集中获取细节和聚合。实际查询要大得多。这只是问题的一个示例。 - Phil Freeman

还是不明白...但你必须首先按列分组... - Teja

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

在评论和进一步思考后，我认为以下查询可以满足您的需求。

我挑选查询结果中前100个“窗口”，并返回所有落在这些窗口中的行。由于窗口是通过field1进行划分的，因此这实际上是field1具有最大sum2的100个不同值。对于sum2相等的情况，在我的查询中较大的field1获胜（您没有指定）。

WITH x AS (
    SELECT field1
          ,field2
          ,field3
          ,sum(field2) over w sum2
          ,sum(field3) over w sum3
    FROM   table1
    GROUP  BY field1, field2, field3
    WINDOW w AS (PARTITION BY field1) 
    )
    , y AS (
    SELECT field1
    FROM   x
    GROUP  BY sum2, field1
    ORDER  BY sum2 DESC, field1 DESC
    LIMIT  100
    )
SELECT x.*
FROM   y
JOIN   x USING (field1)
ORDER  BY sum2 DESC, field1 DESC, field2 DESC, field3 DESC;

关键点是在CTE中生成聚合值，在另一个CTE中挑选出100个获胜的窗口（也可以使用DISTINCT完成，我选择了GROUP BY/ORDER BY），并将结果连接回第一个CTE以获取这些窗口的所有行。

总的来说，这是一个相当复杂的查询。