sql - 在 Redshift SQL 中从数组中提取值

Question

我有一些数组以下列格式存储在 Redshift 表“事务”中:

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

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

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

但是我得到一个错误返回说

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

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

感谢您的帮助!

Answer 1

如果 breakdown 确实是一个数组，您可以这样做:

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

如果分解不是数组而是例如varchar 列，如果将方括号替换为大括号，则可以将其转换为数组:

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