sql - 使用 CASE WHEN 在 postgresql 中创建数据透视表的正确方法

Question

我正在尝试在 postgresql 中创建一个数据透视表类型的 View ，并且快完成了!这是基本查询:

select 
acc2tax_node.acc, tax_node.name, tax_node.rank 
from 
tax_node, acc2tax_node 
where 
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';

和数据:

   acc    |          name           |     rank     
----------+-------------------------+--------------
 AJ012531 | Paromalostomum fusculum | species
 AJ012531 | Paromalostomum          | genus
 AJ012531 | Macrostomidae           | family
 AJ012531 | Macrostomida            | order
 AJ012531 | Macrostomorpha          | no rank
 AJ012531 | Turbellaria             | class
 AJ012531 | Platyhelminthes         | phylum
 AJ012531 | Acoelomata              | no rank
 AJ012531 | Bilateria               | no rank
 AJ012531 | Eumetazoa               | no rank
 AJ012531 | Metazoa                 | kingdom
 AJ012531 | Fungi/Metazoa group     | no rank
 AJ012531 | Eukaryota               | superkingdom
 AJ012531 | cellular organisms      | no rank

我想得到的是以下内容:

acc      | species                  | phylum
AJ012531 | Paromalostomum fusculum  | Platyhelminthes

我正在尝试使用 CASE WHEN 执行此操作，所以我已经了解了以下内容:

select 
acc2tax_node.acc, 
CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as species, 
CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as phylum 
from 
tax_node, acc2tax_node 
where 
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';

这给了我输出:

   acc    |         species         |     phylum      
----------+-------------------------+-----------------
 AJ012531 | Paromalostomum fusculum | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | Platyhelminthes
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 
 AJ012531 |                         | 

现在我知道我必须在某个时候按 acc 分组，所以我尝试

select 
acc2tax_node.acc, 
CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as sp, 
CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as ph 
from 
tax_node, acc2tax_node 
where 
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' 
group by acc2tax_node.acc;

但我觉得很可怕

ERROR:  column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function

我能找到的所有前面的例子都在 CASE 语句周围使用了类似 SUM() 的东西，所以我猜这就是聚合函数。我试过使用 FIRST():

select 
acc2tax_node.acc, 
FIRST(CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END) as sp, 
FIRST(CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END) as ph 
from tax_node, acc2tax_node where tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' group by acc2tax_node.acc;

但出现错误:

ERROR:  function first(character varying) does not exist

任何人都可以提供任何提示吗？

Answer 1

使用 MAX() 或 MIN()，而不是 FIRST()。在这种情况下，您将在每个组值的列中拥有所有 NULL，除了最多一个具有非空值的值。根据定义，这是该组值的最小值和最大值(排除所有空值)。