BigQuery-将多行连接为单行 [英] BigQuery - Concatenate multiple rows into a single row
本文介绍了BigQuery-将多行连接为单行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含2列的BigQuery表:
I have a BigQuery table with 2 columns:
id|name
1|John
1|Tom
1|Bob
2|Jack
2|Tim
预期的输出:将按ID分组的串联名称
Expected output: Concatenate names grouped by id
id|Text
1|John,Tom,Bob
2|Jack,Tim
推荐答案
对于BigQuery标准SQL:
For BigQuery Standard SQL:
#standardSQL
--WITH yourTable AS (
-- SELECT 1 AS id, 'John' AS name UNION ALL
-- SELECT 1, 'Tom' UNION ALL
-- SELECT 1, 'Bob' UNION ALL
-- SELECT 2, 'Jack' UNION ALL
-- SELECT 2, 'Tim'
--)
SELECT
id,
STRING_AGG(name ORDER BY name) AS Text
FROM yourTable
GROUP BY id
STRING_CONCAT
中的可选ORDER BY name
允许您获取如下所示的名称的排序列表
Optional ORDER BY name
within STRING_CONCAT
allows you to get out sorted list of names as below
id Text
1 Bob,John,Tom
2 Jack,Tim
对于旧版SQL
#legacySQL
SELECT
id,
GROUP_CONCAT(name) AS Text
FROM yourTable
GROUP BY id
如果您需要在此处输出排序列表,则可以在下面使用(以前-BigQuery Legacy SQL不能保证获得排序列表-但在我遇到的大多数实际情况下-可以使用)
If you would need to output sorted list here, you can use below (formally - it is not guaranteed by BigQuery Legacy SQL to get sorted list - but for most practical cases I had - it worked)
#legacySQL
SELECT
id,
GROUP_CONCAT(name) AS Text
FROM (
SELECT id, name
FROM yourTable
ORDER BY name
)
GROUP BY id
这篇关于BigQuery-将多行连接为单行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文