Comment faire pivoter des lignes en colonnes dans BigQuery sans fonctionnalité de pivotement native ?

Transposer des lignes en colonnes dans BigQuery (implémentation Pivot)

Dans ce didacticiel BigQuery, nous visons à répondre à un besoin répandu de transformation des données : convertir des lignes en colonnes tout en préservant les paires clé-valeur. Cette technique, connue sous le nom de pivotement, est largement utilisée dans l’analyse des données et le reporting. Cependant, contrairement aux systèmes de bases de données conventionnels qui prennent en charge les opérations de pivotement de manière native, BigQuery ne dispose actuellement pas de cette fonctionnalité.

Pivotement avec une colonne de regroupement supplémentaire

Pour surmonter cette limitation, nous présentons un solution de contournement qui implique une colonne supplémentaire dans les données d'entrée, qui sert à regrouper les lignes qui doivent être fusionnées en une seule ligne dans la sortie. Voici comment fonctionne l'approche :

Étape 1 : Construire la requête pivot

Nous commençons par créer une requête qui générera la requête pivot réelle. Cette requête construit dynamiquement le code nécessaire pour effectuer l'opération de pivotement en fonction de l'ensemble de données d'entrée.

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
) 

Cette requête identifie toutes les valeurs de clé uniques dans les données d'entrée et génère une chaîne qui, lorsqu'elle est exécutée en tant que requête distincte , effectuera l'opération de pivotement.

Étape 2 : Exécuter le pivot généré Requête

Le résultat de la première requête est une chaîne qui définit la requête pivot réelle. Nous devons simplement copier cette chaîne et l'exécuter comme une requête BigQuery standard. Le résultat sera un ensemble de données transposé avec les clés comme noms de colonnes et les valeurs comme valeurs de colonnes.

Exemple d'entrée et de sortie

Considérez la table d'entrée suivante :

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

L'ensemble de données pivotées résultant apparaîtrait comme suit :

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

Limitations et alternatives

Bien que cette approche imite efficacement un comportement de pivotement, elle comporte des limites. Plus particulièrement, BigQuery impose une limite de 10 000 colonnes par table, ce qui peut gêner le pivotement de grands ensembles de données comportant de nombreuses clés uniques. Dans de tels scénarios, envisagez d'explorer des solutions alternatives telles que le pivotement externe à l'aide de Python ou de R ou l'utilisation d'une approche de requête fédérée avec un système de base de données prenant en charge le pivotement de manière native.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!