sql server 大数据，统计分组查询，数据量比较大计算每秒钟执行数据执行次数...

深碍√TFBOYSˉ_ 2021-10-23 15:28 302阅读 0赞

\-- 数据量比较大的情况，统计十分钟内每秒钟执行次数

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

![1108483-20190810102424989-514389598.png][]

查询前一秒执行次数

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

![1108483-20190810144351894-1535426059.png][]

最后优化结果：平均每秒钟执行计算 10 条数据

新手初来乍到：代码亲笔手写，高手路过勿喷，请多多指点

原链接：[https://www.cnblogs.com/FGang/p/11330736.html][https_www.cnblogs.com_FGang_p_11330736.html]

转载于:https://www.cnblogs.com/FGang/p/11330736.html

[1108483-20190810102424989-514389598.png]: /images/20211023/4228184538704932b78e35b10991d152.png
[1108483-20190810144351894-1535426059.png]: /images/20211023/9acfabf0c3cb476691c1d3a3f104d0af.png
[https_www.cnblogs.com_FGang_p_11330736.html]: https://www.cnblogs.com/FGang/p/11330736.html