BigQuery中的枢纽 [英] Pivot in BigQuery

问题描述

当我在BigQuery中使用数据透视功能时，下面的代码段

When i use pivot function in BigQuery, something as snippet below

CALL fhoffa.x.pivot(
  'bigquery-public-data.ghcn_d.ghcnd_2019' # source table
  , 'fh-bigquery.temp.test_pivotted' # destination table
  , ['id', 'date'] # row_ids
  , 'element' # pivot_col_name
  , 'value' # pivot_col_value
  , 30 # max_columns
  , 'AVG' # aggregation
  , 'LIMIT 10' # optional_limit
);

数据透视表的列名带有前缀e _

The pivoted column name has a prefix e_

<身体>

id	日期	e_PRCP	e_TMIN	e_TMAX	e_SNOW
1	27-01-2021	1	2	5	8
2	28-01-2021	3	5	9	3
3	29-01-2021	5	7	4	1

有什么办法摆脱这个 e _ 前缀?

Is there any way to get rid of this e_ prefix?

推荐答案

这里是该过程的完整定义，您可以在自己的数据集中创建一个过程，并根据需要进行修改.您可以看到 e _ 已添加到第一个 EXECUTE IMMEDIATE 中.

Here's full definition of the procedure, you can create one in your own dataset and modify as you see fit. You can see the e_ was added inside the first EXECUTE IMMEDIATE.

CREATE OR REPLACE PROCEDURE `fhoffa.x.pivot`(table_name STRING, destination_table STRING, row_ids ARRAY<STRING>, pivot_col_name STRING, pivot_col_value STRING, max_columns INT64, aggregation STRING, optional_limit STRING)
BEGIN
  DECLARE pivotter STRING;
EXECUTE IMMEDIATE (
    "SELECT STRING_AGG(' "||aggregation
    ||"""(IF('||@pivot_col_name||'="'||x.value||'", '||@pivot_col_value||', null)) e_'||fhoffa.x.normalize_col_name(x.value))
   FROM UNNEST((
       SELECT APPROX_TOP_COUNT("""||pivot_col_name||", @max_columns) FROM `"||table_name||"`)) x"
  ) INTO pivotter 
  USING pivot_col_name AS pivot_col_name, pivot_col_value AS pivot_col_value, max_columns AS max_columns;
EXECUTE IMMEDIATE (
   'CREATE OR REPLACE TABLE `'||destination_table
   ||'` AS SELECT '
   ||(SELECT STRING_AGG(x) FROM UNNEST(row_ids) x)
   ||', '||pivotter
   ||' FROM `'||table_name||'` GROUP BY '
   || (SELECT STRING_AGG(''||(i+1)) FROM UNNEST(row_ids) WITH OFFSET i)||' ORDER BY '
   || (SELECT STRING_AGG(''||(i+1)) FROM UNNEST(row_ids) WITH OFFSET i)
   ||' '||optional_limit
  );
END;

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