PostgreSQL实战(12)高级特性
1、WITH 查询
先通过一个简单的 CTE 示例了解 WITH 查询,如下所示:
WITH t as (
SELECT generate series(l,3)
SELECT * FROM t;
generate_series
--------------------
1
2
3
(3 rows)
递归查询使用 CTE:
WITH 查询的 一个重要属 性是 RECURSIVE ,使用 RECURSIVE 属性可以引用自己的输出,从而实现递归, 一般用于层次结构或树状结构的应用场景,一个简单的RECURSIVE 例子如下所示
WITH recursive t (x) as (
SELECT 1
UNION
SELECT x + 1
FROM t
WHERE x < 5
)
SELECT sum(x) FROM t;
输出结果为
sum
15
(1 row)
2、聚合函数
string_agg 函数:此函数语法如下所示:
string_agg(expression , delimiter)
简单地说 string_agg 函数能将结果集某个字段的所有行连接成字符串,并用指定delimiter 分隔符分隔, expression 表示要处理的字符类型数据;参数的类型为(text, te~t)或(bytea, bytea ,函数返 回的类型同输入参数类型一致, by tea 属于 进制类型,使用情况不多,我们主 介绍 text 类型输入参数,一个例子:
mydb=> SELECT * FROM city;
country city
------------+--- ---
中国 | 台北
中国 |香港
中国 |上海
日本 东京
日本 大阪
(5 rows)
将city 段连接成 符串的代码如下:
mydb=> SELECT str ng agg c 工 ty ,’,’) FROM city;
string_agg
台北,香港,上海,东京,大阪
(1 r ow )
array_agg 函数:array_agg 函数和 string_agg 函数类似,主要的区别为返回的类型为 数组,数组数据类型同输入 参数数据类型 一致, array_agg 函数支持两种语 第一种如下
array_agg (expression ) 输入参数为任何非数组类型
输入 数可以是任何非数组类型,返回的结果是一维数组 array_agg 函数将结果集某个字段的所有行连接成数组,例如执行以下 询:
mydb=> SELECT coutry array_agg(city) FROM city GROUP BY country;
country array_agg
-- + ------
日本 | {东京 大阪}
中国 | {台北 香港 上海}
第二种 array_agg 语法如下所示:
array_agg (expression ) 输入参数为任何数组类型
返回类型为多维数组:例如:
数据如下所示:
select * from test_aray;
id
-------------------------
{ 1, 2 ' 3}
{ 4 ' 5 , 6}
(2 rows)
使用 array_agg 函数 如下
mydb=> SELECT array_ agg(id) FROM test_arra y3 ;
array_agg
{{1 , 2 , 3) , {4 , 5 ,6 }}
(1 r ow )