在同一表格中串联BigQuery中具有相同ID的行 [英] Concatenate rows with same id in BigQuery from same table
本文介绍了在同一表格中串联BigQuery中具有相同ID的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在Google bigquery中运行此查询
I am running this query in google bigquery
SELECT rep.key.id as id, te.cmnt as comment
FROM `table` rep, UNNEST(tre) as te
WHERE u_id LIKE 'test'
ORDER BY cts DESC
我正在得到这个
Id comment
---------------------------------
5165356444286976 HEloo
5165356444286976 TEST
5165356444286976
我想合并具有相同ID的所有注释,例如,看起来像这样:
I want to merge all comments with same id, for example to look like this:
Id comment
---------------------------------
5165356444286976 HEloo, TEST
推荐答案
使用 STRING_AGG()
:
WITH table AS (
SELECT 'test' u_id, STRUCT(1 AS id) AS key, [STRUCT('hey' AS cmnt)] AS tre, 3 cts
UNION ALL
SELECT 'test', STRUCT(1), [STRUCT('you')], 2
UNION ALL
SELECT 'test', STRUCT(1), [STRUCT(CAST(null AS STRING))], 1
)
SELECT rep.key.id as id, STRING_AGG(te.cmnt, ', ' ORDER BY cts DESC) AS comments
FROM `table` rep, UNNEST(tre) as te
WHERE u_id LIKE 'test'
GROUP BY id
这篇关于在同一表格中串联BigQuery中具有相同ID的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文