枚举Postgres表中的表分区 [英] Enumerating table partitions in Postgres table
本文介绍了枚举Postgres表中的表分区的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有一个这样的表:
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
我想枚举部分属性为0的组.
I would like to enumerate groups that have part atribute 0.
最终我想得到这个:
id | part | value | number
----+-------+-----------------
1 | 0 | 8 | 1
2 | 0 | 3 | 2
3 | 0 | 4 | 3
4 | 1 | 6 | 0
5 | 0 | 13 | 1
6 | 0 | 4 | 2
7 | 1 | 2 | 0
8 | 0 | 11 | 1
9 | 0 | 15 | 2
10 | 0 | 3 | 3
11 | 0 | 2 | 4
是否可以使用Postgres窗口函数解决此问题,或者还有其他方法吗?
Is it possible to solve this with Postgres window functions or is there another way?
推荐答案
是的,很简单:
SELECT id, part, value,
row_number() OVER (PARTITION BY grp ORDER BY id) - 1 AS number
FROM (SELECT id, part, value,
sum(part) OVER (ORDER BY id) AS grp
FROM mytable
) AS q;
id | part | value | number
----+------+-------+--------
1 | 0 | 8 | 0
2 | 0 | 3 | 1
3 | 0 | 4 | 2
4 | 1 | 6 | 0
5 | 0 | 13 | 1
6 | 0 | 4 | 2
7 | 1 | 2 | 0
8 | 0 | 11 | 1
9 | 0 | 15 | 2
10 | 0 | 3 | 3
11 | 0 | 2 | 4
(11 rows)
这篇关于枚举Postgres表中的表分区的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文