PostgreSQL功能拓展：使用REGEXP_SPLIT_TO_TABLE和STRING_AGG函数

博主：小蓝
发布时间：2023 年 12 月 25 日
916 次浏览
暂无评论
2500字数
分类： Linux 技术杂烩

PostgreSQL是一种功能强大的开源关系数据库管理系统，它提供了许多高级功能，包括对正则表达式的支持。在数据处理和转换方面，`REGEXP_SPLIT_TO_TABLE`和 `STRING_AGG`函数是两个非常有用的工具。

### REGEXP_SPLIT_TO_TABLE 函数

这个函数使用正则表达式作为分隔符来分割字符串，并将结果作为新行返回。这对于将单个文本字段中存储的多值数据拆分成可查询和可操作的多行非常有用。

#### 语法

```sql
REGEXP_SPLIT_TO_TABLE(string text, pattern text)
```

- `string`: 要分割的文本。
- `pattern`: 用于指定如何进行分割操作的正则表达式模式。

#### 示例

假设我们有一个包含以逗号隔开值列表字符串字段 `tags` 的表格 `articles`, 我们想要将每一个标签提取出来作为单独一行：

```sql
SELECT REGEXP_SPLIT_TO_TABLE(tags, ',') AS tag FROM articles;
```

### STRING_AGG 函数

与之相反，如果我们想要执行聚合操作并且把多个记录合并成一个单独字符串，则可以使用 `STRING_AGG` 函数。它可以把组内不同记录中某列值连接起来，并且可以指定连接时使用什么样子字符做间隔。![](https://www.8kiz.cn/usr/uploads/2023/12/3142320199.png)

#### 语法

```sql
STRING_AGG(expression, delimiter ORDER BY order_expression)
```

- `expression`: 需要聚合连接起来列。
- `delimiter`: 在每次连接时插入到值之间得字符。
- (可选) ORDER BY: 指定在聚合前如何排序这些值。

#### 示例

如果我们想从上面提到得文章标签例子反过来进行操作：即从文章标签列表中创建出一个由逗号隔开得字符串：

```sql
SELECT STRING_AGG(tag, ', ') AS tags_list FROM (SELECT tag FROM articles) sub;
```

### 实际应用场景示例

考虑以下场景：你需要处理用户提交给你网站后台管理系统里面关键词字段内容，并希望能够快速地检索出所有相关联文章标题列表。首先你可能会利用到 REGEXP_SPLIT_TO_TABLE 来拆解用户输入内容；然后通过搜索引擎去匹配相关联标题；最终利用 STRING_AGG 将搜索结果汇总展示给用户看见结果集串联起来形成简洁明了、易于阅读理解得格式输出展现方式：

1. 使用 REGEXP_SPLIT_TO_TABLE 分析输入：

```sql
   SELECT title 
   FROM articles 
   WHERE article_id IN (
       SELECT article_id 
       FROM article_tags 
       WHERE tag = ANY(
           SELECT REGEXP_SPLIT_TO_TABLE('user input keywords', '\\s+')
       )
   );
   ```
2. 使用 STRING_AGG 汇总搜索结果：

```sql
   SELECT STRING_AGG(title, '; ') AS related_titles 
   FROM (
       -- 上述查询语句放置此处作为子查询 --
   ) sub;
   ```

通过上述方法结合运算符、条件判断等SQL技巧能够实现复杂数据转换与汇总需求，在实际应用程序或者报告生成等方面具备极高价值与效率优势。

#### [云服务器/高防CDN推荐](https://www.tsyvps.com/)

#### [蓝易云](https://www.tsyvps.com/)国内/海外高防云服务器推荐

<div class="inner-content" >
<p class="inser-title">蓝易云-五网CN2服务器【点我购买】</p>
<div class="inster-summary text-muted">
<span style='color: red;'>蓝易云采用KVM高性能架构，稳定可靠，安全无忧！<br>蓝易云服务器真实CN2回国线路，不伪造，只做高质量海外服务器。</span>
</div>
</div>
</a>

</div>

</div>

---

**<span style='color:#000000'>海外免备案云服务器链接：[www.tsyvps.com](https://www.tsyvps.com)</span>**

**<span style='color:#000000'>蓝易云安全企业级高防CDN：[www.tsycdn.com](https://www.tsycdn.com)</span>**

**<span style='color:#DC143C'>持有增值电信营业许可证：B1-20222080【资质齐全】</span>**

**<span style='color:#DC143C'>蓝易云香港五网CN2 GIA/GT精品网络服务器。拒绝绕路，拒绝不稳定。</span>**

---

#### **百度搜索：[蓝易云](https://www.baidu.com/s?ie=UTF-8&wd=%E8%93%9D%E6%98%93%E4%BA%91)**![](https://www.8kiz.cn/usr/uploads/2023/12/1145065575.png)