聚合严格最小值和最大值

聚合函数 `strict_min` 和 `strict_max` 的行为类似于它们的内置对应函数，除了它们在任何输入为 NULL 时将返回 NULL。此实现比内置聚合函数明显慢，并且无法利用索引支持。

严格最小值和最大值聚合函数实现

片段

严格最小值和最大值聚合函数

适用于 PostgreSQL

至少回溯到 8.4

编写语言

SQL

依赖

无

-- If no values have been delivered to the aggregate, the internal state is the
-- NULL array.  If a null values has been delivered, it is an array with one
-- element, which is NULL.  Otherwise, it is an array with one element,
-- the least/greatest seen to this point.
CREATE OR REPLACE FUNCTION strict_min_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE
            WHEN $1 IS NULL THEN ARRAY[$2]
            WHEN $1[1] IS NULL THEN $1
            WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
            ELSE ARRAY[least($1[1],$2)] END ;
$$;

CREATE OR REPLACE FUNCTION strict_agg_final (anyarray)
RETURNS anyelement LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE when $1 is null then NULL else $1[1] END ;
$$;

CREATE AGGREGATE strict_min (anyelement) (
        sfunc     = strict_min_agg,
        stype     = anyarray,
        finalfunc = strict_agg_final
);

CREATE OR REPLACE FUNCTION strict_max_agg (anyarray,anyelement )
RETURNS anyarray LANGUAGE sql IMMUTABLE AS $$
        SELECT CASE
            WHEN $1 IS NULL THEN ARRAY[$2]
            WHEN $1[1] IS NULL THEN $1
            WHEN $2 IS NULL THEN ARRAY[$2] -- use $2 not NULL to preserve type
            ELSE ARRAY[greatest($1[1],$2)] END ;
$$;

CREATE AGGREGATE strict_max (anyelement) (
        sfunc     = strict_max_agg,
        stype     = anyarray,
        finalfunc = strict_agg_final
);

并行启用实现

-- for versions 9.6 add two functions and change the aggregates.
CREATE FUNCTION strict_max_combine(anyarray, anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
  select case 
  when $1 is null then $2
  when $2 is null then $1
  when $1[1] is null then $1
  when $2[1] is null then $2
  else ARRAY[greatest($1[1],$2[1])] END ;
$_$;

CREATE FUNCTION strict_min_combine(anyarray, anyarray) RETURNS anyarray
    LANGUAGE sql IMMUTABLE
    AS $_$
  select case 
  when $1 is null then $2
  when $2 is null then $1
  when $1[1] is null then $1
  when $2[1] is null then $2
  else ARRAY[least($1[1],$2[1])] END ;
$_$;

CREATE AGGREGATE strict_max(anyelement) (
    SFUNC = strict_max_agg,
    STYPE = anyarray,
    FINALFUNC = strict_agg_final,
    COMBINEFUNC = strict_max_combine,
    PARALLEL = safe
);

CREATE AGGREGATE strict_min(anyelement) (
    SFUNC = strict_min_agg,
    STYPE = anyarray,
    FINALFUNC = strict_agg_final,
    COMBINEFUNC = strict_min_combine,
    PARALLEL = safe
);

用法

select strict_min(x) from (values (1),(-4),(NULL),(-87)) f(x);

SELECT group_id, strict_max(some_date) FROM t group by group_id;