Aggregate function that allows only one unique input

Question

I often find that I am adding expressions to a group by clause, which I am sure is unique. Sometimes it turns out that I'm wrong - because of an error in my SQL or an erroneous assumption, and this expression is really not unique.

There are many cases where I would really like for this to generate an SQL error, rather than expanding my result set silently, and sometimes very subtly.

I would like to do something like:

 select product_id, unique description from product group by product_id 

but obviously I can’t implement this myself, but something almost like compressed can be implemented using user-defined aggregates in some databases.

Would a special aggregate that allows only one unique input value in all versions of SQL be useful? If so, can such a thing be implemented now in most databases? null values ​​should be considered like any other value - unlike the way the avg built-in aggregate works. (I added answers with implementation methods for postgres and Oracle.)

The following example is intended to show how an aggregate will be used, but this is a simple case when it is obvious which expressions should be unique. The real use is likely to be in large queries, where it is easier to err on the assumptions about uniqueness

tables:

  product_id | description ------------+------------- 1 | anvil 2 | brick 3 | clay 4 | door sale_id | product_id | cost ---------+------------+--------- 1 | 1 | £100.00 2 | 1 | £101.00 3 | 1 | £102.00 4 | 2 | £3.00 5 | 2 | £3.00 6 | 2 | £3.00 7 | 3 | £24.00 8 | 3 | £25.00 

inquiries:

 > select * from product join sale using (product_id); product_id | description | sale_id | cost ------------+-------------+---------+--------- 1 | anvil | 1 | £100.00 1 | anvil | 2 | £101.00 1 | anvil | 3 | £102.00 2 | brick | 4 | £3.00 2 | brick | 5 | £3.00 2 | brick | 6 | £3.00 3 | clay | 7 | £24.00 3 | clay | 8 | £25.00 > select product_id, description, sum(cost) from product join sale using (product_id) group by product_id, description; product_id | description | sum ------------+-------------+--------- 2 | brick | £9.00 1 | anvil | £303.00 3 | clay | £49.00 > select product_id, solo(description), sum(cost) from product join sale using (product_id) group by product_id; product_id | solo | sum ------------+-------+--------- 1 | anvil | £303.00 3 | clay | £49.00 2 | brick | £9.00 

error:

 > select solo(description) from product; ERROR: This aggregate only allows one unique input 

Answer 1

Here is my implementation for postgres (edited to handle null as a unique value):

 create function solo_sfunc(inout anyarray, anyelement) language plpgsql immutable as $$ begin if $1 is null then $1[1] := $2; else if ($1[1] is not null and $2 is null) or ($1[1] is null and $2 is not null) or ($1[1]!=$2) then raise exception 'This aggregate only allows one unique input'; end if; end if; return; end;$$; create function solo_ffunc(anyarray) returns anyelement language plpgsql immutable as $$ begin return $1[1]; end;$$; create aggregate solo(anyelement) (sfunc=solo_sfunc, stype=anyarray, ffunc=solo_ffunc); 

sample tables for testing:

 create table product(product_id integer primary key, description text); insert into product(product_id, description) values (1, 'anvil'), (2, 'brick'), (3, 'clay'), (4, 'door'); create table sale( sale_id serial primary key, product_id integer not null references product, cost money not null ); insert into sale(product_id, cost) values (1, '100'::money), (1, '101'::money), (1, '102'::money), (2, '3'::money), (2, '3'::money), (2, '3'::money), (3, '24'::money), (3, '25'::money); 

Answer 2

ORACLE Solution

 select product_id, case when min(description) != max(description) then to_char(1/0) else min(description) end description, sum(cost) from product join sale using (product_id) group by product_id; 

Instead of to_char (1/0) [which causes a DIVIDE_BY_ZERO error) you can use a simple function that does

 CREATE OR REPLACE FUNCTION solo (i_min IN VARCHAR2, i_max IN VARCHAR2) RETURN VARCHAR2 IS BEGIN IF i_min != i_max THEN RAISE_APPLICATION_ERROR(-20001, 'Non-unique value specified'); ELSE RETURN i_min; END; END; / select product_id, solo(min(description),max(description)) end description, sum(cost) from product join sale using (product_id) group by product_id; 

You can use a user-defined aggregate, but I would be concerned about the performance impact on SQL and PL / SQL.

Answer 3

You must define the UNIQUE restriction (product_id, description), then you do not have to worry about the fact that there are two descriptions for one product.

Answer 4

And here is my implementation for Oracle - unfortunately, I think you need one implementation for each base type:

 create type SoloNumberImpl as object ( val number, flag char(1), static function ODCIAggregateInitialize(sctx in out SoloNumberImpl) return number, member function ODCIAggregateIterate( self in out SoloNumberImpl, value in number ) return number, member function ODCIAggregateTerminate( self in SoloNumberImpl, returnValue out number, flags in number ) return number, member function ODCIAggregateMerge( self in out SoloNumberImpl, ctx2 in SoloNumberImpl ) return number ); / create or replace type body SoloNumberImpl is static function ODCIAggregateInitialize(sctx in out SoloNumberImpl) return number is begin sctx := SoloNumberImpl(null, 'N'); return ODCIConst.Success; end; member function ODCIAggregateIterate( self in out SoloNumberImpl, value in number ) return number is begin if self.flag='N' then self.val:=value; self.flag:='Y'; else if (self.val is null and value is not null) or (self.val is not null and value is null) or (self.val!=value) then raise_application_error( -20001, 'This aggregate only allows one unique input' ); end if; end if; return ODCIConst.Success; end; member function ODCIAggregateTerminate( self in SoloNumberImpl, returnValue out number, flags in number ) return number is begin returnValue := self.val; return ODCIConst.Success; end; member function ODCIAggregateMerge( self in out SoloNumberImpl, ctx2 in SoloNumberImpl ) return number is begin if self.flag='N' then self.val:=ctx2.val; self.flag=ctx2.flag; elsif ctx2.flag='Y' then if (self.val is null and ctx2.val is not null) or (self.val is not null and ctx2.val is null) or (self.val!=ctx2.val) then raise_application_error( -20001, 'This aggregate only allows one unique input' ); end if; end if; return ODCIConst.Success; end; end; / create function SoloNumber (input number) return number aggregate using SoloNumberImpl; /