SQL: Benötigt wird eine aggregierte Pivot-Ansicht mit mehreren Feldern mit booleschen Werten

Question

Gegeben: Ich habe eine Quelltabelle, die verschiedene Attribute für Schuhe in mehreren booleschen Feldern enthält, die angeben, ob die Schuhe für dieses Attribut verfügbar sind. 1=Verfügbar, 0=Nicht verfügbar. Beispieldaten sind wie folgt -

Prod_id	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_shoes	T_Sandalen	t_pantoffeln	...
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	...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Es gibt auch einige Attributspalten wie o_casual, o_formal, o_ethnic, m_canvas, m_leather, m_silicon, p_plain, p_textured, p_funky usw., und alle Attributspalten haben Binärwerte ihrer jeweiligen prod_id. Es gibt über 50.000 prod_ids.

Fragen Sie: Ich benötige eine rotierte Verteilung der Verfügbarkeit jedes Attributpaars und der Zellenwert sollte die Anzahl der prod_id für ein bestimmtes Paar am Schnittpunkt angeben. Beispielsweise hat eine prod_id mit den Attributen „s_8“ und „c_black“ eine Anzahl von 4 (wobei s_8=1 und c_black=1). Es sollte in der Ansicht unten erscheinen, was für die weitere Analyse nützlich sein kann.

Eigenschaften	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_shoes	T_Sandalen	t_pantoffeln	...
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_Sandalen	1	...											...
t_pantoffeln	2	...											...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Intuitiv wird die Ausgabetabelle über die Diagonale gespiegelt und die diagonalen Zellen enthalten die Gesamtzahl der prod_ids für dieses bestimmte Attribut.

Ich bin SQL-Neuling. Bitte helfen Sie mir, eine Logik zu finden, mit der ich die gewünschte Pivot-Ansicht erreichen kann. Beachten Sie, dass die Datenmenge groß ist. Erwägen Sie daher skalierbare Lösungen. Wenn die Frage unklar ist und Sie eine Erklärung zur Klärung benötigen, lassen Sie es mich bitte wissen.

Answer 1

您可以使用类似 - 的查询

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`,
...

可以用您选择的语言轻松构建。这是一个使用 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;