代码之家 › 专栏 › 技术社区 › p4x

PostgreSQL:使用计数器更新表列上的数据

postgresql-9.4 counter sql-update loops sql

p4x · 技术社区 · 7 年前

我想将数据库中表的一列更改为UNIQUE NOT NULL,首先我需要编写一个postgres sql脚本来修复当前数据。

基本上,我需要做的是找到“code”列为NULL或“”的所有实例,并使用如下文本对其进行更改:(1)、(2)、(3)、(4)。。

同样,我必须找到所有具有重复代码的实例,并将它们更改为这样的内容:“sameCode(1)”、“sameCode(2)”、“equalCode(1)”、“equalCode(2)”、“equalCode(3)”。

Example input       Expected Output
ââââââ¤ââââââââ      ââââââ¤âââââââââââ
â id â code  â      â id â code     â
â âââââªââââââââ£      â âââââªâââââââââââ£
â 1  â null  â      â 1  â (1)      â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 2  â ' '   â      â 2  â (2)      â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 3  â 'FOO' â      â 3  â 'FOO(1)' â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 4  â 'FOO' â      â 4  â 'FOO(2)' â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 5  â 'BAR' â      â 5  â 'BAR(1)' â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 6  â 'BOB' â      â 6  â 'BOB'    â
ââââââ¼ââââââââ¢      ââââââ¼âââââââââââ¢
â 7  â 'BAR' â      â 7  â 'BAR(2)' â
ââââââ§ââââââââ      ââââââ§âââââââââââ

使用SQL编写带有计数器和更新的循环不是我的强项之一,我发现这有点困难。

从概念上讲,这很简单。获取按代码(NULL和“”)排序的ID及其代码的有序列表,然后在其上循环,将“(++计数器)”添加到当前代码文本中,但每次代码更改时都会重新启动计数器。这会很慢,但没关系,这只是一次性的事情,我们这里不会讨论数百万个实例。

DECLARE
    list_of_instances tablename[]; #dunno how to declare an instance
    counter INTEGER;
    oldcode CHARACTER VARYING;
BEGIN
    list_of_instances:=(select id, code from tablename 
                       where code is null or code = ' ' or code in 
                        (select code from tablename group by code having count(*) > 1) 
                       order by codi desc);
    counter:=1;
    oldcode:=tablename[0].code;
    for i in list_of_instances LOOP
        UPDATE tablename SET code=i.code + '(' + cast(counter as text) +')'
        WHERE id=i.id;
        counter:= counter+1;
        IF i.code <> oldcode THEN
            oldcode:=i.code;
            counter:=1;
        END IF;
    END LOOP;
END
$$ language 'plpgsql';

不用说,这段代码不起作用。

1 回复 | 直到 7 年前

a_horse_with_no_name 7 年前

您可以使用 window functions . 显然你想请客 null 和只有空格的字符串是一样的,所以需要对它们进行规范化。

以下查询将生成所需的数字,如果单个字符串出现多次,还将提供信息:

select id, code, 
       row_number() over (partition by coalesce(nullif(trim(code),''),'') order by id) as counter,
       count(*) over (partition by coalesce(nullif(trim(code),''),'')) as num_rows
from tablename
order by id;

通过示例输入,将返回:

id | code | counter | num_rows
---+------+---------+---------
 1 |      |       1 |        2
 2 |      |       2 |        2
 3 | FOO  |       1 |        2
 4 | FOO  |       2 |        2
 5 | BAR  |       1 |        2
 6 | BOB  |       1 |        1
 7 | BAR  |       2 |        2

表达式 coalesce(nullif(trim(code),''),'') “正常化” NULL 仅由空格组成的值和字符串,例如。 ' ' 到空字符串 '' .

现在可以使用它来更新表。由于您只想将计数器添加到多次出现的值,因此需要将更新限制为:

update tablename as tn
  set code = concat(trim(tn.code), '(', x.counter, ')')
from (
  select id, code, 
         row_number() over (partition by coalesce(nullif(trim(code),''),'') order by id) as counter,
         count(*) over (partition by coalesce(nullif(trim(code),''),'')) as num_rows
  from tablename
) x
where x.id = tn.id
  and x.num_rows > 1;

更新后,您的示例如下所示:

id | code  
---+-------
 1 | (1)   
 2 | (2)   
 3 | FOO(1)
 4 | FOO(2)
 5 | BAR(1)
 6 | BOB   
 7 | BAR(2)

联机示例: http://rextester.com/WGAL85544