001.pgsql 使用sql剔除结果中的数据

110 阅读3分钟

开篇:

记录本人在一次业务开发中遇到的业务问题,与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

三、开发想法

  1. 通过代码先创建好 100 个名字:['a','a-1','a-2','a-3'...] ​以此作为参数

  2. 通过 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
  3. 通过 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}
  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')
    
  5. 最终结果

    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,祝你幸福