区间分布sql该怎么写才能使最终统计的各区间值和为100%

Question

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%

Answer 1

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