使用 CASE WHEN 在 postgresql 中创建数据透视表的正确方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2477231/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
correct way to create a pivot table in postgresql using CASE WHEN
提问by mojones
I am trying to create a pivot table type view in postgresql and am nearly there! Here is the basic query:
我正在尝试在 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';
And the data:
和数据：
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
I am trying to do this with CASE WHEN, so I've got as far as the following:
我试图用 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';
Which gives me the output:
这给了我输出：
acc | species | phylum
----------+-------------------------+-----------------
AJ012531 | Paromalostomum fusculum |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | | Platyhelminthes
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
Now I know that I have to group by acc at some point, so I try
现在我知道我必须在某个时候按 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;
But I get the dreaded
但我感到害怕
ERROR: column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function
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():
我能找到的所有前面的例子都在 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;
but get the error:
但得到错误：
ERROR: function first(character varying) does not exist
Can anyone offer any hints?
任何人都可以提供任何提示吗？
采纳答案by Matthew Wood
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).
使用 MAX() 或 MIN()，而不是 FIRST()。在这种情况下，您将在每个组值的列中拥有所有 NULL，除了最多一个具有非空值的值。根据定义，这是该组值的 MIN 和 MAX（排除所有空值）。
回答by Frank Heikens
PostgreSQL does have a couple of functions for pivot queries, see this article at Postgresonline. You can find these functions in the contrib.
PostgreSQL 确实有几个用于数据透视查询的函数，请参阅Postgresonline 上的这篇文章。您可以在contrib 中找到这些功能。
回答by The G Man
Execute:
执行：
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, ...)
回答by Alkini
As Matthew Wood pointed out, use MIN() or MAX(), not FIRST():
正如马修伍德指出的那样，使用 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;
回答by Quassnoi
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 '
回答by mojones
Further info as requested (in a reply rather than a comment for nice formatting):
根据要求提供更多信息（在回复中，而不是对良好格式的评论）：
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
