gpt4 book ai didi

sql - PostgreSQL : Converting comma separated integer values and intervals to sequenced numbers

转载 作者:行者123 更新时间:2023-11-29 12:11:37 24 4
gpt4 key购买 nike

我有一个包含以下内容的表格:

Value 是一种 varchar 类型,用于存储字符串字符,如 commadashes 所以任何事情都可以。但通常它只包含 numberscommadash 来指定间隔。

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 查询,以数据库级别(不是代码级别),这就是为什么我需要选择一个像这样的表。

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 中对数字进行排序?

最佳答案

对于您首选的 PL 中的过程或简单的 C 扩展,这似乎是一个很好的例子。 pl/perl、pl/pythonu 或 pl/v8 将是我的选择。

也就是说,在 SQL 中这很容易。拆分以找到子范围,可以是单个数字或范围。然后为每个范围生成_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_tableregexp_split_to_array 函数
  • LATERAL 查询,此处隐式使用这些查询,因为一个函数使用连接列表中另一个函数的结果。

以上示例仅适用于 PostgreSQL 9.2 及更新版本。如果您使用的是旧版本,则必须使用嵌套子查询层来解决缺少 LATERAL 的问题。

关于sql - PostgreSQL : Converting comma separated integer values and intervals to sequenced numbers,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/31691879/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com