我有一个CTE(CTE_person_count(,结果如下:
| name | count |
------------------
| Mike | 9 |
| Jane | 4 |
| Frank | 3 |
| Brian | 2 |
| Ann | 2 |
| Other | 15 |
从这个cte,我想计算计数与计数之和的百分比,作为新的第三列,我想要没有小数的百分比。所以我尝试了这个:
SELECT name,
count,
ROUND(count*100/(SELECT SUM(count) FROM cte_person_count),0) AS percent
FROM cte_person_count
当我这样做的时候,我得到:
| name | count | percent |
----------------------------
| Mike | 9 | 26 |
| Jane | 4 | 11 |
| Frank | 3 | 9 |
| Brian | 2 | 6 |
| Ann | 2 | 6 |
| Other | 15 | 43 |
唯一的问题是百分比列的和是101…这可能与ROUND运算符有关。我该如何避免这种情况?我的百分比栏的总和应该总是100。
如果你需要一个精确的100%和,一个技巧是将最大的百分比值作为差值100和(除了最大的百分比之外的所有百分比(。例如
with p as(
SELECT name,
count,
ROUND(count*100./(SELECT SUM(count) FROM cte_person_count),0) AS percent
FROM cte_person_count
)
select name,
case(row_number() over(order by percent desc)) when 1
then 100 - sum(percent) over(order by percent desc rows between 1 following and unbounded following)
else percent end pp
from p