SQL:需要具有布尔值的多个字段的聚合透视视图
P粉969253139
P粉969253139 2023-09-07 19:47:09
[MySQL讨论组]

给定: 我有一个源表,其中包含多个布尔字段中鞋类的不同属性,该值指示该属性的鞋类是否可用。 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 新手。请帮助我找到一个逻辑,通过它我可以实现所需的枢轴视图。请注意,数据很大,因此请考虑可扩展的解决方案。如果问题不清楚并且您需要一些解释来澄清,请告诉我。

P粉969253139
P粉969253139

全部回复(1)
P粉022723606

您可以使用类似 - 的查询

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;
热门教程
更多>
最新下载
更多>
网站特效
网站源码
网站素材
前端模板
关于我们 免责申明 意见反馈 讲师合作 广告合作 最新更新
php中文网:公益在线php培训,帮助PHP学习者快速成长!
关注服务号 技术交流群
PHP中文网订阅号
每天精选资源文章推送
PHP中文网APP
随时随地碎片化学习
PHP中文网抖音号
发现有趣的

Copyright 2014-2025 https://www.php.cn/ All Rights Reserved | php.cn | 湘ICP备2023035733号