在BigQuery中枢轴重复字段 [英] Pivot Repeated fields in BigQuery

问题描述

  userid：string 
 timestamp：integer 
 params：带有2个字段的嵌套/重复字段
  -  name：string（可能的值：a，b，c）
  -  value：string 
  
 
 
 
我希望我的查询返回以下内容： 
 
 
  userid，timestamp，a，b，c 
 123，1447799796，foo，bar，xyz 
 233,1447799900，bob，xxx，yyy 
：
：
  
最简单的方法是什么？ 
 
解决方案
如果可能的值事先已知，并且没有那么多人手动编写SQL  - 您可以在下面使用： 
 
 
  SELECT 
 userid，
 ts，
 MAX（IF（params.name =a，params.value，NULL））WITHIN RECORD a，$ b $在记录b中，
 MAX（IF（params.name =c，params.value，NULL））WITHIN RECORD c 
 FROM yourTable 
  
如果可能的值事先是未知的并且/或者从运行到运行是动态的，那么您可以使用下面的助手SQL来生成以上类型的SQL。  
 
 
  SELECT'select userid，ts，'+ 
 GROUP_CONCAT_UNQUOTED（
'max（if（params。 
 WITHIN RECORD as ['+ STRING（params.name）+']'
）
 +'from yourTable'
 FROM（SELECT params.name FROM yourTable GROUP BY params.name）
  
 
My Schema looks something like this:
userid:string
timestamp:integer
params:nested/repeated field with 2 fields
  - name:string (possible values: "a", "b","c")
  - value:string
I want my query to return the following:
userid, timestamp, a, b, c
123, 1447799796, foo, bar, xyz
233, 1447799900, bob, xxx, yyy
:
:
What's the easiest way to do this?
解决方案
when possible values are known in advance and there are not that many of them to write manually SQL - you can use below:
SELECT 
  userid,
  ts,
  MAX(IF(params.name = "a", params.value, NULL)) WITHIN RECORD a,
  MAX(IF(params.name = "b", params.value, NULL)) WITHIN RECORD b,
  MAX(IF(params.name = "c", params.value, NULL)) WITHIN RECORD c
FROM yourTable 
If possible values are "unknown" in advance and/or dynamic from run to run, you can use below helper SQL to generate  above type of SQL. 
SELECT 'select userid, ts, ' + 
   GROUP_CONCAT_UNQUOTED(
      'max(if(params.name = "' + STRING(params.name) + '", params.value, null)) 
       WITHIN RECORD as [' + STRING(params.name) + ']'
   ) 
   + ' from yourTable '
FROM (SELECT params.name FROM yourTable GROUP BY params.name)


                        
这篇关于在BigQuery中枢轴重复字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！