SQL : besoin d'une vue pivot agrégée avec plusieurs champs avec des valeurs booléennes

Question

Donné : J'ai une table source qui contient différents attributs pour les chaussures dans plusieurs champs booléens qui indiquent si les chaussures pour cet attribut sont disponibles. 1=Disponible, 0=Non disponible. Les exemples de données sont les suivants -

ID_Prod	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_shoes	t_sandales	t_slippers	...
001	0	1	0	1	1	1	0	1	0	1	0	0	...
002	1	1	0	0	1	0	1	0	0	0	0	1	...
003	0	1	1	0	0	1	1	0	1	1	0	0	...
004	0	0	1	1	0	0	1	1	0	0	1	1	...
005	1	0	1	0	1	1	1	0	0	0	0	1	...
006	0	1	1	1	0	1	0	1	1	1	0	0	...
007	0	0	1	1	1	1	0	0	0	1	0	0	...
008	0	1	1	0	0	1	0	1	1	0	0	1	...
009	1	1	1	0	0	0	1	0	1	0	1	0	...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Il existe également des colonnes d'attributs, telles que o_casual, o_formal, o_ethnic, m_canvas, m_leather, m_silicon, p_plain, p_textured, p_funky, etc., et toutes les colonnes d'attributs ont des valeurs binaires de leur prod_id respectif. Il existe plus de 50 000 prod_ids.

Demandez : J'ai besoin d'une distribution pivotée de la disponibilité de chaque paire d'attributs, et la valeur de la cellule doit indiquer le nombre de prod_id pour une paire donnée à l'intersection. Par exemple, un prod_id avec les attributs « s_8 » et « c_black » a un compte de 4 (où s_8=1 et c_black=1). Il devrait apparaître dans la vue ci-dessous, ce qui peut être utile pour une analyse plus approfondie.

Propriétés	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_shoes	t_sandales	t_slippers	...
s_7	3	2	2	0	2	1	3	0	1	0	1	2	...
s_8	2	6	4	2	2	4	3	3	4	3	1	2	...
s_9	2	4	7	3	2	5	4	3	4	3	2	3	...
s_10	0	...	...	...	...	...	...	...	...	...	...	...	...
c_white	2	...											...
c_black	1	...											...
c_blue	3	...											...
c_brown	0	...											...
c_other	1	...											...
t_shoes	0	...											...
t_sandales	1	...											...
t_slippers	2	...											...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Intuitivement, le tableau de sortie est reflété sur la diagonale et les cellules diagonales contiendront le nombre total de prod_ids pour cet attribut particulier.

Je suis nouveau sur SQL. S'il vous plaît, aidez-moi à trouver une logique grâce à laquelle je peux obtenir la vue pivot souhaitée. Notez que les données sont volumineuses, alors envisagez des solutions évolutives. Si la question n'est pas claire et que vous avez besoin d'explications pour la clarifier, faites-le moi savoir.

Answer 1

Vous pouvez utiliser une requête comme -

SELECT
    's_7' `attribute`,
    SUM(`s_7` = 1) `s_7`,
    SUM(`s_8` = 1) `s_8`,
    SUM(`s_9` = 1) `s_9`,
    SUM(`s_10` = 1) `s_10`,
    SUM(`c_white` = 1) `c_white`,
    SUM(`c_black` = 1) `c_black`,
    SUM(`c_blue` = 1) `c_blue`,
    SUM(`c_brown` = 1) `c_brown`,
    SUM(`c_other` = 1) `c_other`,
    SUM(`t_shoes` = 1) `t_shoes`,
    SUM(`t_sandals` = 1) `t_sandals`,
    SUM(`t_slippers` = 1) `t_slippers`
FROM t1 WHERE `s_7` = 1
UNION ALL
SELECT
    's_8' `attribute`,
    SUM(`s_7` = 1) `s_7`,
    SUM(`s_8` = 1) `s_8`,
    SUM(`s_9` = 1) `s_9`,
    SUM(`s_10` = 1) `s_10`,
    SUM(`c_white` = 1) `c_white`,
    SUM(`c_black` = 1) `c_black`,
    SUM(`c_blue` = 1) `c_blue`,
    SUM(`c_brown` = 1) `c_brown`,
    SUM(`c_other` = 1) `c_other`,
    SUM(`t_shoes` = 1) `t_shoes`,
    SUM(`t_sandals` = 1) `t_sandals`,
    SUM(`t_slippers` = 1) `t_slippers`
FROM t1 WHERE `s_8` = 1
UNION ALL
SELECT
    's_9' `attribute`,
...

Facilement construit dans la langue de votre choix. Voici un exemple simple utilisant PHP -

<?php

$attributes = ['s_7', 's_8', 's_9', 's_10', 'c_white', 'c_black', 'c_blue', 'c_brown', 'c_other', 't_shoes', 't_sandals', 't_slippers'];
$sql = null;
foreach ($attributes as $attribute) {
    if ($sql) {
        $sql .= ' UNION ALL ';
    }
    $sql .= "SELECT '$attribute' `attribute`";
    foreach ($attributes as $attr) {
        $sql .= ", SUM(`$attr` = 1) `$attr`";
    }
    $sql .= " FROM t1 WHERE `$attribute` = 1";
}
 echo $sql;