sql - 每特定时间选择一行

Question

我有一个看起来像这样的表:

ID  UserID  DateTime             TypeID

1     1     1/1/2010 10:00:00      1
2     2     1/1/2010 10:01:50      1
3     1     1/1/2010 10:02:50      1
4     1     1/1/2010 10:03:50      1
5     1     1/1/2010 11:00:00      1
6     2     1/1/2010 11:00:50      1

我需要查询其typeID为1的所有用户，但每15分钟只有一行

例如，结果应为:

1     1     1/1/2010 10:00:00      1
2     2     1/1/2010 10:01:50      1
5     1     1/1/2010 11:00:00      1
6     2     1/1/2010 11:00:50      1

未显示ID 3和4，因为自上次针对特定userID的记录以来未经过15分钟。

之所以显示ID 1和ID 5，是因为该特定用户ID已超过15分钟
与ID 2和6相同。

我该怎么做？

谢谢

Answer 1

尝试这个:

select * from 
(
      select ID, UserID, 
      Max(DateTime) as UpperBound, 
      Min(DateTime) as LowerBound, 
      TypeID 
      from the_table
      where TypeID=1
      group by ID,UserID,TypeID
) t 
where datediff(mi,LowerBound,UpperBound)>=15

编辑:因为我的上述尝试很糟糕，所以我添加了另一种使用不需要递归的Sql表值函数的方法，因为可以理解，这是一个大问题。

步骤1:按如下方式创建表类型(LoginDate是Shay示例中的DateTime列-DateTime名称与SQL数据类型冲突，我认为避免这些冲突是明智的)

CREATE TYPE [dbo].[TVP] AS TABLE(
    [ID] [int] NOT NULL,
    [UserID] [int] NOT NULL,
    [LoginDate] [datetime] NOT NULL,
    [TypeID] [int] NOT NULL
)
GO

步骤2:创建以下函数:

CREATE FUNCTION [dbo].[fnGetLoginFreq] 
(
    -- notice: TVP is the type (declared above)
    @TVP TVP readonly
)
RETURNS 
@Table_Var TABLE 
(
    -- This will be our result set
    ID int, 
    UserId int,
    LoginTime datetime,
    TypeID int,
    RowNumber int
)
AS
BEGIN
    --We will insert records in this table as we go through the rows in the
    --table passed in as parameter and decide that we should add an entry because
    --15' had elapsed between logins 
    DECLARE @temp  table
    (
        ID int,
        UserId int, 
        LoginTime datetime,
        TypeID int
    )
    -- seems silly, but is not because we need to add a row_number column to help
    -- in our iteration and table-valued paramters cannot be modified inside the function
    insert into @Table_var
    select ID,UserID,Logindate,TypeID,row_number() OVER(ORDER BY UserID,LoginDate) AS [RowNumber] 
    from @TVP order by UserID asc,LoginDate desc

    declare @Index int,@End int,@CurrentLoginTime datetime, @NextLoginTime datetime, @CurrentUserID int , @NextUserID int

    select @Index=1,@End=count(*) from @Table_var

    while(@Index<=@End)
    begin        
            select @CurrentLoginTime=LoginTime,@CurrentUserID=UserID from @Table_var where RowNumber=@Index
            select @NextLoginTime=LoginTime,@NextUserID=UserID from @Table_var where RowNumber=(@Index+1)

            if(@CurrentUserID=@NextUserID)
            begin
                if( abs(DateDiff(mi,@CurrentLoginTime,@NextLoginTime))>=15)
                begin   
                    insert into @temp
                    select ID,UserID,LoginTime,TypeID
                    from @Table_var
                    where RowNumber=@Index
                end     
            END
            else 
            bEGIN
                    insert into @temp
                    select ID,UserID,LoginTime,TypeID
                    from @Table_var
                    where RowNumber=@Index and UserID=@CurrentUserID 
            END

            if(@Index=@End)--last element?
            begin
                insert into @temp
                select ID,UserID,LoginTime,TypeID
                from @Table_var
                where RowNumber=@Index and not 
                abs((select datediff(mi,@CurrentLoginTime,max(LoginTime)) from @temp where UserID=@CurrentUserID))<=14
            end

            select @Index=@Index+1
    end 

    delete  from @Table_var

    insert into @Table_var
    select ID, UserID ,LoginTime ,TypeID ,row_number() OVER(ORDER BY UserID,LoginTime) AS 'RowNumber' 
    from @temp

    return 

END

步骤3:试一下

declare @TVP TVP

INSERT INTO @TVP
select ID,UserId,[DateType],TypeID from Shays_table where TypeID=1 --AND any other date restriction you want to add 

select * from fnGetLoginFreq(@TVP) order by LoginTime asc

我的测试返回了:

ID  UserId  LoginTime               TypeID  RowNumber
2   2       2010-01-01 10:01:50.000 1       3
4   1       2010-01-01 10:03:50.000 1       1
5   1       2010-01-01 11:00:00.000 1       2
6   2       2010-01-01 11:00:50.000 1       4