SQL：需要具有布林值的多個欄位的聚合透視視圖

Question

給定： 我有一個來源表，其中包含多個布林字段中鞋類的不同屬性，該值指示該屬性的鞋類是否可用。 1=可用，0=不可用。範例資料如下 -

Prod_id	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_鞋	t_涼鞋	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	...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

還有一些屬性列，例如 o_casual、o_formal、o_ethnic、m_canvas、m_leather、m_silicon、p_plain、p_textured、p_funky 等，且所有屬性列都有各自 prod_id 的二進位值。有超過 50k prod_ids。

詢問： 我需要每對屬性的可用性的旋轉分佈，並且單元格值應指示交叉點處給定對的 prod_id 的計數。 例如，具有屬性「s_8」和「c_black」的 prod_id 的計數為 4（其中 s_8=1 且 c_black=1）。它應該顯示在下面的視圖中，這對於進一步分析很有用。

屬性	s_7	s_8	s_9	s_10	c_white	c_black	c_blue	c_brown	c_other	t_鞋	t_涼鞋	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_棕色	0	...											...
c_other	1	...											...
t_鞋	0	...											...
t_涼鞋	1	...											...
t_slippers	2	...											...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

很直觀，輸出表是跨對角線鏡像的，對角線單元格將包含該特定屬性的 prod_id 的總數。

我是 SQL 新手。請幫我找到一個邏輯，透過它我可以實現所需的樞軸視圖。請注意，數據很大，因此請考慮可擴展的解決方案。如果問題不清楚並且您需要一些解釋來澄清，請告訴我。

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;