SQL聚合函数选择唯一值

Question

我有一个包含两列的行集:technical_id和natural_id。行集实际上是复杂查询的结果。假定列值之间的映射是双射的(即，对于具有相同technical_id的两行，natural_id也是相同的，对于不同的technical_id，natural_id也是不同的)。由于原始查询中的联接，因此(technical_id,natural_id)对在行集中不是唯一的。例子:

with t (technical_id, natural_id, val) as (values
  (1, 'a', 1),
  (1, 'a', 2),
  (2, 'b', 3),
  (2, 'b', 2),
  (3, 'c', 0),
  (3, 'c', 1),
  (4, 'd', 1)
)

不幸的是，双射仅由应用程序逻辑来实现。 natural_id实际上是从多个表中收集的，并使用基于 coalesce的表达式组成，因此它的唯一性几乎不能通过db约束来实现。

我需要通过 technical_id聚合行集的行，假设 natural_id是唯一的。如果不是(例如，如果将元组 (4, 'x', 1)添加到示例数据中)，则查询应失败。在理想的SQL世界中，我将使用一些假设的聚合函数:

select technical_id, only(natural_id), sum(val)
from t
group by technical_id;

我知道SQL中没有这样的功能。有其他选择或解决方法吗？特定于Postgres的解决方案也是可以的。

请注意，尽管 group by technical_id, natural_id或 select technical_id, max(natural_id)在良好的情况下也能很好地工作，但两者都 Not Acceptable (首先是因为 technical_id在所有情况下在结果上都必须唯一，其次是因为该值可能是随机的并且掩盖了数据不一致)。

感谢您的提示:-)

更新:预期的答案是

technical_id,v,sum
1,a,3
2,b,5
3,c,1
4,d,1

或也存在 4,x,1时失败。

Answer 1

您可以使用以下方法仅获取“唯一的”自然ID:

select technical_id, max(natural_id), sum(val)
from t
group by technical_id
having min(natural_id) = max(natural_id);

如果您希望查询实际上失败，则很难保证。这是一种简单的方法:

select technical_id, max(natural_id), sum(val)
from t
group by technical_id
having (case when min(natural_id) = max(natural_id) then 0 else 1 / (count(*) - count(*)) end) = 0;

还有一个db <> fiddle来说明 this。