Bagaimana untuk Mengubah Baris ke Lajur dalam BigQuery Tanpa Fungsi Pangsi Asli?

Pindahkan Baris ke Lajur dalam BigQuery (Pelaksanaan Pangsi)

Dalam tutorial BigQuery ini, kami berhasrat untuk menangani keperluan lazim untuk transformasi data: menukar baris kepada lajur sambil mengekalkan pasangan nilai kunci. Teknik ini, yang dikenali sebagai pivoting, digunakan secara meluas dalam analisis dan pelaporan data. Walau bagaimanapun, tidak seperti sistem pangkalan data konvensional yang menyokong operasi pangsi secara asli, BigQuery pada masa ini tidak mempunyai fungsi ini.

Mengasing dengan Lajur Pengumpulan Tambahan

Untuk mengatasi had ini, kami membentangkan penyelesaian penyelesaian yang melibatkan lajur tambahan dalam data input, yang berfungsi untuk mengumpulkan baris yang harus digabungkan menjadi satu baris dalam keluaran. Begini cara pendekatan ini berfungsi:

Langkah 1: Bina Pertanyaan Pangsi

Kami mulakan dengan mencipta pertanyaan yang akan menjana pertanyaan pangsi sebenar. Pertanyaan ini secara dinamik membina kod yang diperlukan untuk melaksanakan operasi berputar berdasarkan set data input.

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
) 

Pertanyaan ini mengenal pasti semua nilai kunci unik dalam data input dan menjana rentetan yang, apabila dilaksanakan sebagai pertanyaan berasingan , akan melakukan operasi berputar.

Langkah 2: Laksanakan Pangsi Dijana Pertanyaan

Hasil pertanyaan pertama ialah rentetan yang mentakrifkan pertanyaan pangsi sebenar. Kami hanya perlu menyalin rentetan ini dan menjalankannya sebagai pertanyaan BigQuery biasa. Hasilnya ialah set data yang ditukar dengan kunci sebagai nama lajur dan nilai sebagai nilai lajur.

Contoh Input dan Output

Pertimbangkan jadual input berikut:

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

Data data terpivot yang terhasil akan muncul seperti berikut:

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

Batasan dan Alternatif

Walaupun pendekatan ini meniru tingkah laku berputar secara berkesan, pendekatan ini disertakan dengan pengehadan. Paling ketara, BigQuery mengenakan had 10,000 lajur bagi setiap jadual, yang boleh menghalang pemusingan set data besar dengan banyak kunci unik. Dalam senario sedemikian, pertimbangkan untuk meneroka penyelesaian alternatif seperti pangsi luaran menggunakan Python atau R atau menggunakan pendekatan pertanyaan bersekutu dengan sistem pangkalan data yang menyokong pangsi secara asli.

Atas ialah kandungan terperinci Bagaimana untuk Mengubah Baris ke Lajur dalam BigQuery Tanpa Fungsi Pangsi Asli?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!