英文:
Split string in a dataset to different columns
问题
以下是您要的翻译：
"How can I split the string in my dataset to different columns. Below is how my dataset looks like."
"我如何将数据集中的字符串拆分到不同的列中。下面是我的数据集的样子。"
"Below is the expected output. Please note that the string has variable length with no delimiter and has to be split only till the first two characters in the string."
"下面是期望的输出。请注意，字符串的长度不定，没有分隔符，只需要将字符串拆分到前两个字符。"
"I'm new to BigQuery. Any help would be appreciated."
"我对BigQuery还不熟悉。任何帮助将不胜感激。"
英文:
How can I split the string in my dataset to different columns. Below is how my dataset looks like.
| ColumnA |
|---|
| ABCDEF |
| GHIPQRS |
Below is the expected output. Please note that the string has variable length with no delimiter and has to be split only till the first two characters in the string.
| Column B | Column C | Column D | Column E | Column F |
|---|---|---|---|---|
| ABCDE | ABCD | ABC | AB | |
| GHIPQR | GHIPQ | GHIP | GHI | GH |
I'm new to BigQuery. Any help would be appreciated.
答案1
得分: 0
你可以考虑使用以下递归方法：
WITH RECURSIVE sample_table AS (
SELECT 'ABCDEF' ColumnA UNION ALL SELECT 'GHIPQRS'
),
iterations AS (
SELECT ColumnA, 1 idx, ColumnA AS SubStr FROM sample_table
UNION ALL
SELECT ColumnA, idx + 1, LEFT(SubStr, LENGTH(SubStr) - 1)
FROM iterations WHERE LENGTH(SubStr) > 2
)
SELECT * EXCEPT(ColumnA) FROM iterations
PIVOT (ANY_VALUE(SubStr) Column FOR idx IN (1, 2, 3, 4, 5, 6));
查询结果
- 另请参阅：PIVOT 查询
对于动态查询，
CREATE TEMP TABLE sample_table AS
SELECT 'ABCDEF' ColumnA UNION ALL
SELECT 'GHIPQRS' UNION ALL
SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
EXECUTE IMMEDIATE FORMAT("""
WITH RECURSIVE iterations AS (
SELECT ColumnA, 1 idx, ColumnA AS SubStr FROM sample_table
UNION ALL
SELECT ColumnA, idx + 1, LEFT(SubStr, LENGTH(SubStr) - 1)
FROM iterations WHERE LENGTH(SubStr) > 2
)
SELECT * EXCEPT(ColumnA) FROM iterations
PIVOT (ANY_VALUE(SubStr) Column FOR idx IN (%s));
""", (SELECT STRING_AGG('' || idx)
FROM UNNEST(GENERATE_ARRAY(1, (SELECT MAX(LENGTH(ColumnA)) FROM sample_table))) idx
)
);
单击最后的“查看结果”，然后你将会看到
英文:
You can consider a recursive approach like below.
WITH RECURSIVE sample_table AS (
SELECT 'ABCDEF' ColumnA UNION ALL SELECT 'GHIPQRS'
),
iterations AS (
SELECT ColumnA, 1 idx, ColumnA AS SubStr FROM sample_table
UNION ALL
SELECT ColumnA, idx + 1, LEFT(SubStr, LENGTH(SubStr) - 1)
FROM iterations WHERE LENGTH(SubStr) > 2
)
SELECT * EXCEPT(ColumnA) FROM iterations
PIVOT (ANY_VALUE(SubStr) Column FOR idx IN (1, 2, 3, 4, 5, 6));
Query results
- See also : PIVOT query
For a dynamic query,
CREATE TEMP TABLE sample_table AS
SELECT 'ABCDEF' ColumnA UNION ALL
SELECT 'GHIPQRS' UNION ALL
SELECT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
EXECUTE IMMEDIATE FORMAT("""
WITH RECURSIVE iterations AS (
SELECT ColumnA, 1 idx, ColumnA AS SubStr FROM sample_table
UNION ALL
SELECT ColumnA, idx + 1, LEFT(SubStr, LENGTH(SubStr) - 1)
FROM iterations WHERE LENGTH(SubStr) > 2
)
SELECT * EXCEPT(ColumnA) FROM iterations
PIVOT (ANY_VALUE(SubStr) Column FOR idx IN (%s));
""", (SELECT STRING_AGG('' || idx)
FROM UNNEST(GENERATE_ARRAY(1, (SELECT MAX(LENGTH(ColumnA)) FROM sample_table))) idx
)
);
Click last VIEW RESULTS, then you will see
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论