英文:
how to split string characters into rows by order of appearance for each id?
问题
假设我有一个由id列和由一组字符表示的字符串组成的表格：
with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))
我想将字符串字符拆分为每个不同id值的行，所期望的输出应该如下所示：
# id value# A T# A 7# A 0# A 8# A 9# A 1# B R# B T# B 9# B 8# B 1# B 1# B 1# B 1# B 1
到目前为止，我尝试了类似于以下的方法：
select fd.id, f.valuefrom fake_data fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', './0', 2), ',') f
但这种方法没有按顺序检索旋转列。
您知道如何以有序方式检索期望的数据吗？
英文:
Suppose, that I have a table composed by an id column and by a string represented by a set of characters:
with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))
I would like to split string characters into rows for each different id value, so expected output should look like this:
# id value# A T# A 7# A 0# A 8# A 9# A 1# B R# B T# B 9# B 8# B 1# B 1# B 1# B 1# B 1
So far I've tried something similar to:
select fd.id, f.valuefrom fake_data fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', './/0', 2), ',') f
But this approach hasn't worked as it retrieves pivoted column unordered.
Do you know how may I retrieve expected data in an ordered way?
答案1
得分: 2
如果你将拆分更改为：
lateral split_to_table(regexp_replace(trim(fd.ex_character), ''.', ''.\lateral split_to_table(regexp_replace(trim(fd.ex_character), ''.', ''.\\0'', 2), ''.') f'', 2), ''.') f
它将正常工作。要按顺序获取你想要的内容，你可以使用 f.index
因此：
with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))selectfd.id,f.valuefrom fake_data as fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), ''.', ''.\with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))selectfd.id,f.valuefrom fake_data as fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), ''.', ''.\\0'', 2), ''.') forder by fd.id'', 2), ''.') forder by fd.id
得到结果如下：
| ID | VALUE |
|---|---|
| A | T |
| A | 7 |
| A | 0 |
| A | 8 |
| A | 9 |
| A | 1 |
| B | R |
| B | T |
| B | 9 |
| B | 8 |
| B | 1 |
| B | 1 |
| B | 1 |
| B | 1 |
| B | 1 |
所以不需要选择 f.index，我只是包含它，以便你可以看到可用的内容。
英文:
if you swap the split to be:
lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', '.\lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', '.\\0', 2), '.') f', 2), '.') f
it will happen correctly. to order thing you want to use f.index
thus:
with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))selectfd.id,f.value,f.indexfrom fake_data as fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', '.\with fake_data(id, ex_character) as (select * from values('A', 'T70891'),('B', 'RT9811111'))selectfd.id,f.value,f.indexfrom fake_data as fd,lateral split_to_table(regexp_replace(trim(fd.ex_character), '.', '.\\0', 2), '.') forder by fd.id, f.index', 2), '.') forder by fd.id, f.index
gives:
| ID | VALUE | INDEX |
|---|---|---|
| A | T | 1 |
| A | 7 | 2 |
| A | 0 | 3 |
| A | 8 | 4 |
| A | 9 | 5 |
| A | 1 | 6 |
| B | R | 1 |
| B | T | 2 |
| B | 9 | 3 |
| B | 8 | 4 |
| B | 1 | 5 |
| B | 1 | 6 |
| B | 1 | 7 |
| B | 1 | 8 |
| B | 1 | 9 |
so the selecting of f.index is not needed, I just included it, so you could see what was available.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论