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

Question

我正在尝试在PostgreSQL中创建一个类似于数据透视表的视图，而我已经接近成功了！以下是基本查询语句：

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

有人能提供一些提示吗？

- mojones

---

请问您能否发布此查询的结果：SELECT * FROM acc2tax_node WHERE acc = 'AJ012531'？ - Quassnoi

Answer 1

使用MAX()或MIN()，不要使用FIRST()。在此情况下，每个组值的列中将有所有NULL，除了最多一个具有非空值。根据定义，这是该值集合的MIN和MAX（所有null都被排除）。

- Matthew Wood

---

非常好，谢谢。由于我使用字符串值，出于某种原因，我认为MAX（）不起作用。 - mojones

Answer 2

PostgreSQL有几个用于透视查询的函数，请参阅Postgresonline上的这篇文章。您可以在contrib中找到这些函数。

- Frank Heikens

---

是的，我怀疑正确的做法是使用scrosstab。但出于自己的教育目的，我仍然想弄清楚我在这里做错了什么。 - mojones

Answer 3

如Matthew Wood所指出的那样，应该使用MIN()或MAX()而不是FIRST()：

SELECT 
    an.acc, 
    MAX(
        CASE tn.rank 
            WHEN 'species' THEN tn.name 
            ELSE NULL 
        END
    ) AS species, 
    MAX(
        CASE tn.rank 
            WHEN 'phylum' THEN tn.name 
            ELSE NULL 
        END
    ) AS phylum 
FROM tax_node tn, 
    acc2tax_node an
WHERE tn.taxid = an.taxid 
    and an.acc = 'AJ012531' 
GROUP by an.acc;

- Alkini

Answer 4

SELECT  atn.acc, ts.name AS species, tp.name AS phylum
FROM    acc2tax_node atn
LEFT JOIN
        tax_node ts
ON      ts.taxid = atn.taxid
        AND ts.rank = 'species'
LEFT JOIN
        tax_node tp
ON      tp.taxid = atn.taxid
        AND tp.rank = 'phylum'
WHERE   atn.acc = 'AJ012531 '

- Quassnoi

Answer 5

根据要求提供更多信息（为了良好的格式，以回复而不是评论的形式）：

SELECT * FROM acc2tax_node WHERE acc = 'AJ012531';

   acc    | taxid  
----------+--------
 AJ012531 |  66400
 AJ012531 |  66399
 AJ012531 |  39216
 AJ012531 |  39215
 AJ012531 | 166235
 AJ012531 | 166384
 AJ012531 |   6157
 AJ012531 |  33214
 AJ012531 |  33213
 AJ012531 |   6072
 AJ012531 |  33208
 AJ012531 |  33154
 AJ012531 |   2759
 AJ012531 | 131567

- mojones

Answer 6

执行：

SELECT report.* FROM crosstab(
 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';
) AS report(species text, enus text, family text, ...)

- The G Man