如何使用窗口函数枚举Postgres表中的分区组？ [英] How to enumerate groups of partitions in my Postgres table with window functions?

问题描述

假设我有一个像这样的表：

Suppose I have a table like this:

id  | part  | value
----+-------+-------
 1  | 0     | 8
 2  | 0     | 3
 3  | 0     | 4
 4  | 1     | 6
 5  | 0     | 13
 6  | 0     | 4
 7  | 1     | 2
 8  | 0     | 11
 9  | 0     | 15
 10 | 0     | 3
 11 | 0     | 2

我想枚举具有部分属性1的行之间的组。

I would like to enumerate groups between rows that have part atribute 1.

所以我想得到这个：

id  | part  | value | number
----+-------+-----------------
 1  | 0     | 8     |   1
 2  | 0     | 3     |   1
 3  | 0     | 4     |   1
 4  | 1     | 6     |   0
 5  | 0     | 13    |   2
 6  | 0     | 4     |   2
 7  | 1     | 2     |   0
 8  | 0     | 11    |   3
 9  | 0     | 15    |   3
 10 | 0     | 3     |   3
 11 | 0     | 2     |   3

是否可以使用Postgres窗口函数实现此功能，或者还有其他方法吗？

Is it possible to achieve this with Postgres window functions or is there any other way?

推荐答案

您似乎想要的比部分的累加总和还要多1。最简单的方法是：

You seem to want something like 1 more than the cumulative sum of the parts. The simplest method is:

select t.*,
       (case when part = 1 then 0  -- the easy case
             else 1 + sum(part) over (order by id)
        end) as number
from t;

如果 part 可以采用除0和1：

If part can take on values other than 0 and 1:

select t.*,
       (case when part = 1 then 0  -- the easy case
             else 1 + sum( (part = 1)::int ) over (order by id)
        end) as number
from t;

这篇关于如何使用窗口函数枚举Postgres表中的分区组？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！