-- 数据量比较大的情况，统计十分钟内每秒钟执行次数
declare @begintime varchar(100); -- 开始时间
declare @endtime varchar(100); -- 结束时间
declare @num int; -- 结束时间
set @begintime = '2019-08-10 09:10:00' -- 开始时间
set @endtime = '2019-08-10 09:20:00' -- 结束时间 set @num = (select count(1) from PM_SYS_LOGINLOG where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime) print(@num)
select @num as 总条数,
AVG(调用总数) as 十分钟内每秒平均执行次数
from
(select s.请求时间,
(调用一次的总数+
(
select 调用多次 from
(
select 请求时间, COUNT(1) 调用多次 from
(
select CONVERT(varchar(100),loginTime, 20) as 请求时间, count(1) as 调用次数 from PM_SYS_LOGINLOG
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) > 1) o where 请求时间 = s.请求时间 group by o.请求时间
) o
)
) as 调用总数
from
(
select t.请求时间, count(1) as 调用一次的总数
from
(
select CONVERT(varchar(100),loginTime, 20) as 请求时间, count(1) as 调用次数
from PM_SYS_LOGINLOG
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) = 1
) t group by 请求时间
) s
) m
查询前一秒执行次数
declare @str varchar(100);
set @str = convert(varchar,dateadd(ss,-1,getdate()),20)
--select @str
--print(@str) select @str as 执行时间, count(1) + (
select count(1) from (select top 20 CONVERT(varchar(100),loginTime, 20) as 请求时间,
count(1) as 调用次数 from PM_SYS_LOGINLOG
where CONVERT(varchar(100),loginTime, 20) = @str
group by loginTime
having count(1) = 2
order by loginTime desc) as o
) as 执行次数
from (
select top 20 CONVERT(varchar(100),loginTime, 20) as 请求时间,
count(1) as 调用次数 from PM_SYS_LOGINLOG
where CONVERT(varchar(100),loginTime, 20) = @str
group by loginTime
--having count(1) = 1
order by loginTime desc
) t
聚合函数分组查询最大值
select max(t.总数) as 最大值 from (select Token as 令牌, count(1) as 总数 from PM_SYS_LOGINLOG group by token having count(1) > max(1)) as t
select top 1 count(1) as 总数 from PM_SYS_LOGINLOG group by token having count(1) > 1 order by 总数 desc
第二次优化统计半个小时时间统计每秒钟执行次数条数
declare @begintime varchar(100); -- 开始时间
declare @endtime varchar(100); -- 结束时间
--declare @tmpTab varchar(50); -- 定义临时表名称前缀
declare @num int; -- 结束时间
set @begintime = '2019-08-10 09:00:00' -- 开始时间
set @endtime = '2019-08-10 09:30:00' -- 结束时间
-- 定义临时表名称前缀加时间戳
-- set @tmpTab = '_' + DateName(YEAR,GetDate()) + DateName(MONTH,GetDate()) + DateName(DAY,GetDate()) + DateName(HOUR,GetDate()) + DateName(MINUTE,GetDate()) + DateName(S,GetDate()) + DateName(MILLISECOND,GetDate()) -- set @num = (select count(1) from PM_SYS_LOGINLOG where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime) --print(@data)
print(@num)
--print(@tmpTab) -- 创建临时表 判断是否存在如果不存在则删除
if exists(select * from sys.tables where name = '_tmpTab')
begin
drop table _tmpTab
end
-- 创建临时表
create table _tmpTab
(
ID int,
LoginName nvarchar(20),
Token varchar(50),
loginTime datetime,
)
-- 将数据插入到临时表
insert into _tmpTab(id, loginName,loginTime, Token)
(select ID, LoginName, loginTime, Token from PM_SYS_LOGINLOG where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime) -- 查询统计临时表数据总条数
set @num = (select count(1) from _tmpTab) select @num as 总条数,
AVG(调用总数) as 十分钟内每秒平均执行次数
from
(select s.请求时间,
(调用一次的总数+
(
select 调用多次 from
(
select 请求时间, COUNT(1) 调用多次 from
(
select CONVERT(varchar(100),loginTime, 20) as 请求时间, count(1) as 调用次数 from _tmpTab
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) > 1) o where 请求时间 = s.请求时间 group by o.请求时间
) o
)
) as 调用总数
from
(
select t.请求时间, count(1) as 调用一次的总数
from
(
select CONVERT(varchar(100),loginTime, 20) as 请求时间, count(1) as 调用次数
from _tmpTab
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) = 1
) t group by 请求时间
) s
) m -- 使用完毕删除临时表
drop table _tmpTab
第三次最终优化
declare @begintime varchar(100); -- 开始时间
declare @endtime varchar(100); -- 结束时间
declare @startTime datetime; -- 查询开始时间
declare @num int; -- 数据总条数
set @begintime = '2019-08-10 08:00:00' -- 开始时间
set @endtime = '2019-08-10 14:20:00' -- 结束时间 set @startTime = GETDATE(); -- 创建临时表 判断是否存在如果不存在则删除
if exists(select * from sys.tables where name = '_tmpTab')
begin
drop table _tmpTab
end
-- 创建临时表
create table _tmpTab
(
ID int,
LoginName nvarchar(20),
Token varchar(50),
loginTime datetime,
) -- 将数据插入到临时表
insert into _tmpTab(id, loginName,loginTime, Token)
(select ID, LoginName, loginTime, Token from PM_SYS_LOGINLOG where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime) -- 创建临时表用于存储临时查到的数据进行求平均数
if exists(select * from sys.tables where name = '_tmpAvg')
begin
drop table _tmpAvg
end -- 创建临时表存储查询到的数据
create table _tmpAvg
(
reqTime varchar(100),
reqNum int
) -- 查询统计临时表数据总条数
set @num = (select count(1) from _tmpTab) -- 添加数据到临时表
insert into _tmpAvg(reqTime, reqNum)
(select x.reqTime, (x.reqNum+m.reqNum) as reqNum
from (
(select reqTime, sum(1) reqNum from
(select CONVERT(varchar(100),loginTime, 20) as reqTime, (count(1) * 1) as reqNum from _tmpTab
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) = 1
) o group by o.reqTime
) as x left join
(select reqTime, sum(1) as reqNum from
(select CONVERT(varchar(100),loginTime, 20) as reqTime, (count(1) * 2) as reqNum from _tmpTab
where CONVERT(varchar(100),loginTime, 20) >= @begintime and CONVERT(varchar(100),loginTime, 20) <= @endtime
group by loginTime having count(1) = 2
) o group by o.reqTime
) as m on x.reqTime = m.reqTime)) select DATEDIFF(MILLISECOND, @startTime, GETDATE()) as 查询耗时单位秒, @num as 数据总条数, avg(reqNum) 每秒钟执行次数, @begintime 查询开始时间, @endtime as 查询结束时间 from _tmpAvg -- 使用完毕删除临时表
drop table _tmpAvg
drop table _tmpTab
最后优化结果：平均每秒钟执行计算 10 条数据
新手初来乍到：代码亲笔手写，高手路过勿喷，请多多指点