SQL实现鞋类多布尔属性对的聚合透视视图（可扩展方案）

核心思路 #

要实现可扩展的属性交叉计数，关键是避免硬编码属性字段——通过将宽表转换为通用长表结构，再通过自连接完成两两属性的关联统计，新增属性时无需修改核心SQL逻辑。

步骤1：将宽属性表转换为长表 #

先把源表（假设表名为shoe_attributes）的每个布尔属性字段拆成prod_id、attribute_name、is_available的行结构，适配任意数量的属性：

WITH attribute_long AS (
    SELECT 
        prod_id,
        's_7' AS attribute_name,
        s_7 AS is_available
    FROM shoe_attributes
    UNION ALL
    SELECT 
        prod_id,
        'c_black' AS attribute_name,
        c_black AS is_available
    FROM shoe_attributes
    UNION ALL
    -- 新增属性时仅需追加UNION ALL语句
    SELECT 
        prod_id,
        't_shoes' AS attribute_name,
        t_shoes AS is_available
    FROM shoe_attributes
)

若使用支持UNPIVOT语法的数据库（如Oracle、SQL Server），可更简洁地避免硬编码：

WITH attribute_long AS (
    SELECT prod_id, attribute_name, is_available
    FROM shoe_attributes
    UNPIVOT (
        is_available FOR attribute_name IN (
            s_7, c_black, t_shoes -- 新增属性时仅需在此添加字段名
        )
    ) AS unpvt
)

步骤2：统计两两属性的同时可用计数 #

通过自连接长表，筛选出两个属性均为可用状态的prod_id，按属性对分组计数：

SELECT
    a.attribute_name AS attr1,
    b.attribute_name AS attr2,
    COUNT(DISTINCT a.prod_id) AS common_prod_count
FROM attribute_long a
JOIN attribute_long b 
    ON a.prod_id = b.prod_id
    AND a.is_available = 1
    AND b.is_available = 1
GROUP BY a.attribute_name, b.attribute_name

查询结果说明：

• 当attr1与attr2相同时，得到的是该属性的总可用Prod_id数（对应透视表对角线数据）
• 当attr1与attr2不同时，得到的是两个属性同时可用的Prod_id数

步骤3：转换为透视表格式（可选） #

如果需要将结果转换为行列对应的透视表（类似Excel透视表），以MySQL为例，可用CASE语句实现：

WITH attribute_long AS (
    -- 复用步骤1的长表转换逻辑
    SELECT prod_id, attribute_name, is_available
    FROM shoe_attributes
    UNPIVOT (
        is_available FOR attribute_name IN (s_7, c_black, t_shoes)
    ) AS unpvt
),
cross_counts AS (
    SELECT
        a.attribute_name AS attr1,
        b.attribute_name AS attr2,
        COUNT(DISTINCT a.prod_id) AS common_prod_count
    FROM attribute_long a
    JOIN attribute_long b 
        ON a.prod_id = b.prod_id
        AND a.is_available = 1
        AND b.is_available = 1
    GROUP BY a.attribute_name, b.attribute_name
)
SELECT
    attr1,
    MAX(CASE WHEN attr2 = 's_7' THEN common_prod_count END) AS s_7,
    MAX(CASE WHEN attr2 = 'c_black' THEN common_prod_count END) AS c_black,
    MAX(CASE WHEN attr2 = 't_shoes' THEN common_prod_count END) AS t_shoes
    -- 新增属性时仅需追加对应的CASE语句
FROM cross_counts
GROUP BY attr1

性能优化建议 #

• 给源表的prod_id字段创建主键或唯一索引，提升自连接的匹配效率
• 为布尔属性字段创建复合索引（如(s_7, prod_id)），加快is_available=1的筛选速度
• 若数据量持续增长，可预先计算长表并存储为中间表，定期刷新，避免每次查询都执行UNPIVOT操作

内容的提问来源于stack exchange，提问作者PavanV