MySQL - 比较百分比与连接

Question

我有两个表(主表和标签)和以下 SQL:

select t.label, m.demographic_sex, count(*) from 
Master m
LEFT OUTER JOIN tags t ON t.interaction_id = m.interaction_id
where m.demographic_sex is not null
group by t.label, m.demographic_sex
;

这个输出:

label           demographic_sex     count(*)
------------------------------
label_a         Female  117845
label_a         Male    45966
BOTH            Female  6476
BOTH            Male    4406
label_b         Female  364984
label_b         Male    261089

如何计算每个标签的每个性别的百分比？

Answer 1

你能试试这个吗？

SELECT t.label, m.demographic_sex, COUNT(*), COUNT(*) / total_t.total_cnt
FROM Master m LEFT OUTER JOIN tags t ON t.interaction_id = m.interaction_id
    INNER JOIN (SELECT label, COUNT(*) AS total_cnt
                FROM tags
                GROUP BY label
    ) total_t ON total_t.label = t.label
WHERE m.demographic_sex is not null
GROUP BY t.label, m.demographic_sex ;

total_t 具有每个 label 的总计数。