sql - sql server中基于分区的Min()和Max()

Question

我想使用最小和最大函数，但要满足某些条件。

Create Table #Test (Id Int Identity(1,1), Category Varchar(100), DateTimeStamp DateTime)



    Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 01:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 02:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 03:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 04:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 05:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 06:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 07:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 08:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 09:00:13.503')
        Insert into #Test (Category,DateTimeStamp) values ('c2','2019-08-13 10:00:13.503')  
        Insert into #Test (Category,DateTimeStamp) values ('c1','2019-08-13 11:00:13.503')

当前查询与输出

select category, min(DateTimeStamp) as minn , max(DateTimeStamp) as maxx from #Test
group by category

电流输出

预期输出

Answer 1

您可以尝试以下 - 这是一个间隙和孤岛问题

<强> DEMO

select category, min(datetimestamp),max(datetimestamp)
from
(
select *,row_number() over(order by datetimestamp) -
row_number() over(partition by category order by datetimestamp) as rn2
from #Test
)A group by category,rn2 order by 2

输出:

category       minval               maxval
c1             13/08/2019 01:00:13  13/08/2019 05:00:13
c2             13/08/2019 06:00:13  13/08/2019 10:00:13
c1             13/08/2019 11:00:13  13/08/2019 11:00:13