使用CASE WHEN在postgresql中创建数据透视表的正确方法 [英] correct way to create a pivot table in postgresql using CASE WHEN

问题描述

我正在尝试在postgresql中创建数据透视表类型视图，并且快要完成了！这是基本查询:

I am trying to create a pivot table type view in postgresql and am nearly there! Here is the basic query:

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

我想要得到的是以下内容:

What I am trying to get is the following:

acc      | species                  | phylum
AJ012531 | Paromalostomum fusculum  | Platyhelminthes

我正在尝试使用CASE WHEN进行此操作，因此我得到以下信息:

I am trying to do this with CASE WHEN, so I've got as far as the following:

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分组，所以我尝试

Now I know that I have to group by acc at some point, so I try

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():

All the previous examples I've been able to find use something like SUM() around the CASE statements, so I guess that is the aggregate function. I have tried using 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

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

Can anyone offer any hints?

推荐答案

使用MAX()或MIN()，而不要使用FIRST().在这种情况下，每个组值在列中将具有所有NULL，但最多只有一个不为null的值.根据定义，这是该组值的MIN和MAX(排除所有空值).

Use MAX() or MIN(), not FIRST(). In this scenario, you will have all NULLs in the column per each group value except for, at most, one with a not null value. By definition, this is both the MIN and the MAX of that set of values (all nulls are excluded).

这篇关于使用CASE WHEN在postgresql中创建数据透视表的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！