英文:
Count values by range include zero count values
问题
我已经创建了一个查询，根据它们的范围计算值的数量。我期望查询包括范围，即使计数为0。
这是查询的结果。
这是我期望的结果。
所以以此类推。
英文:
I've made a query that counts the values based on their range. I am expecting the query to include the range even if the count is 0.
select
case
when rm.VALUE < 0 then '-LOW'
when rm.VALUE > 0 and rm.VALUE <= 5 then '00-05'
when rm.VALUE > 5 and rm.VALUE <= 10 then '05-10'
when rm.VALUE > 10 and rm.VALUE <= 10.5 then '10-10.5'
when rm.VALUE > 10.5 and rm.VALUE <= 11 then '10.5-11'
when rm.VALUE > 11 and rm.VALUE <= 11.5 then '11-11.5'
when rm.VALUE > 11.5 and rm.VALUE <= 12 then '11.5-12'
when rm.VALUE > 12 and rm.VALUE <= 12.5 then '12-12.5'
when rm.VALUE > 12.5 and rm.VALUE <= 13 then '12.5-13'
when rm.VALUE > 13 and rm.VALUE <= 13.5 then '13-13.5'
when rm.VALUE > 13.5 and rm.VALUE <= 14 then '13.5-14'
when rm.VALUE > 14 and rm.VALUE <= 14.5 then '14-14.5'
when rm.VALUE > 14.5 and rm.VALUE <= 15 then '14.5-15'
when rm.VALUE > 15 and rm.VALUE <= 15.5 then '15-15.5'
when rm.VALUE > 15.5 and rm.VALUE <= 16 then '15.5-16'
when rm.VALUE > 16 and rm.VALUE <= 16.5 then '16-16.5'
when rm.VALUE > 16.5 and rm.VALUE <= 17 then '16.5-17'
when rm.VALUE > 17 and rm.VALUE <= 17.5 then '17-17.5'
when rm.VALUE > 17.5 and rm.VALUE <= 18 then '17.5-18'
when rm.VALUE > 18 and rm.VALUE <= 18.5 then '18-18.5'
when rm.VALUE > 18.5 and rm.VALUE <= 19 then '18.5-19'
when rm.VALUE > 19 and rm.VALUE <= 19.5 then '19-19.5'
when rm.VALUE > 19.5 and rm.VALUE <= 20 then '19.5-20'
when rm.VALUE > 20 and rm.VALUE <= 20.5 then '20-20.5'
when rm.VALUE > 20.5 and rm.VALUE <= 21 then '20.5.21'
when rm.VALUE > 21 and rm.VALUE <= 21.5 then '21-21.5'
when rm.VALUE > 21.5 and rm.VALUE <= 22 then '21.5-22'
when rm.VALUE > 22 and rm.VALUE <= 22.5 then '22-22.5'
when rm.VALUE > 22.5 and rm.VALUE <= 23 then '22.5-23'
when rm.VALUE > 23 and rm.VALUE <= 23.5 then '23-23.5'
when rm.VALUE > 23.5 and rm.VALUE <= 24 then '23.5-24'
when rm.VALUE > 24 and rm.VALUE <= 24.5 then '24-24.5'
when rm.VALUE > 24.5 and rm.VALUE <= 25 then '24.5-25'
when rm.VALUE > 25 and rm.VALUE <= 25.5 then '25-25.5'
when rm.VALUE > 25.5 and rm.VALUE <= 26 then '25.5.26'
when rm.VALUE > 26 and rm.VALUE <= 26.5 then '26-26.5'
when rm.VALUE > 26.5 and rm.VALUE <= 27 then '26.5-27'
when rm.VALUE > 27 and rm.VALUE <= 27.5 then '27-27.5'
when rm.VALUE > 27.5 and rm.VALUE <= 28 then '27.5-28'
when rm.VALUE > 28 and rm.VALUE <= 28.5 then '28-28.5'
when rm.VALUE > 28.5 and rm.VALUE <= 29 then '28.5-29'
when rm.VALUE > 29 and rm.VALUE <= 29.5 then '29-29.5'
when rm.VALUE > 29.5 and rm.VALUE <= 30 then '29.5-30'
when rm.VALUE > 30 and rm.VALUE <= 30.5 then '30-30.5'
when rm.VALUE > 30.5 and rm.VALUE <= 31 then '30.5-31'
when rm.VALUE > 31 and rm.VALUE <= 31.5 then '31-31.5'
when rm.VALUE > 31.5 and rm.VALUE <= 32 then '31.5-32'
when rm.VALUE > 32 and rm.VALUE <= 32.5 then '32-32.5'
when rm.VALUE > 32.5 and rm.VALUE <= 33 then '32.5-33'
when rm.VALUE > 33 and rm.VALUE <= 33.5 then '33-33.5'
when rm.VALUE > 33.5 and rm.VALUE <= 34 then '33.5-34'
when rm.VALUE > 34 and rm.VALUE <= 34.5 then '34-34.5'
when rm.VALUE > 34.5 and rm.VALUE <= 35 then '34.5-35'
when rm.VALUE > 35 and rm.VALUE <= 35.5 then '35-35.5'
when rm.VALUE > 35.5 and rm.VALUE <= 36 then '35.5-36'
when rm.VALUE > 36 and rm.VALUE <= 36.5 then '36-36.5'
when rm.VALUE > 36.5 and rm.VALUE <= 37 then '36.5-37'
when rm.VALUE > 37 and rm.VALUE <= 37.5 then '37-37.5'
when rm.VALUE > 37.5 and rm.VALUE <= 38 then '37.5-38'
when rm.VALUE > 38 and rm.VALUE <= 38.5 then '38-38.5'
when rm.VALUE > 38.5 and rm.VALUE <= 39 then '38.5-39'
when rm.VALUE > 39 and rm.VALUE <= 39.5 then '39-39.5'
when rm.VALUE > 39.5 and rm.VALUE <= 40 then '39.5-40'
else 'HIGH'
end as [RANGE],
isnull(count(1),0) as [Count]
from Tbl_roomMonitoring rm
--where Timestamp between '2023-05-15 00:00:00' and '2023-05-15 23:59:59'
group by
case
when rm.VALUE < 0 then '-LOW'
when rm.VALUE > 0 and rm.VALUE <= 5 then '00-05'
when rm.VALUE > 5 and rm.VALUE <= 10 then '05-10'
when rm.VALUE > 10 and rm.VALUE <= 10.5 then '10-10.5'
when rm.VALUE > 10.5 and rm.VALUE <= 11 then '10.5-11'
when rm.VALUE > 11 and rm.VALUE <= 11.5 then '11-11.5'
when rm.VALUE > 11.5 and rm.VALUE <= 12 then '11.5-12'
when rm.VALUE > 12 and rm.VALUE <= 12.5 then '12-12.5'
when rm.VALUE > 12.5 and rm.VALUE <= 13 then '12.5-13'
when rm.VALUE > 13 and rm.VALUE <= 13.5 then '13-13.5'
when rm.VALUE > 13.5 and rm.VALUE <= 14 then '13.5-14'
when rm.VALUE > 14 and rm.VALUE <= 14.5 then '14-14.5'
when rm.VALUE > 14.5 and rm.VALUE <= 15 then '14.5-15'
when rm.VALUE > 15 and rm.VALUE <= 15.5 then '15-15.5'
when rm.VALUE > 15.5 and rm.VALUE <= 16 then '15.5-16'
when rm.VALUE > 16 and rm.VALUE <= 16.5 then '16-16.5'
when rm.VALUE > 16.5 and rm.VALUE <= 17 then '16.5-17'
when rm.VALUE > 17 and rm.VALUE <= 17.5 then '17-17.5'
when rm.VALUE > 17.5 and rm.VALUE <= 18 then '17.5-18'
when rm.VALUE > 18 and rm.VALUE <= 18.5 then '18-18.5'
when rm.VALUE > 18.5 and rm.VALUE <= 19 then '18.5-19'
when rm.VALUE > 19 and rm.VALUE <= 19.5 then '19-19.5'
when rm.VALUE > 19.5 and rm.VALUE <= 20 then '19.5-20'
when rm.VALUE > 20 and rm.VALUE <= 20.5 then '20-20.5'
when rm.VALUE > 20.5 and rm.VALUE <= 21 then '20.5.21'
when rm.VALUE > 21 and rm.VALUE <= 21.5 then '21-21.5'
when rm.VALUE > 21.5 and rm.VALUE <= 22 then '21.5-22'
when rm.VALUE > 22 and rm.VALUE <= 22.5 then '22-22.5'
when rm.VALUE > 22.5 and rm.VALUE <= 23 then '22.5-23'
when rm.VALUE > 23 and rm.VALUE <= 23.5 then '23-23.5'
when rm.VALUE > 23.5 and rm.VALUE <= 24 then '23.5-24'
when rm.VALUE > 24 and rm.VALUE <= 24.5 then '24-24.5'
when rm.VALUE > 24.5 and rm.VALUE <= 25 then '24.5-25'
when rm.VALUE > 25 and rm.VALUE <= 25.5 then '25-25.5'
when rm.VALUE > 25.5 and rm.VALUE <= 26 then '25.5.26'
when rm.VALUE > 26 and rm.VALUE <= 26.5 then '26-26.5'
when rm.VALUE > 26.5 and rm.VALUE <= 27 then '26.5-27'
when rm.VALUE > 27 and rm.VALUE <= 27.5 then '27-27.5'
when rm.VALUE > 27.5 and rm.VALUE <= 28 then '27.5-28'
when rm.VALUE > 28 and rm.VALUE <= 28.5 then '28-28.5'
when rm.VALUE > 28.5 and rm.VALUE <= 29 then '28.5-29'
when rm.VALUE > 29 and rm.VALUE <= 29.5 then '29-29.5'
when rm.VALUE > 29.5 and rm.VALUE <= 30 then '29.5-30'
when rm.VALUE > 30 and rm.VALUE <= 30.5 then '30-30.5'
when rm.VALUE > 30.5 and rm.VALUE <= 31 then '30.5-31'
when rm.VALUE > 31 and rm.VALUE <= 31.5 then '31-31.5'
when rm.VALUE > 31.5 and rm.VALUE <= 32 then '31.5-32'
when rm.VALUE > 32 and rm.VALUE <= 32.5 then '32-32.5'
when rm.VALUE > 32.5 and rm.VALUE <= 33 then '32.5-33'
when rm.VALUE > 33 and rm.VALUE <= 33.5 then '33-33.5'
when rm.VALUE > 33.5 and rm.VALUE <= 34 then '33.5-34'
when rm.VALUE > 34 and rm.VALUE <= 34.5 then '34-34.5'
when rm.VALUE > 34.5 and rm.VALUE <= 35 then '34.5-35'
when rm.VALUE > 35 and rm.VALUE <= 35.5 then '35-35.5'
when rm.VALUE > 35.5 and rm.VALUE <= 36 then '35.5-36'
when rm.VALUE > 36 and rm.VALUE <= 36.5 then '36-36.5'
when rm.VALUE > 36.5 and rm.VALUE <= 37 then '36.5-37'
when rm.VALUE > 37 and rm.VALUE <= 37.5 then '37-37.5'
when rm.VALUE > 37.5 and rm.VALUE <= 38 then '37.5-38'
when rm.VALUE > 38 and rm.VALUE <= 38.5 then '38-38.5'
when rm.VALUE > 38.5 and rm.VALUE <= 39 then '38.5-39'
when rm.VALUE > 39 and rm.VALUE <= 39.5 then '39-39.5'
when rm.VALUE > 39.5 and rm.VALUE <= 40 then '39.5-40'
else 'HIGH'
end
order by
[RANGE] asc
This is the result of the query.
RANGE Count
16-16.5 352
16.5-17 1860
17-17.5 2859
17.5-18 5830
18-18.5 8160
18.5-19 10630
19-19.5 18112
19.5-20 19205
20-20.5 20593
20.5.21 20363
21-21.5 15933
21.5-22 19221
22-22.5 14155
22.5-23 12566
23-23.5 7830
23.5-24 6466
24-24.5 3138
24.5-25 1655
25-25.5 757
25.5.26 1158
26-26.5 803
26.5-27 1297
27-27.5 2237
27.5-28 2179
28-28.5 1518
28.5-29 1177
29-29.5 785
29.5-30 404
30-30.5 178
30.5-31 35
This is the result that I am expecting.
RANGE Count
-LOW 0
00-05 0
05-10 0
10.5-11 0
11-11.5 0
11.5-12 0
12-12.5 0
12.5-13 0
13-13.5 0
13.5-14 0
14-14.5 0
14.5-15 0
15-15.5 0
15.5-16 0
16-16.5 352
16.5-17 1860
17-17.5 2859
17.5-18 5830
18-18.5 8160
18.5-19 10630
19-19.5 18112
19.5-20 19205
20-20.5 20593
20.5.21 20363
21-21.5 15933
21.5-22 19221
22-22.5 14155
22.5-23 12566
23-23.5 7830
23.5-24 6466
24-24.5 3138
24.5-25 1655
25-25.5 757
25.5.26 1158
26-26.5 803
26.5-27 1297
27-27.5 2237
27.5-28 2179
28-28.5 1518
28.5-29 1177
29-29.5 785
29.5-30 404
30-30.5 178
30.5-31 35
So on and so forth.
HIGH N
答案1
得分: 1
这是一个处理方法，简单地将这些范围提供为行，然后左连接你的数据到这些范围，例如 left join data on data.col > range_from and data.col <= range_to。通过使用这种方法，即使没有匹配的数据进行计数，每个范围都将出现在最终结果中。
我只是将你的 case 表达式转换为值来生成这个：
select
range_from, range_to, range_name, count(left_joined_table.*) -- 仅计数左连接的数据
from (
values
(-9999999999,0,'-LOW'), (0,5,'00-05'), (5,10,'05-10')
, (10,10.5,'10-10.5'), (10.5,11,'10.5-11'), (11,11.5,'11-11.5'), (11.5,12,'11.5-12'), (12,12.5,'12-12.5')
, (12.5,13,'12.5-13'), (13,13.5,'13-13.5'), (13.5,14,'13.5-14'), (14,14.5,'14-14.5'), (14.5,15,'14.5-15')
, (15,15.5,'15-15.5'), (15.5,16,'15.5-16'), (16,16.5,'16-16.5'), (16.5,17,'16.5-17'), (17,17.5,'17-17.5')
, (17.5,18,'17.5-18'), (18,18.5,'18-18.5'), (18.5,19,'18.5-19'), (19,19.5,'19-19.5'), (19.5,20,'19.5-20')
, (20,20.5,'20-20.5'), (20.5,21,'20.5.21'), (21,21.5,'21-21.5'), (21.5,22,'21.5-22'), (22,22.5,'22-22.5')
, (22.5,23,'22.5-23'), (23,23.5,'23-23.5'), (23.5,24,'23.5-24'), (24,24.5,'24-24.5'), (24.5,25,'24.5-25')
, (25,25.5,'25-25.5'), (25.5,26,'25.5.26'), (26,26.5,'26-26.5'), (26.5,27,'26.5-27'), (27,27.5,'27-27.5')
, (27.5,28,'27.5-28'), (28,28.5,'28-28.5'), (28.5,29,'28.5-29'), (29,29.5,'29-29.5'), (29.5,30,'29.5-30')
, (30,30.5,'30-30.5'), (30.5,31,'30.5-31'), (31,31.5,'31-31.5'), (31.5,32,'31.5-32'), (32,32.5,'32-32.5')
, (32.5,33,'32.5-33'), (33,33.5,'33-33.5'), (33.5,34,'33.5-34'), (34,34.5,'34-34.5'), (34.5,35,'34.5-35')
, (35,35.5,'35-35.5'), (35.5,36,'35.5-36'), (36,36.5,'36-36.5'), (36.5,37,'36.5-37'), (37,37.5,'37-37.5')
, (37.5,38,'37.5-38'), (38,38.5,'38-38.5'), (38.5,39,'38.5-39'), (39,39.5,'39-39.5'), (39.5,40,'39.5-40')
, (40,9999999999,'HIGH')
) range (range_from,range_to,range_name)
left join left_joined_table on left_joined_table.col > range_from
and left_joined_table.col <= range_to
group by
range_from, range_to, range_name
可能可以使用递归 CTE 生成一组范围，但在这里我认为使用值已经足够了。
最后说明一下，根据我的经验，范围通常以 somecol >= range_from and somecol < range_to 的方式工作，但我想这并没有硬性规定。
英文:
A way to do this is simply provide the ranges as rows, then left join your data to those ranges e.g. left join data on data.col > range_from and data.col <= range_to By using this approach every range will be in the final result even if there is no matching data to count
I simply converted your case expression into values to produce this:
select
range_from,range_to,range_name, count(left_joined_table.*) -- count only from the left joined data
from (
values
(-9999999999,0,'-LOW'), (0,5,'00-05'), (5,10,'05-10')
, (10,10.5,'10-10.5'), (10.5,11,'10.5-11'), (11,11.5,'11-11.5'), (11.5,12,'11.5-12'), (12,12.5,'12-12.5')
, (12.5,13,'12.5-13'), (13,13.5,'13-13.5'), (13.5,14,'13.5-14'), (14,14.5,'14-14.5'), (14.5,15,'14.5-15')
, (15,15.5,'15-15.5'), (15.5,16,'15.5-16'), (16,16.5,'16-16.5'), (16.5,17,'16.5-17'), (17,17.5,'17-17.5')
, (17.5,18,'17.5-18'), (18,18.5,'18-18.5'), (18.5,19,'18.5-19'), (19,19.5,'19-19.5'), (19.5,20,'19.5-20')
, (20,20.5,'20-20.5'), (20.5,21,'20.5.21'), (21,21.5,'21-21.5'), (21.5,22,'21.5-22'), (22,22.5,'22-22.5')
, (22.5,23,'22.5-23'), (23,23.5,'23-23.5'), (23.5,24,'23.5-24'), (24,24.5,'24-24.5'), (24.5,25,'24.5-25')
, (25,25.5,'25-25.5'), (25.5,26,'25.5.26'), (26,26.5,'26-26.5'), (26.5,27,'26.5-27'), (27,27.5,'27-27.5')
, (27.5,28,'27.5-28'), (28,28.5,'28-28.5'), (28.5,29,'28.5-29'), (29,29.5,'29-29.5'), (29.5,30,'29.5-30')
, (30,30.5,'30-30.5'), (30.5,31,'30.5-31'), (31,31.5,'31-31.5'), (31.5,32,'31.5-32'), (32,32.5,'32-32.5')
, (32.5,33,'32.5-33'), (33,33.5,'33-33.5'), (33.5,34,'33.5-34'), (34,34.5,'34-34.5'), (34.5,35,'34.5-35')
, (35,35.5,'35-35.5'), (35.5,36,'35.5-36'), (36,36.5,'36-36.5'), (36.5,37,'36.5-37'), (37,37.5,'37-37.5')
, (37.5,38,'37.5-38'), (38,38.5,'38-38.5'), (38.5,39,'38.5-39'), (39,39.5,'39-39.5'), (39.5,40,'39.5-40')
, (40,9999999999,'HIGH')
) range (range_from,range_to,range_name)
left join left_joined_table on left_joined_table.col > range_from
and left_joined_table.col <= range_to
group by
range_from,range_to,range_name
It might be possible to generate a set of ranges using a recursive CTE, but here I think using values is more than sufficient.
As a final note, it is more common in my experience for ranges to work somecol >= range_from and somecol < range_to but there is no mandate on this I guess.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论