在Redshift SQL中从数组中提取值

I have some arrays stored in Redshift table "transactions" in the following format:

我有一些数组存储在Redshift表“transactions”中,格式如下:

id, total, breakdown
1, 100, [50,50]
2, 200, [150,50]
3, 125, [15, 110]
...
n, 10000, [100,900]

Since this format is useless to me, I need to do some processing on this to get the values out. I've tried using regex to extract it.

由于这种格式对我来说没用,我需要对此进行一些处理以获取值。我尝试使用正则表达式来提取它。

SELECT regexp_substr(breakdown, '\[([0-9]+),([0-9]+)\]')
FROM transactions

but I get an error returned that says

但我得到一个错误,说回来了

Unmatched ( or \(
Detail: 
-----------------------------------------------
error:  Unmatched ( or \(
code:      8002
context:   T_regexp_init
query:     8946413
location:  funcs_expr.cpp:130
process:   query3_40 [pid=17533]
--------------------------------------------

Ideally I would like to get x and y as their own columns so I can do the appropriate math. I know I can do this fairly easy in python or PHP or the like, but I'm interested in a pure SQL solution - partially because I'm using an online SQL editor (Mode Analytics) to plot it easily as a dashboard.

理想情况下,我想将x和y作为自己的列,以便我可以进行适当的数学运算。我知道我可以在python或PHP等中相当容易地做到这一点,但我对纯SQL解决方案感兴趣 - 部分是因为我使用在线SQL编辑器(Mode Analytics)将其轻松地绘制为仪表板。

Thanks for your help!

谢谢你的帮助!

3 个解决方案

#1

If breakdown really is an array you can do this:

如果故障确实是一个阵列,你可以这样做:

select id, total, breakdown[1] as x, breakdown[2] as y
from transactions;

If breakdown is not an array but e.g. a varchar column, you can cast it into an array if you replace the square brackets with curly braces:

如果击穿不是阵列,而是例如如果用大括号替换方括号,可以将varchar列转换为数组:

select id, total, 
       (translate(breakdown, '[]', '{}')::integer[])[1] as x,
       (translate(breakdown, '[]', '{}')::integer[])[2] as y
from transactions;

#2

You can try this :

你可以试试这个:

SELECT REPLACE(SPLIT_PART(breakdown,',',1),'[','') as x,REPLACE(SPLIT_PART(breakdown,',',2),']','') as y FROM transactions;

I tried this with redshift db and this worked for me.

我尝试使用redshift db,这对我有用。

Detailed Explanation:

SPLIT_PART(breakdown,',',1) will give you [50.

SPLIT_PART(细分,',',1)会给你[50。

SPLIT_PART(breakdown,',',2) will give you 50].

SPLIT_PART(细分,',',2)会给你50]。

REPLACE(SPLIT_PART(breakdown,',',1),'[','') will replace the [ and will give just 50.

REPLACE(SPLIT_PART(细分,',',1),'[','')将取代[并且只会给50。

REPLACE(SPLIT_PART(breakdown,',',2),']','') will replace the ] and will give just 50.

REPLACE(SPLIT_PART(细分,',',2),']','')将替换]并且仅提供50。

#3

Know its an old post.But if someone needs a much easier way

知道它的旧帖子。但是如果有人需要更简单的方法

select json_extract_array_element_text('[100,101,102]', 2);

output : 102

输出:102

#1