从字段sql / presto中提取子字符串 [英] Extract substrings from field sql/presto

问题描述

我的数据库中有包含以/分隔的值的列。我试图从列中提取某些值并用它们创建新行。

I have columns in my database that contains values separated by /. I am trying to extract certain values from columns and create new row with them.

数据示例如下所示：

user/values2/class/year/subject/18/9/2000291.csv
holiday/booking/type/1092/1921/1.csv
drink/water/juice/1/232/89.json
drink/water1/soft/90091/2/89.csv
car/type/1/001/1.json
game/mmo/1/2/3.json

我想从数据中提取最后3个数字例如，来自

I want to extract the last 3 numbers from the data e.g., from

user/values2/class/year/subject/18/9/2000291.csv

我要

x = 18
y = 9
z = 200291

并显示这些是

我一直在研究和处理presto查询，但无法弄清楚如何实现自己想要的。我在下面编写了查询，但是无法正常工作。

I have been researching and playing around with presto queries but couldn't figure out how I can achieve what I want. I wrote the query below but can't get it to work.

SELECT origin
         split_part(origin, '.' & '/', 1) as z,
         split_part(origin, '.' & '/', 2) as y,
         split_part(origin, '.' & '/', 3) as x,
FROM "data_customer";

已编辑

当前表

所需结果

推荐答案

Presto代码：

with your_data as(
select * from (values
'user/values2/class/year/subject/18/9/2000291.csv',
'holiday/booking/type/1092/1921/1.csv',
'drink/water/juice/1/232/89.json',
'drink/water1/soft/90091/2/89.csv',
'car/type/1/001/1.json',
'game/mmo/1/2/3.json'
)s (origin)
)

select s.origin, origin_splitted[3] x, origin_splitted[2]  y, regexp_extract(origin_splitted[1],'\d*') z
from
(
select s.origin, reverse(split(s.origin,'/')) origin_splitted from your_data s
)s

结果：

origin  x   y   z
user/values2/class/year/subject/18/9/2000291.csv    18  9   2000291
holiday/booking/type/1092/1921/1.csv              1092  1921    1
drink/water/juice/1/232/89.json                      1  232 89
drink/water1/soft/90091/2/89.csv                 90091  2   89
car/type/1/001/1.json                              1    1   1
game/mmo/1/2/3.json                                1    2   3

将相同的regexp_extract应用于x和y列（如果需要）或可以应用其他拆分而不是regexp_extract，希望您有主意

apply the same regexp_extract to x and y columns if necessary or another split can be applied instead of regexp_extract, hope you got the idea

这篇关于从字段sql / presto中提取子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！