区间分布sql该怎么写才能使最终统计的各区间值和为100%
mysql中如何对区间进行补全？
| clo | count_day | speed_ranger | speed_num | not_all |
|---|---|---|---|---|
| A | 2022/8/6 | [20, 30) | 1 | 17 |
| A | 2022/8/6 | [10, 20) | 4 | 17 |
| A | 2022/8/6 | [1, 10) | 12 | 17 |
| B | 2022/8/6 | [60, 70) | 26 | 1500 |
| B | 2022/8/6 | [50, 60) | 93 | 1500 |
| B | 2022/8/6 | [40, 50) | 107 | 1500 |
| B | 2022/8/6 | [30, 40) | 293 | 1500 |
| B | 2022/8/6 | [20, 30) | 345 | 1500 |
| B | 2022/8/6 | [10, 20) | 352 | 1500 |
| B | 2022/8/6 | [1, 10) | 284 | 1500 |
| C | 2022/8/6 | [90, 100) | 69 | 2925 |
| C | 2022/8/6 | [80, 90) | 568 | 2925 |
| C | 2022/8/6 | [70, 80) | 588 | 2925 |
| C | 2022/8/6 | [60, 70) | 207 | 2925 |
| C | 2022/8/6 | [50, 60) | 239 | 2925 |
| C | 2022/8/6 | [40, 50) | 359 | 2925 |
| C | 2022/8/6 | [30, 40) | 340 | 2925 |
| C | 2022/8/6 | [20, 30) | 207 | 2925 |
| C | 2022/8/6 | [10, 20) | 139 | 2925 |
| C | 2022/8/6 | [1, 10) | 209 | 2925 |
总区间为
[90, 100),[80, 90),[70, 80),[60, 70),[50, 60),[40, 50),[30, 40),[20, 30),[10, 20),[1, 10)
目前想计算A,B,C三个在各区间的占比情况。占比为 speed_num / not_all
为了能够让各区间的占比算出来和为100%,则在计算前要对A,B的区间进补全。speed_num补0，not_all补其对应区间。
如A，在[90, 100)没有，则其speed_num=0 not_all=17
sql该怎么写，才能是的各区间占比算出来为100%
select clo,count_day,t1.speed_ranger,
sum(case when t1.speed_ranger = t2.speed_ranger then speed_num else 0 end) speed_num,
sum(case when t1.speed_ranger = t2.speed_ranger then not_all else 0 end) not_all,
if(sum(case when t1.speed_ranger = t2.speed_ranger then not_all else 0 end)=0,0,sum(case when t1.speed_ranger = t2.speed_ranger then speed_num else 0 end) /sum(case when t1.speed_ranger = t2.speed_ranger then not_all else 0 end)) zb
from (select distinct speed_ranger from speed_tab) t1
left join speed_tab t2
on 1=1
group by clo,count_day,t1.speed_ranger