34,593
社区成员
发帖
与我相关
我的任务
分享
;with cte as
(
select (convert(nvarchar(14), [DateTime], 120) + (case
when datepart(Minute, [DateTime])< 5 then '00:00'
when datepart(Minute, [DateTime])< 10 then '05:00'
when datepart(Minute, [DateTime])< 15 then '10:00'
when datepart(Minute, [DateTime])< 20 then '15:00'
when datepart(Minute, [DateTime])< 25 then '20:00'
when datepart(Minute, [DateTime])< 30 then '25:00'
when datepart(Minute, [DateTime])< 35 then '30:00'
when datepart(Minute, [DateTime])< 40 then '35:00'
when datepart(Minute, [DateTime])< 45 then '40:00'
when datepart(Minute, [DateTime])< 50 then '45:00'
when datepart(Minute, [DateTime])< 55 then '50:00'
when datepart(Minute, [DateTime])< 60 then '55:00' end)) as newdt,
InterfaceID, In_Averagebps
from tbl
-- where [DateTime] >= '2015-06-01' and [DateTime] < '2015-08-01' -- 时间段条件
)
select newdt, InterfaceID, avg(In_Averagebps) from cte group by newdt, InterfaceID
DECLARE @date datetime
DECLARE @splitMinutes int
SET @date = '2015-06-22'
SET @splitMinutes = 5
;WITH /* 测试数据
table1(InterfaceID,[DateTime],In_Averagebps) AS (
SELECT 991,'2015-06-22 00:01:46.000',1242305 UNION ALL
SELECT 991,'2015-06-22 00:03:46.997',1326974 UNION ALL
SELECT 991,'2015-06-22 00:05:46.997',1074069 UNION ALL
SELECT 991,'2015-06-22 00:07:47.000',1290775 UNION ALL
SELECT 991,'2015-06-22 00:09:48.997',1204597 UNION ALL
SELECT 991,'2015-06-22 00:11:48.997',943425.3 UNION ALL
SELECT 991,'2015-06-22 00:13:50.000',1045493 UNION ALL
SELECT 991,'2015-06-22 00:15:50.000',1017358 UNION ALL
SELECT 991,'2015-06-22 00:17:50.997',1490217 UNION ALL
SELECT 991,'2015-06-22 00:19:49.997',1281273 UNION ALL
SELECT 991,'2015-06-22 00:21:49.997',1538141 UNION ALL
SELECT 991,'2015-06-22 00:23:51.000',1563413 UNION ALL
SELECT 991,'2015-06-22 00:25:55.000',1517572 UNION ALL
SELECT 991,'2015-06-22 00:27:55.000',1612961 UNION ALL
SELECT 991,'2015-06-22 00:29:58.000',1811118 UNION ALL
SELECT 991,'2015-06-22 00:31:56.997',1807192 UNION ALL
SELECT 991,'2015-06-22 00:33:58.000',1761714 UNION ALL
SELECT 991,'2015-06-22 00:35:58.000',1655595 UNION ALL
SELECT 991,'2015-06-22 00:37:57.997',1548015 UNION ALL
SELECT 991,'2015-06-22 00:40:01.000',1564148 UNION ALL
SELECT 991,'2015-06-22 00:42:00.000',1433731 UNION ALL
SELECT 991,'2015-06-22 00:44:00.997',972750.4 UNION ALL
SELECT 991,'2015-06-22 00:46:03.000',1173996 UNION ALL
SELECT 991,'2015-06-22 00:48:00.997',1185221
) */
,a AS (
SELECT DATEADD(minute,@splitMinutes * number,@date) dt1,
DATEADD(minute,@splitMinutes * (number+1),@date) dt2
FROM master..spt_values
WHERE type = 'p'
AND number < (1440/@splitMinutes)
),
b AS (
SELECT DISTINCT InterfaceID
FROM table1
)
SELECT b.InterfaceID,
a.dt1 [Datetime],
AVG(t.In_Averagebps) In_Averagebps
FROM a
JOIN b
ON 1=1
LEFT JOIN table1 t
ON b.InterfaceID = t.InterfaceID
AND t.[Datetime] >= a.dt1
AND t.[Datetime] < a.dt2
GROUP BY b.InterfaceID, a.dt1
ORDER BY b.InterfaceID, a.dt1
InterfaceID Datetime In_Averagebps
----------- ----------------------- ---------------------------------------
991 2015-06-22 00:00:00.000 1284639.500000
991 2015-06-22 00:05:00.000 1189813.666666
991 2015-06-22 00:10:00.000 994459.150000
991 2015-06-22 00:15:00.000 1262949.333333
991 2015-06-22 00:20:00.000 1550777.000000
991 2015-06-22 00:25:00.000 1647217.000000
991 2015-06-22 00:30:00.000 1784453.000000
991 2015-06-22 00:35:00.000 1601805.000000
991 2015-06-22 00:40:00.000 1323543.133333
991 2015-06-22 00:45:00.000 1179608.500000
991 2015-06-22 00:50:00.000 NULL
...
991 2015-06-22 23:55:00.000 NULL