假如我一张表中存有汽车的速度记录，大概每秒记录一次，但也不一定，也可能两秒三秒一次，我需要列出每10分钟的平局速度，得到这个表以后，我需要把平均速度归类，30km/h以下为低速，30-60km/h为中速，60以上为高速，得出每类出现的次数，请问这个SQL如何写呢？
已有表
time speed(km/h)
12:00:00 35.1
12:00:01 34.2
12:00:05 40.5
..........
13:00:01 80.3
13:00:03 81.7
13:00:04 82.1
我最后希望得到的表
type count
low 23
mid 46
hi 35
------解决思路----------------------
WITH test([time],speed) AS (
SELECT Convert(datetime, '12:00:00', 114), 35.1 UNION ALL
SELECT Convert(datetime, '12:00:01', 114), 34.2 UNION ALL
SELECT Convert(datetime, '12:00:05', 114), 40.5 UNION ALL
SELECT Convert(datetime, '12:10:03', 114), 20.5 UNION ALL
SELECT Convert(datetime, '13:00:01', 114), 80.3 UNION ALL
SELECT Convert(datetime, '13:00:03', 114), 81.7 UNION ALL
SELECT Convert(datetime, '13:00:04', 114), 82.1
)
,t1 AS (
SELECT DateDiff(minute,
Convert(datetime, '00:00:00', 114),
[time]
) / 10 AS tenMinutes,
speed
FROM test
)
,t2 AS (
SELECT tenMinutes,
AVG(speed) AS AvgSpeed
FROM t1
GROUP BY tenMinutes
)
,t3 AS (
SELECT tenMinutes,
CASE WHEN AvgSpeed <= 30.0 THEN
'low'
WHEN AvgSpeed <= 60.0 THEN
'mid'
ELSE
'hi'
END AS [type]
FROM t2
)
SELECT [type],
Count(*) AS [count]
FROM t3
GROUP BY [type]
type count
---- -----------
hi 1
low 1
mid 1