google-bigquery - 将行转置为列和对应的值 BigQuery

Question

我有一张桌子

WITH table1 AS (
  SELECT '2020-12-01 00:00:06 UTC' datetime, 'FuelRate' type, 'l/h' unit, 30.1 Value, 24.1998842 lat,  40.3261354 lon UNION ALL
  SELECT '2020-12-01 00:00:06 UTC', 'EngineSpeed', 'rpm', 1850,  24.1998842, 40.3261354  UNION ALL
  SELECT '2020-12-01 00:00:06 UTC', 'OutdoorTemp', 'C', -1.0, 24.1998842, 40.3261354  UNION ALL
  SELECT '2020-12-01 00:00:06 UTC', 'FuelLevel', 'L', 78.4, 24.1998842, 40.3261354  UNION ALL
  SELECT '2020-12-01 00:00:06 UTC', 'HRSengineHours', 'h', 1560, 24.1998842, 40.3261354 
) 

select * from table1

目前看起来像这样：

但所需的输出将如下所示：

这是我的查询：

SELECT *,
 IF(type = 'FuelRate', val, NULL) AS FuelRate_l_h,
 IF(type = 'EngineSpeed', val, NULL) AS EngineSpeed_rpm,
 IF(type = 'OutdoorTemp', val, NULL) AS OutdoorTemp_C,
 IF(type = 'FuelLevel', val, NULL) AS FuelLevel_L,
 IF(type = 'HRSengineHours', val, NULL) AS HRSengineHours_h,
 
FROM table1, 
UNNEST(['Value']) col,
UNNEST([CASE col
  WHEN 'Value' THEN Value
  END]) val 
  GROUP BY 1,2,3,4,5,6,7,8

但结果如下所示：

我究竟做错了什么？感谢帮助！

标签： google-bigquery

Answer 1

以下是 BigQuery 标准 SQL

execute immediate (select 'select datetime, ' || string_agg(
  'max(if(type="' || type || '",value,null)) ' || type || '_' || replace(unit,'/','_'),', '
) || ', lat, lon from table1 group by datetime, lat, lon '
from (select distinct type, '_' || replace(unit, '/', '_') as unit from table1)
);

如果适用于您的问题的样本数据 - 输出是