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;