Comment faire pivoter dynamiquement des données dans SQL à l'aide de crosstab() ?

Tableau croisé dynamique SQL : une alternative dynamique utilisant la fonction crosstab()

Description du problème :

Supposons qu'il existe un tableau contenant des données au format suivant :

id	feh	bar
1	10	A
2	20	A
3	3	B
4	4	B
5	5	C
6	6	D
7	7	D
8	8	D

Le but est de convertir ce tableau dans un format plus structuré, où chaque ligne représente une catégorie (bar) et les valeurs correspondantes (feh) sont disposées en colonnes avec des noms de colonnes tels que val1, val2, etc. Le résultat souhaité ressemble à ceci :

bar	val1	val2	val3
A	10	20	NULL
B	3	4	NULL
C	5	NULL	NULL
D	6	7	8

La solution traditionnelle consiste à utiliser des instructions CASE et des clauses GROUP BY pour faire pivoter les données. Cependant, pour les tableaux comportant de nombreuses catégories, cette approche peut devenir très verbeuse et lourde.

Alternative :

Le module tablefunc offre une alternative plus efficace et dynamique aux solutions traditionnelles. En utilisant la fonction crosstab(), nous pouvons obtenir les mêmes résultats avec une requête plus simple et plus maintenable.

Solution :

Assurez-vous que le module tablefunc est installé, exécutez la commande suivante une fois par base de données :

<code class="language-sql">CREATE EXTENSION tablefunc;</code>

Voici une requête croisée de base qui résout le problème :

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  --更多列？</code>

Dans cette requête :

• La fonction crosstab() accepte deux paramètres : une sous-requête qui sélectionne les données et attribue des valeurs de catégorie factices à chaque ligne et une liste de noms de colonnes (dans ce cas, val1, val2 et val3).
La sous-requête
• sélectionne les colonnes bar et feh de la table d'origine, utilise la fonction de fenêtre row_number() pour attribuer une valeur de catégorie factice de 1 à chaque ligne et trie les données par bar et feh pour garantir le bon ordre dans le sortir.
• Le résultat de la fonction crosstab() est affecté à un CTE (expression de table commune) nommé ct pour faciliter le référencement dans les instructions SELECT suivantes.

Tableau croisé dynamique :

Bien que cette approche de base fonctionne bien, elle peut ne pas fonctionner correctement lorsque le nombre de catégories (colonnes) est inconnu à l'avance. Pour résoudre ce problème, nous pouvons définir une fonction de tableau croisé dynamique qui génère des noms de colonnes basés sur différentes valeurs dans une colonne de catégorie spécifiée.

La requête suivante montre comment créer et utiliser des fonctions de tableau croisé dynamique :

<code class="language-sql">-- 创建动态crosstab函数
CREATE FUNCTION dynamic_crosstab(anyarray) RETURNS table AS $$
  DECLARE
    column_names text[];
    column_definitions text[];
    cte_name text;
  BEGIN
    -- 获取类别列的不同值
    column_names := ARRAY(SELECT DISTINCT unnest());
    -- 生成列定义
    column_definitions := ARRAY(SELECT STRING_AGG('"' || name || '" INT', ', ') FROM (SELECT unnest(column_names) AS name) AS subquery);
    cte_name := 'cte_' || md5(random()::text); -- 生成唯一的CTE名称
    EXECUTE FORMAT('CREATE TEMP TABLE %s (%s)', cte_name, column_definitions);
    -- 将数据插入CTE
    INSERT INTO %s SELECT * FROM crosstab();
    -- 返回CTE
    RETURN QUERY EXECUTE FORMAT('SELECT * FROM %s', cte_name);
  END;
$$ LANGUAGE plpgsql;

-- 使用动态crosstab函数
SELECT * FROM dynamic_crosstab(ARRAY['bar']);</code>

Cette réponse révisée fournit une explication plus détaillée et précise de la solution de tableau croisé dynamique SQL, y compris la création et l'utilisation d'une fonction de tableau croisé dynamique. Le code est formaté pour une meilleure lisibilité.

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!