PostgreSQL函数（存储过程）--案例分析

前言

　　最近操作数据库时遇到一个问题，来这里记录下，分享下心得。

　　情景重现：数据库有张表，有一亿五千万条数据，其中都为同一年数据，由于建表时忘记添加唯一索引导致有部分数据重复。现在需要统计某一字段为某个值的数据条数。

　　表字段定义如下（由于原字段涉及公司数据，因此模拟重现）：

　　含义：每天凌晨爬取各地孩童前一天的出生情况,其中每次爬取入库的时间都在前一天内。

　　　　数据库名：test

　　　　数据表名：users

　　这其中有不明数量的数据重复，提到数据库数据去重，开发人员第一个想到的应该是distinct和group by

distinct实现

create table tmp_users as select distinct * from users;

group by实现

create table tmp_users as select card_id, name, from users group by card_id, name, age;

distinct与group by的分析

　　distinct是专门用来去重复的，group by利用字段进行分组达到去重的效果，其中数据量多时group by的执行效率明显高于distinct，感兴趣的可以实验下。

　　那么问题来了，执行这任意命令可以得到结果吗？当面对过亿的数据时执行这两句查询语句都会让你等到崩溃，甚至由于高负载导致服务器宕机，运气好等个几个小时出来结果，运气不好直接程序报错。

分析问题

1. 数据量庞大。
2. 每次数据写入的出生日期都在同一天。
3. 未知执行时间。

分析结果

　　由于每一天入库的数据都在同一天，因此同一片内存扇区存储的数据的birth_date大体都为某个时间段。由于磁盘设计原理，游标通过磁盘旋转找到某一地址，由于同一段时间都在附近节约游标定位成本，这就是读取某一地址内容附近地址内容也很容易读取的原因。再加上数据的预读特性，我决定对表的数据进行按月份分段进行group by去重后写入一个新表，这样就可以得到自己想要的去重数据的最终结果表。

代码实现

既然分析结果确定方法那我们就马上实现吧！

12个月如果手动执行12遍就太笨了，我想到用psql的函数实现，严格意义psql没有存储过程的定义，因为函数已经包含psql的存储过程。

存储过程: 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

-- 创建名为dis_users的函数
create or replace function dis_users() 
returns void as
$$
declare
  year text := '2017';
  month text;
  datestr text;
begin
  execute 'create table tmp_users as select cart_id, name, birth_date from users limit 1';
  execute 'delete from tmp_users';
  for i in 1..12 loop
    month := cast(i as text);
    if i<10 then month := '0'||month;
    end if;
    datestr := year||'-'||month||'%';
    raise notice 'value %', datestr;
    execute 'insert into tmp_users (select cart_id, name, birth_date from users group by card_id, name, birth_date having cast(born as text) like '''||datestr||''')';
  end loop;
end;
$$
language plpgsql;
-- 执行函数
select dis_users();

代码分析

psql函数的书写框架大致是：

-- 创建和替换函数
create or replace function fun_name()
-- 返回值类型 
returns void as
$$
-- 定义变量
declare 
    name text := 'Rnet';
begin
    -- 函数体
    --startment--
end;
$$
-- 指定sql语句规范，各个sql应用的实现可能和标准的sql规范有所差异，但都大同小异。
language plpgsql;
-- 执行函数
select fun_name();

关键内容

-- 变量赋值
name := ‘Rnet’;
-- 执行sql命令
execute 'select ...';
-- 字符串的连接
c := a || b;
-- for循环
for i in 1..12 loop
end loop;
-- 输出提示信息
raise notice 'message： %', c;
-- 条件判断
if a<b then --startment--;
end if;
-- 按条件分组
group by birth_date having birth_date like '2017%'

最后

程序执行完大约用了1个多小时，去重五百多万数据，作为ACMer面对这么久的执行时间还是不能忍受的，如果你有更好的方法或者博文有错误欢迎指教和讨论，thanks very much!