sql - 是否可以在 PostgreSQL 中单独查询任意数量的列？

Question

我们有一个包含多个列的表，每个列都包含重复项。我们需要以这样一种方式查询它，即结果仅包含所有列的任意子集的每一列中的不同元素，而不仅仅是单个元组。

给定下表，让我用一个例子来说明这个问题

 color | type  | vendor | price 
-------+-------+--------+-------
 red   | apple | smith  |     1
 red   | apple | cooper |     2
 red   | pear  | smith  |     3
 red   | pear  | cooper |     4
 green | apple | smith  |     1
 green | apple | cooper |     2
 green | pear  | smith  |     3
 green | pear  | cooper |     4

要求不同元素的典型查询(总是选择最便宜的元素)

SELECT DISTINCT ON (color, type)
  color,
  type,
  vendor,
  price
FROM fruits
ORDER BY
  color,
  type,
  price;

结果如下

 color | type  | vendor | price 
-------+-------+--------+-------
 green | apple | smith  |     1
 green | pear  | smith  |     3
 red   | apple | smith  |     1
 red   | pear  | smith  |     3

然而，所需的结果在颜色和类型两列上应该是不同的，即

 color | type  | vendor | price 
-------+-------+--------+-------
 green | apple | smith  |     1
 red   | pear  | smith  |     3

我们想知道是否有适用于任意数量列的解决方案，即 DISTINCT ON INDIVIDUALLY (color, type) ...。如果这不可能，下一个最佳解决方案将使用给定的最大列数，即 SELECT DISTINCT ON MAX_INDIVIDUAL ($COLUMNS) 其中 $COLUMNS 可以是 1， 2 或 3 列，但不会更多。最低要求是固定列数的解决方案。然而，后者可以使用子查询简单地实现。

上面例子中的按价格排序的问题不需要用数学上精确的方式来处理。

上面的表格可以设置

DROP TABLE IF EXISTS fruits;

CREATE TABLE fruits (
  color TEXT,
  type TEXT,
  vendor TEXT,
  price INTEGER
);

INSERT INTO fruits VALUES
  ('red', 'apple', 'smith', 1),
  ('red', 'apple', 'cooper', 2),
  ('red', 'pear', 'smith', 3),
  ('red', 'pear', 'cooper', 4),
  ('green', 'apple', 'smith', 1),
  ('green', 'apple', 'cooper', 2),
  ('green', 'pear', 'smith', 3),
  ('green', 'pear', 'cooper', 4);

注意:我们知道已经提出了几个乍一看非常相似的问题，但它们都没有涵盖上述问题的一般性。

Answer 1

类似于:

select color, type, max(vendor), min(price) 
from (
    select color, type, vendor, price
         , dense_rank() over (order by color) as rn1
         , dense_rank() over (order by type) as rn2 
    from fruits
) x
where rn1 = rn2 
group by color, type

应该给出颜色和类型的样本。聚合(随机选择)应该为每个样本选择一个值。