如何在大查询中聚合拆分的单词

Question

我正在尝试将一个分裂的单词重新聚合在一起。我一开始把这个词分开的原因是我想把第一个字母都大写。例如，dog corki将返回大写的Dog Corki。我有下面的代码，但是offset函数不能帮助对齐所有的单词。它返回如下

Corki Dog Golden
Corki Retriver

但是，我希望它返回以下

Corki
Dog Corki
Golden Retreiver

代码如下:

WITH array_table AS (
SELECT "corki" AS dog UNION ALL
SELECT "dog corki" UNION ALL
SELECT "golden retriever"
)
,
split_table AS (
SELECT SPLIT(array_table.dog, " ") AS split_word
FROM array_table
)
SELECT 
-- split_table.split_word,
-- unnest_split_word,
-- pos
STRING_AGG(CONCAT(UPPER(SUBSTR(unnest_split_word, 1, 1)), LOWER(SUBSTR(unnest_split_word, 2))), ' ')
FROM 
split_table,
UNNEST(split_table.split_word) AS unnest_split_word
WITH OFFSET AS pos
GROUP BY pos

提前感谢!

Answer 1

您可以使用INITCAP函数代替。

WITH array_table AS (
SELECT "corki" AS dog UNION ALL
SELECT "dog corki" UNION ALL
SELECT "golden retriever"
)
SELECT INITCAP(dog) FROM array_table;
+------------------+
|       f0_        |
+------------------+
| Corki            |
| Dog Corki        |
| Golden Retriever |
+------------------+

如果我们使用你的方法，下面将返回与上面相同的结果。

WITH array_table AS (
SELECT "corki" AS dog UNION ALL
SELECT "dog corki" UNION ALL
SELECT "golden retriever"
),
split_table AS (
SELECT FORMAT('%t', t) AS hash_id, *
FROM array_table t, UNNEST(SPLIT(dog, " ")) AS split_word WITH offset
)
SELECT STRING_AGG(INITCAP(split_word), ' ' ORDER BY offset)
FROM split_table
GROUP BY hash_id;