sql - 一个只允许一个唯一输入的聚合函数

Question

我经常发现自己在 group by 子句中添加我确信是唯一的表达式。有时结果证明我错了 - 因为我的 SQL 中有错误或错误的假设，并且该表达式并不是真正唯一的。

在很多情况下，我宁愿这会产生一个 SQL 错误，也不愿静静地扩展我的结果集，有时甚至是非常微妙地。

我希望能够做这样的事情:

select product_id, unique description from product group by product_id

但显然我自己无法实现 - 但可以在某些数据库上使用用户定义的聚合来实现几乎同样简洁的东西。

只允许一个唯一输入值的特殊聚合是否普遍适用于所有版本的 SQL？如果是这样，现在可以在大多数数据库上实现这样的事情吗？ null 值应该像任何其他值一样考虑 - 不同于内置聚合 avg 通常工作的方式。 (我已经为 postgres 和 Oracle 添加了实现方法的答案。)

以下示例旨在说明如何使用聚合，但这是一个简单的示例，其中很明显哪些表达式应该是唯一的。真正的使用更有可能是在更大的查询中，在这些查询中更容易对唯一性做出错误的假设

表格:

 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

查询:

> 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

错误案例:

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

Answer 1

ORACLE 解决方案是

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;

而不是 to_char(1/0) [引发 DIVIDE_BY_ZERO 错误]，您可以使用一个简单的函数来执行

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;

您可以使用用户定义的聚合，但我担心在 SQL 和 PL/SQL 之间切换对性能的影响。