MySQL:计数百分比，如果百分比小于 1%，则对它们进行分组并更改标签

Question

这是我用来获取每个国家/地区事件参与者的百分比的查询：

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

它返回如下表：

+-------+------------+-------------+
| count | percentage | Nationality |
+-------+------------+-------------+
|   100 |        0.8 | Albania     |
|   114 |        0.9 | Algeria     |
|   273 |        2.2 | Belgium     |
|   ... |        ... | ...         |
+-------+------------+-------------+

我想修改查询，以便不显示任何百分比小于 1% 的行，而是汇总它们的数量并使用新百分比添加为"其他"。因此，使用新查询，结果将是：

+-------+------------+-------------+
| count | percentage | Nationality |
+-------+------------+-------------+
|   214 |        1.7 | Other       |
|   273 |        2.2 | Belgium     |
|   ... |        ... | ...         |
+-------+------------+-------------+

Answer 1

使用子查询：

SELECT (CASE WHEN count < 0.01 * total THEN 'Other'
ELSE Nationality
END) as new_Nationality,
SUM(count),
ROUND(100 * SUM(count) / total , 1) as percentage
FROM (SELECT p.Nationality,
count(*) as count,
SUM(COUNT(*)) OVER () as total
FROM participants p JOIN
participants_events pe
on p.id = pe.participant_id
GROUP BY p.Nationality
)  p
GROUP BY new_Nationality, total
ORDER BY 2 DESC;

Answer 2

尝试使用Having子句：

SELECT count(p.id) as count
, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
, p.Nationality
FROM participants as p
JOIN participants_events as pe  
on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) > 1

编辑

对于第二部分，尝试将第一部分的结果与小于 1 的百分比总和的结果相结合。

SELECT count(p.id) as count
, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
, p.Nationality
FROM participants as p
JOIN participants_events as pe  
on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) > 1
UNION
SELECT SUM(A.count) As count,
SUM (A.percentage) As percentage,
'OTHER'
FROM (SELECT count(p.id) as count
, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage
, p.Nationality
FROM participants as p
JOIN participants_events as pe  
on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) <= 1
) A

Answer 3

请检查您的初始查询以使用相同的集合：

因此，请从以下位置修改您的初始查询：

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

自

SELECT count(p.id) as count, round(100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  on p.id = pe.participant_id),1) as percentage, p.Nationality
FROM participants as p
JOIN participants_events as pe  on p.id = pe.participant_id
GROUP BY p.Nationality

所以i.signori的答案将是：

SELECT count(p.id) as count
, 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
on p.id = pe.participant_id) as percentage
, p.Nationality
FROM participants as p
JOIN participants_events as pe  
on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
on p.id = pe.participant_id) > 1
UNION
SELECT SUM(A.count) As count,
SUM(A.percentage) As percentage,
'Other'
FROM (SELECT count(p.id) as count
, 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
on p.id = pe.participant_id) as percentage
, p.Nationality
FROM participants as p
JOIN participants_events as pe  
on p.id = pe.participant_id
GROUP BY p.Nationality
HAVING 100*count(p.id)/(SELECT count(p.id) FROM participants as p JOIN participants_events as pe  
on p.id = pe.participant_id) <= 1
) A