在PostgreSQL中,我一直为这个问题而苦恼。我有一个名为test
的表格,它有2列:- id
和content
。比如:
create table test (id integer,
content varchar(1024));
insert into test (id, content) values
(1, 'Lorem Ipsum is simply dummy text of the printing and typesetting industry.'),
(2, 'Lorem Ipsum has been the industrys standard dummy text '),
(3, 'ever since the 1500s, when an unknown printer took a galley of type and scrambled it to'),
(4, 'make a type specimen book.'),
(5, 'It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged.'),
(6, 'It was popularised in the 1960s with the release of Letraset sheets containing Lorem '),
(7, 'Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker'),
(8, ' including versions of Lorem Ipsum.');
如果我运行以下查询...
select id, length(content) as characters from test order by id
...然后我得到:-
id | characters
---+-----------
1 | 74
2 | 55
3 | 87
4 | 26
5 | 120
6 | 85
7 | 87
8 | 35
我想要做的是将id
分组成行,使得内容的总和超过一个阈值。例如,如果该阈值为100
,则期望的结果如下:
ids | characters
----+-----------
1,2 | 129
3,4 | 113
5 | 120
6,7 | 172
8 | 35
注意(1): - 查询不需要生成一个characters
列,只需要ids
- 它们在这里用来传达它们都超过了100
- 除了最后一行的值为35
。
注意(2): - ids
可以是逗号分隔的字符串或PostgreSQL数组 - 类型不如值重要。
我可以使用窗口函数来实现这个功能还是需要像lateral join
这样更复杂的东西?