postgresql拓展if、ifnull、group

postgresql版本是8.2.15。

最近陆续有数据分析师从impala、hive转到查询gpdb，gpdb虽然能够支持在查询语句中带多个distinct，但是缺少相应的if、ifnull、group_concat函数，正好年后有空就拓展一些函数给他们用

1. to_array聚集函数

CREATE AGGREGATE pg_catalog.to_array (anyelement)

(

    sfunc = array_append,

    stype = anyarray,

    initcond = '{}'

);

2. if函数

create function if(expr bool, true_result anyelement, false_result anyelement) RETURNS anyelement AS

$$

BEGIN

  if expr then return true_result;

  else return false_result;

  end if;

END;

$$

LANGUAGE plpgsql;

3. ifnull函数

create function ifnull(value anyelement, null_value anyelement) RETURNS anyelement AS

$$

BEGIN

  if value is null then return null_value;

  else return value;

  end if;

END;

$$

LANGUAGE plpgsql;

if第2、3参数和ifnull的两个参数需要指明其中一个参数的类型，而且类型要一样，比如ifnull('a'::text, 'b')，由于两个参数都是输出，因此他们的类型必须一致，指出其中一个参数类型，另一个参数的类型也就确定了。常量参数必须要指明其中一个参数的类型，但表字段本身就有类型，因此不需要特意指出类型，如ifnull(city_name, 'b')，同样也要注意两个参数类型一致，不能写ifnull(city_name, 3)

其中用聚组函数to_array配合distinct语句、array_to_string函数来模拟group_concat函数的功能，例如group_concat(city_name, ',')可以改成array_to_string(to_array(distinct city_name), ',')

为什么不直接写一个group_concat聚组函数呢？因为pg的的聚组函数只能从一种类型转变成另一种，不能连续转变成第二种，因此普通字段需要先转变成数组，然后再变成字符串。

那么为什么不先把字段变成字符串，然后拼接在一起呢？因为postgresql8.2.15版本没有任何一个去重函数，只能用distinct语句来去重，同时distinct只能用在“只有一个参数的聚组函数”中，对于group_concat(distinct city_name, ',')这条语句，group_concat有两个参数，语法有错。这么一来二去，只能用两层函数去实现group_concat了

2017年2月9号发现postgresql原生就有一个array_agg聚合函数，相当于to_array的功能

distinct和group的区别

distinct: 适用于重复度高、值可枚举、种类少的字段，因为ditinct保存在内存中，如果种类太多内存会爆，去重速度快

group: 生成临时表，速度慢，没有内存爆的问题

巴特西

postgresql拓展if、ifnull、group_concat函数

1. to_array聚集函数

2. if函数

3. ifnull函数

distinct和group的区别

最新文章

热门文章