回答

收藏

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

技术问答 技术问答 322 人阅读 | 0 人回复 | 2023-09-14

我正在尝试在postgresql中创建数据透视表类型视图,并且快要完成了!这是基本查询:
& {! I/ L' a, rselect
/ S6 y" S4 p* A9 u2 Dacc2tax_node.acc, tax_node.name, tax_node.rank
* c4 d4 v" N7 N' ^: e5 q& Mfrom 8 d& G. U2 ^( Y. Z) p9 c9 d
tax_node, acc2tax_node , j* j& p" p$ P5 |
where 4 u- o/ k; r/ x% X0 B/ ^
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';' ^" I- q0 ^; j. N; |; x5 C
和数据:' g# Y7 u. ~. O
   acc    |          name           |     rank     
8 c, ?2 \* F7 {2 l----------+-------------------------+--------------
/ ~3 R2 r$ ?/ u7 } AJ012531 | Paromalostomum fusculum | species) c4 t# i8 J) w. c' ]
AJ012531 | Paromalostomum          | genus
7 f$ ?2 t! \- L3 `8 z- q8 X AJ012531 | Macrostomidae           | family
4 i" t8 h/ X7 u: n: M! V: y AJ012531 | Macrostomida            | order
' i7 v/ [) \6 m; z* ~* u* ^1 v AJ012531 | Macrostomorpha          | no rank
+ _5 }. U3 g* T, r; U6 S/ N) L AJ012531 | Turbellaria             | class  w- s' i- h; C8 s1 X
AJ012531 | Platyhelminthes         | phylum
( ~) g% ]( I. y) v' M8 c AJ012531 | Acoelomata              | no rank7 C0 P" p1 H8 q) p5 z
AJ012531 | Bilateria               | no rank
9 O6 F# G, i+ k+ U5 k7 m# { AJ012531 | Eumetazoa               | no rank
( N( P% ?- d/ Y& T AJ012531 | Metazoa                 | kingdom8 J6 Z( p; Z* z6 ]
AJ012531 | Fungi/Metazoa group     | no rank8 e6 j6 Q! F& |/ n3 `
AJ012531 | Eukaryota               | superkingdom
# w! a" }/ c4 s& P: u9 y AJ012531 | cellular organisms      | no rank% [, X2 `8 i+ R  v- n0 V4 @& _
我想要得到的是以下内容:
7 x. b1 s0 |8 D* x0 d0 `/ M  Eacc      | species                  | phylum" K& G! Q7 C, V9 j' u
AJ012531 | Paromalostomum fusculum  | Platyhelminthes# s$ L" `7 P! v6 t: o  r$ y
我正在尝试使用CASE WHEN进行此操作,因此我得到了以下信息:
" S% u9 A# d5 vselect
$ Y6 x- q9 D& ~  o; E% A2 Vacc2tax_node.acc, ! o2 U" \+ \7 ]! O9 F$ P( s! q
CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as species, & _- |6 D2 Y& c6 ?0 w/ m
CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as phylum
3 c& w% i3 m0 a/ l6 Efrom : ?3 H7 \/ S- X' k
tax_node, acc2tax_node   R1 J0 l4 M4 M) {: r  H
where
2 `* U$ A5 S# e- u2 p* \tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';, M) N4 }5 o/ q
这给了我输出:
; n( d" d1 C: c   acc    |         species         |     phylum      8 |7 X7 i+ b4 y* K+ u# a; B) b4 j& P  a
----------+-------------------------+-----------------
& \" o# X$ D4 Q6 f6 g' p AJ012531 | Paromalostomum fusculum |
% T! ~5 ]( I; l0 h0 H AJ012531 |                         |
# b) G3 O& X$ P) I% T8 ` AJ012531 |                         | 8 |/ s3 }# c: q& y5 D- X, m
AJ012531 |                         | - }) E/ f; Y6 s( |$ f: k
AJ012531 |                         |
7 \3 s, _1 V7 U0 B9 {3 C( }- i AJ012531 |                         |
5 @1 F% E) e" C5 } AJ012531 |                         | Platyhelminthes4 v" t' m; ]8 _' x; f5 O
AJ012531 |                         |
' `% N. N, g5 w- Y AJ012531 |                         |
  @7 a7 h3 p% u! ^% [ AJ012531 |                         | 1 w% T4 m4 x' I2 l) y) M  N
AJ012531 |                         | # t! N& P# i- H% X0 j
AJ012531 |                         |
! j8 r1 L9 D$ H8 K9 f7 w6 Q AJ012531 |                         | ' V( u, e0 B& u9 O8 p# h8 q
AJ012531 |                         |
. `" p- s" N  C- C$ O$ E) b现在我知道我必须在某个时候按acc分组,所以我尝试
$ Q1 {" A7 T) e! p; D- ?+ ]select : I0 e. x# t0 p  c
acc2tax_node.acc,
5 S# {/ r% w7 x  R- t9 Z; @7 eCASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as sp,
5 L3 V+ B* p4 P2 TCASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as ph
5 u# k: q( x0 g7 [' ffrom + x- F0 J+ k( ^2 \  f4 F
tax_node, acc2tax_node & _7 W' C' x8 `) q
where ' r$ A6 E& `+ A4 E- M- {: m: W2 T
tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531'
5 q8 W% d% t$ l6 y# j6 _  W) Hgroup by acc2tax_node.acc;3 ^* L0 l0 j) g( i
但我感到恐惧
4 y0 U- C7 v0 }9 BERROR:  column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function
# j4 A1 ^$ |% O: z% ?我能够找到的所有先前示例在CASE语句周围都使用了SUM()之类的东西,所以我想那是聚合函数。我尝试使用FIRST():' W- r# a* p5 ?, |7 X* }
select # ]- @% [: {5 q4 x/ S" q
acc2tax_node.acc,
* \7 i, b  b# M( t+ ?5 T1 [FIRST(CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END) as sp, ; Z6 `" l; I) T4 r- z4 K
FIRST(CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END) as ph
1 |6 \+ ~9 ~2 x3 y1 ^3 v4 afrom tax_node, acc2tax_node where tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' group by acc2tax_node.acc;6 R0 n7 }: [* G: d2 Z! K0 X; c
但得到错误:$ c* X( y) H) j
ERROR:  function first(character varying) does not exist& u! r" L4 \6 `! P, }
谁能提供任何提示?
# b0 r7 V- j: x- c1 }5 e$ L               
6 }: ^' f3 w9 O* i- x解决方案:. H8 @: U9 ^2 @( t
                ) R& y4 q4 D5 L- i$ j
7 D" K+ X% _, K$ ?

3 h# D" E0 n9 V; Q5 S' i2 n1 [                使用MAX()或MIN(),而不是FIRST()。在这种情况下,每个组值在列中将具有所有NULL,但最多只有一个不为null的值。根据定义,这是该组值的MIN和MAX(排除所有空值)。
分享到:
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则