Postgresql :将逗号分隔的整数值和间隔转换为序号
Postgresql :将逗号分隔的整数值和间隔转换为序号
提问于 2015-07-29 05:30:36
我有一张桌子，上面写着：
值是一个varchar类型，用于存储字符串字符，如comma和dashes，因此任何事情都会发生。但通常情况下，它只包含numbers、comma和dash来指定间隔。
id | value |
------------------
1 | 1,2,5,8-10 |
2 | 1,2,3 |
3 | 1-3 |
4 | 1-3, 4-5 |
5 | 1-2,2-3 |我希望执行一个select查询，在数据库级别(而不是在代码级别)以“规范化”代码可读的格式select检索值，这就是为什么我需要选择这样的表。
id | value | normalized
-------------------------------
1 | 1,2,5,8-10 |1,2,5,8,9,10
2 | 1,2,3 |1,2,3
3 | 1-3 |1,2,3
4 | 1-3, 4-5 |1,2,3,4,5
5 | 1-2,2-3 |1,2,3对于id # 5记录的特殊情况，即使它指定了2次，它仍然应该只检索2次。postgres中是否已经有这样的函数？如果不是，我如何解析字符串并在Postgres sql中对数字排序
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-07-29 06:24:49
对于您喜欢的PL中的过程或简单的C扩展来说，这似乎是一个很好的例子。pl/perl、pl/pythonu或pl/v8将是我的选择。
尽管如此，在SQL中还是很容易的。拆分以查找子范围，该子范围可以是单个数字，也可以是范围。然后对每一个范围的generate_series在它上面。
例如：
SELECT n
FROM
regexp_split_to_table('1,2,5,8-10', ',') subrange,
regexp_split_to_array(subrange, '-') subrange_parts,
generate_series(subrange_parts[1]::integer,
coalesce(subrange_parts[2], subrange_parts[1])::integer
) n;它可以作为SQL函数结束，也可以作为表查询的一部分使用。
应用于一张桌子，您会得到如下内容：
CREATE TABLE example
("id" int, "value" varchar)
;
INSERT INTO example
("id", "value")
VALUES
(1, '1,2,5,8-10'),
(2, '1,2,3'),
(3, '1-3'),
(4, '1-3, 4-5'),
(5, '1-2,2-3')
;当应用于一张表时，这是一种类似于：
SELECT
example.id,
array_agg(DISTINCT n) AS expanded_set
FROM
example,
regexp_split_to_table(example.value, ',') subrange,
regexp_split_to_array(subrange, '-') subrange_parts,
generate_series(subrange_parts[1]::integer,
coalesce(subrange_parts[2], subrange_parts[1])::integer
) n
GROUP BY
example.id;结果(增加原文)：
id | original_format | expanded_set
----+-----------------+----------------
1 | 1,2,5,8-10 | {1,2,5,8,9,10}
2 | 1,2,3 | {1,2,3}
3 | 1-3 | {1,2,3}
4 | 1-3, 4-5 | {1,2,3,4,5}
5 | 1-2,2-3 | {1,2,3}
(5 rows)这不会是特别快，但可能会没事。如果不是，用C写一些更快的东西作为扩展，或者是plperl或者其他什么的。
要了解发生了什么，请阅读PostgreSQL手册中关于以下内容的部分：
GROUP BY与聚合- 聚合函数，特别是
array_agg DISTINCT作为聚合限定符- PostgreSQL数组，我在这里用作中间状态和结果
generate_series函数regexp_split_to_table和regexp_split_to_array函数LATERAL查询，这些查询在这里隐式使用，因为一个函数使用连接列表中的另一个函数的结果。
上面的示例只适用于PostgreSQL 9.2和更高版本。如果您的版本较早，则必须使用嵌套子查询层来解决缺少LATERAL的问题。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31691879
复制相关文章
相似问题
腾讯云开发者
