MSSQL查询：如何每2小时计算一次（动态）？

时间：2022-10-13 15:48:54

I'm new to this site and couldn't find a similar question, and not sure if this is even possible to query in SQL, but, what the heck, worth trying...

我是这个网站的新手,找不到类似的问题,也不确定这是否有可能在SQL中查询,但是,到底是什么,值得一试......

I have a access control system which writes the entries log into my DB. Attached sample from my data table:

我有一个访问控制系统,它将条目日志写入我的数据库。来自我的数据表的附加样本:

tpxID   Rec_Creation_Date
2995392 2018-03-06 11:50:45.000
2995391 2018-03-06 11:39:48.000
2995390 2018-03-06 11:30:58.453
2995389 2018-03-06 11:30:49.297
2995388 2018-03-06 11:30:30.687
2995387 2018-03-06 11:30:22.547
2995386 2018-03-06 11:30:13.483
2995385 2018-03-06 11:30:04.813
2995384 2018-03-06 11:29:57.640
2995383 2018-03-06 11:29:49.670

The idea is, that I would like to find out if there have been a time frame of 2 hours which I had more than 200 entries?

我的想法是,我想知道是否有2个小时的时间框架,我有超过200个条目?

I mean dynamic query which will not look at round hours only.

我的意思是动态查询,不会只查看圆周时间。

Like not only to query 11:00-13:00 but also to query 11:01-13:01 and 11:02-13:02 etc...

不仅要查询11:00-13:00,还要查询11:01-13:01和11:02-13:02等...

Thanks in advance.

提前致谢。

2 个解决方案

#1

1  

select t1.dt, count(*) as cnt 
from table t1 
join table t2 
  on t2.dt > t1.dt 
 and datediff(mi, t1.dt, t2.dt) < 120 
group by t1.dt 
having count(*) > 200

#2

0  

We can use CTE and GROUP BY with HAVING for this -

我们可以使用CTE和GROUP BY与HAVING - 

declare @xyz table (id int identity(1,1),tpxID int,Rec_Creation_Date datetime)

insert into @xyz (tpxID, Rec_Creation_Date)
select 2995392, '2018-03-06 11:50:45.000' union all
select 2995391, '2018-03-06 11:39:48.000' union all
select 2995390, '2018-03-06 11:30:58.453' union all
select 2995389, '2018-03-06 11:30:49.297' union all
select 2995388, '2018-03-06 11:30:30.687' union all
select 2995387, '2018-03-06 11:30:22.547' union all
select 2995386, '2018-03-06 11:30:13.483' union all
select 2995385, '2018-03-06 11:30:04.813' union all
select 2995384, '2018-03-06 11:29:57.640' union all
select 2995383, '2018-03-06 11:29:49.670' union all
select 2995383, '2018-03-06 09:29:49.670'

;with cte as (
    select
        x.Rec_Creation_Date as StartDT,
        y.Rec_Creation_Date as EndDT
    from @xyz as x
    inner join @xyz as y on x.Rec_Creation_Date < y.Rec_Creation_Date
    where datediff(HOUR,x.Rec_Creation_Date, y.Rec_Creation_Date) >= 2
)
select
    cte.StartDT,
    cte.EndDT
from cte
inner join @xyz as x on x.Rec_Creation_Date between cte.StartDT and cte.EndDT
group by cte.StartDT , cte.EndDT
having count(*) > 200
order by cte.StartDT , cte.EndDT

#1

1  

select t1.dt, count(*) as cnt 
from table t1 
join table t2 
  on t2.dt > t1.dt 
 and datediff(mi, t1.dt, t2.dt) < 120 
group by t1.dt 
having count(*) > 200

#2

0  

We can use CTE and GROUP BY with HAVING for this -

我们可以使用CTE和GROUP BY与HAVING - 

declare @xyz table (id int identity(1,1),tpxID int,Rec_Creation_Date datetime)

insert into @xyz (tpxID, Rec_Creation_Date)
select 2995392, '2018-03-06 11:50:45.000' union all
select 2995391, '2018-03-06 11:39:48.000' union all
select 2995390, '2018-03-06 11:30:58.453' union all
select 2995389, '2018-03-06 11:30:49.297' union all
select 2995388, '2018-03-06 11:30:30.687' union all
select 2995387, '2018-03-06 11:30:22.547' union all
select 2995386, '2018-03-06 11:30:13.483' union all
select 2995385, '2018-03-06 11:30:04.813' union all
select 2995384, '2018-03-06 11:29:57.640' union all
select 2995383, '2018-03-06 11:29:49.670' union all
select 2995383, '2018-03-06 09:29:49.670'

;with cte as (
    select
        x.Rec_Creation_Date as StartDT,
        y.Rec_Creation_Date as EndDT
    from @xyz as x
    inner join @xyz as y on x.Rec_Creation_Date < y.Rec_Creation_Date
    where datediff(HOUR,x.Rec_Creation_Date, y.Rec_Creation_Date) >= 2
)
select
    cte.StartDT,
    cte.EndDT
from cte
inner join @xyz as x on x.Rec_Creation_Date between cte.StartDT and cte.EndDT
group by cte.StartDT , cte.EndDT
having count(*) > 200
order by cte.StartDT , cte.EndDT
标签：

相关文章