开篇:
记录本人在一次业务开发中遇到的业务问题,与pgsql相关
一、涉及到的知识
unnest(): 将数组转换成一个临时表
array_agg(col): 接受一组值,返回一个数组
except: query1 except query2, 用于剔除相同元素
二、背景描述
需要时插入一行数据,例如像 tables 表中插入 table_title: clzSkywalker,发现 table_title 列中已经存在了 clzSkywalker 的值,那么就需要将 clzSkywalker 后面加编号,例如:clzSkywalker1, clzSkywalker2 等等,极限值是 clzSkywalker100,我只需要一个取第一个不存在 table_title,例如表中已经存在了:clzSkywalker,clzSkywalker1,返回的结果有 clzSkywalker2,clzSkywalker3,clzSkywalker4....,我只需要去第一个,也就是 clzSkywalker2
三、开发想法
-
通过代码先创建好 100 个名字:
['a','a-1','a-2','a-3'...]
以此作为参数 -
通过 in 关键字进行匹配查询对应的数据:
select t.table_title from "tables" t where t.table_title in ('a','a-1','a-1','a-3','a-4')
得到结果:
table_title a a-1 a-2 -
通过 sql 将参数转化为列
select string_to_array('a,a-1,a-2,a-3,a-4', ',') as table_title;
结果:
table_title {a,a-1,a-2,a-3,a-4} -
最终 sql
select string_to_array('a,a-1,a-2,a-3,a-4', ',') as table_title except select t.table_title from "tables" t where t.table_title in ('a','a-1','a-1','a-3','a-4')
-
最终结果
table_title {a,a-1,a-2,a-3,a-4}
四、问题描述
最终的 sql 此处用 query1 except query2 表示
通过最终的 sql 结果发现两条 sql 执行的结果并不一致,query1 是一个 array,query 是表,这没法 except
五、我的想法、尝试、结果
想法一:
将 query2 的结果也转为 array(),然后用 query1 的 array 剔除 query2 的 array
将 query2 结果转为 array 的 sql: 使用了一个 array_agg 的函数,下这个 sql 我称为 query3
select array_agg(t.table_title) from "tables" t where t.table_title in ('a','a-1','a-1','a-3','a-4')
结果
table_title |
---|
{a,a-1,a-2} |
接下来就是用 query1 中的 array 一个个剔除 query3 中的 array ,我发现 pgsql 只提供了一个 array_remove(array, element) 函数用于剔除 array 中的元素,array_remove 只能剔除单个元素,在此处无用。
接下来头疼了,我该怎么从一个数组中剔除一组数组呢?
谷歌了一下,可以通过创建函数解决,如下:
create or replace function array_diff(array1 anyarray, array2 anyarray)
returns anyarray language sql immutable as $$
select coalesce(array_agg(elem), '{}')
from unnest(array1) elem
where elem <> all(array2)
$$;
select array_diff(array['a','b','c','d'], array['a','d']);
array_diff
------------
{b,c}
(1 row)
这种方式也可以做到我想要的结果,不过使用函数这种方式并不是我想要的。
想法二:
既然 query2 的结果转为 array 解决不了,那我就把 query 的 array 结果转为临时表。
怎么转呢?怪我 sql
太垃圾,遇事不决,只能谷歌,查了半天,找到了一个函数 unnest()
,这是个好东西,能够将数组转为临时表。
于是,我们的 sql
改装一下:
select unnest(string_to_array('a,a-1,a-2,a-3,a-4', ',')) as table_title
except
select t.table_title from "tables" t where t.table_title in ('a','a-1','a-1','a-3','a-4');
query1 的结果变成了:
table_title |
---|
a |
a-1 |
a-2 |
a-3 |
a-4 |
query2 的结果:
table_title |
---|
a |
a-1 |
a-2 |
结果结果:
table_title |
---|
a-4 |
a-3 |
终于将结果搞定了,取出了其中的差值,这时候,回顾下之前的需求,我们的结果只需要一个,也就是在 a-2
之后的 a-3
,其他的就不需要了,这时候我们需要做的就是给结果进行排序与 limit 限制:
select a2.title from (
select (case when SPLIT_PART(a1.title,'-',2) ='' then 0 else cast(SPLIT_PART(a1.title,'-',2) as integer) end ) as id ,a1.title from ((select
s2.e as title
from
unnest(string_to_array('a,a-1,a-2,a-3,a-4', ',')) as s2(e)
group by title
order by title asc)
except
select t.table_title from "tables" t where t.table_title in ('a','a-1','a-1','a-3','a-4');
) as a1 order by id) as a2 limit 1;
五、结语
当两个结果的数据进行比较时,需要考虑下两者类型是否一致,如果不一致,则需要想办法将他们的数据类型保持一致,可以 a->b 或者 b->a,再或者 a->c,b->c 都可以。
在使用想法二的时候,特别头疼,不知道怎么将数组转为列,为此卡了好久,直到遇到 unnest
才顺利解决,哎
我是 clz,祝你幸福