SQL: Perlukan paparan pangsi agregat dengan berbilang medan dengan nilai boolean

Question

Diberikan: Saya mempunyai jadual sumber yang mengandungi atribut berbeza untuk kasut dalam berbilang medan boolean yang menunjukkan sama ada kasut untuk atribut itu tersedia. 1=Tersedia, 0=Tidak tersedia. Data sampel adalah seperti berikut -

Prod_id	s_7	s_8	s_9	s_10	c_white	c_black	c_biru	c_brown	c_other	t_kasut	t_sandal	t_selipar	...
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	...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Terdapat juga beberapa lajur atribut, seperti o_casual, o_formal, o_ethnic, m_canvas, m_leather, m_silicon, p_plain, p_textured, p_funky, dsb., dan semua lajur atribut mempunyai nilai binari prod_id masing-masing. Terdapat lebih 50k prod_id.

Tanya: Saya memerlukan taburan berputar bagi ketersediaan setiap pasangan atribut dan nilai sel harus menunjukkan kiraan prod_id untuk pasangan tertentu di persimpangan. Contohnya, prod_id dengan atribut "s_8" dan "c_black" mempunyai kiraan 4 (di mana s_8=1 dan c_black=1). Ia sepatutnya muncul dalam paparan di bawah, yang boleh berguna untuk analisis selanjutnya.

Hartanah	s_7	s_8	s_9	s_10	c_white	c_black	c_biru	c_brown	c_other	t_kasut	t_sandal	t_selipar	...
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_biru	3	...											...
c_brown	0	...											...
c_other	1	...											...
t_kasut	0	...											...
t_sandal	1	...											...
t_selipar	2	...											...
...	...	...	...	...	...	...	...	...	...	...	...	...	...

Secara intuitif, jadual keluaran dicerminkan merentasi pepenjuru, dan sel pepenjuru akan mengandungi jumlah bilangan prod_id untuk atribut tertentu itu.

Saya baru dalam SQL. Tolong bantu saya mencari logik yang melaluinya saya boleh mencapai paparan pangsi yang diingini. Ambil perhatian bahawa data adalah besar, jadi pertimbangkan penyelesaian boleh skala. Jika soalan itu tidak jelas dan anda memerlukan penjelasan untuk menjelaskan, sila beritahu saya.

Answer 1

Anda boleh menggunakan pertanyaan seperti -

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

Mudah dibina dalam bahasa pilihan anda. Berikut ialah contoh mudah menggunakan 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;