Vertica:如何按某种顺序连接值? [英] Vertica: how can you concate values by some order?
问题描述
假设您有列
ID | A | B | C
1 | 3 | 1 | 2
2 | 5 | 9 | 1
3 | 1 | 2 | 3
,并且您希望将各列连接起来,以使最终结果看起来像
and you want the columns concatenated such that the end result would look like
ID | ABC_value_DESC | ABC_value_DESC_colnames
1 | 3,2,1 | A,C,B
2 | 9,5,1 | B,A,C
3 | 3,2,1 | C,B,A
要在新列ABC_value_DESC
中按降序获取col值的位置,然后在新列ABC_value_DESC_colnames
中返回相应的列名.
where you want to get the col values in Descending order within the new column ABC_value_DESC
and then return corresponding name of column in the new column ABC_value_DESC_colnames
.
如何在Vertica 9中将多列的值按降序串联到新列中,并按值顺序(而不是名称顺序)返回列名?
Ps.我已经尝试过Listagg -function,但是却遇到无法执行排序的错误,并且尝试了Vertica的建议时此处.
Ps. I have tried Listagg -function but bugs such that ordering not implemented and when tried Vertica's suggestion here giving false result and even bugs with alternative here.
推荐答案
您可以使用讨厌的case
表达式执行此操作.对于三列来说,还不错:
You can do this with a nasty case
expression. For three columns it is not so bad:
select t.*,
(gr || ',' ||
(case when a not in (le, gr) then a
when b not in (le, br) then b
else c
end) || ',' ||
le
),
((case gr when a then 'a' when b then 'b' else 'c' end) || ',' ||
(case when a not in (gr, le) then 'a'
when b not in (gr, le) then 'b'
else 'c'
end) || ',' ||
(case le when a then 'a' when b then 'b' else 'c' end)
)
from (select t.*, greatest(a, b, c) as gr, least(a, b, c) as le
from t
) t;
此特定版本假定没有重复项或NULL
值,尽管可以将其用于此目的.
This particular version assumes there are no duplicates or NULL
values, although this can be adopted for that purpose.
这篇关于Vertica:如何按某种顺序连接值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!