ネイティブのピボット機能を使用せずに BigQuery で行を列にピボットする方法

BigQuery で行を列に転置する (ピボット実装)

この BigQuery チュートリアルでは、データ変換の一般的なニーズに対処することを目的としています。キーと値のペアを維持しながら行を列に変換します。ピボットとして知られるこの手法は、データ分析とレポート作成で広く使用されています。ただし、ピボット操作をネイティブにサポートする従来のデータベース システムとは異なり、BigQuery には現在この機能がありません。

追加のグループ化列によるピボット

この制限を克服するために、入力データに追加の列を含める回避策。これは、出力で 1 つの行にマージする必要がある行をグループ化するのに役立ちます。このアプローチの仕組みは次のとおりです。

ステップ 1: ピボット クエリを構築する

まず、実際のピボット クエリを生成するクエリを作成します。このクエリは、入力データセットに基づいてピボット操作を実行するために必要なコードを動的に構築します。

SELECT 'SELECT id, ' + 
   GROUP_CONCAT_UNQUOTED(
      'MAX(IF(key = "' + key + '", value, NULL)) as [' + key + ']'
   ) 
   + ' FROM yourTable GROUP BY id ORDER BY id'
FROM (
  SELECT key 
  FROM yourTable
  GROUP BY key
  ORDER BY key
) 

このクエリは、入力データ内のすべての一意のキー値を識別し、別のクエリとして実行されるときに文字列を生成します。 、ピボット操作を実行します。

ステップ 2: 生成されたピボットを実行するQuery

最初のクエリの結果は、実際のピボット クエリを定義する文字列です。この文字列をコピーし、通常の BigQuery クエリとして実行するだけです。結果は、列名としてキー、列値として値を持つ転置されたデータセットになります。

入力と出力の例

次の入力テーブルを考えてみましょう:

id	Key	Value
1	channel_title	Mahendra Guru
1	youtube_id	ugEGMG4-MdA
1	channel_id	UCiDKcjKocimAO1tV
1	examId	72975611-4a5e-11e5
1	postId	1189e340-b08f
2	channel_title	Ab Live
2	youtube_id	3TNbtTwLY0U
2	channel_id	UCODeKM_D6JLf8jJt
2	examId	72975611-4a5e-11e5
2	postId	0c3e6590-afeb

結果としてピボットされたデータセットは次のようになります:

id	channel_id	channel_title	examId	postId	youtube_id
1	UCiDKcjKocimAO1tV	Mahendra Guru	72975611-4a5e-11e5	1189e340-b08f	ugEGMG4-MdA
2	UCODeKM_D6JLf8jJt	Ab Live	72975611-4a5e-11e5	0c3e6590-afeb	3TNbtTwLY0U

制限と代替手段

このアプローチはピボット動作を効果的に模倣していますが、制限もあります。最も注目すべき点は、BigQuery ではテーブルあたり 10,000 列の制限が課せられているため、多数の一意のキーを持つ大規模なデータセットのピボットが妨げられる可能性があります。このようなシナリオでは、Python または R を使用した外部ピボットや、ネイティブにピボットをサポートするデータベース システムを使用したフェデレーション クエリ アプローチの使用など、代替ソリューションを検討することを検討してください。

以上がネイティブのピボット機能を使用せずに BigQuery で行を列にピボットする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。