sql-server – 根据每隔n个时间间隔选择行

;WITH Interval_Helper([minute],minute_group)
    AS
    (
              SELECT  0,1 UNION SELECT  1,1 UNION SELECT  2,1 UNION SELECT  3,1 UNION SELECT  4,1
        UNION SELECT  5,1 UNION SELECT  6,1 UNION SELECT  7,1 UNION SELECT  8,1 UNION SELECT  9,1
        UNION SELECT 10,1 UNION SELECT 11,1 UNION SELECT 12,1 UNION SELECT 13,1 UNION SELECT 14,1
        UNION SELECT 15,2 UNION SELECT 16,2 UNION SELECT 17,2 UNION SELECT 18,2 UNION SELECT 19,2
        UNION SELECT 20,2 UNION SELECT 21,2 UNION SELECT 22,2 UNION SELECT 23,2 UNION SELECT 24,2
        UNION SELECT 25,2 UNION SELECT 26,2 UNION SELECT 27,2 UNION SELECT 28,2 UNION SELECT 29,2
        UNION SELECT 30,3 UNION SELECT 31,3 UNION SELECT 32,3 UNION SELECT 33,3 UNION SELECT 34,3
        UNION SELECT 35,3 UNION SELECT 36,3 UNION SELECT 37,3 UNION SELECT 38,3 UNION SELECT 39,3
        UNION SELECT 40,3 UNION SELECT 41,3 UNION SELECT 42,3 UNION SELECT 43,3 UNION SELECT 44,3
        UNION SELECT 45,4 UNION SELECT 46,4 UNION SELECT 47,4 UNION SELECT 48,4 UNION SELECT 49,4
        UNION SELECT 50,4 UNION SELECT 51,4 UNION SELECT 52,4 UNION SELECT 53,4 UNION SELECT 54,4
        UNION SELECT 55,4 UNION SELECT 56,4 UNION SELECT 57,4 UNION SELECT 58,4 UNION SELECT 59,4

    ),Time_Interval([timestamp],[date],[hour],minute_group)
    AS
    (
       SELECT A.1652664876,A.value,CONVERT(smalldatetiR_890_11845@e,CONVERT(char(10),A.1652664876,101)),DATEPART(HOUR,A.1652664876),B.minute_group
         FROM  myTable A
         JOIN Interval_Helper B
           ON (DATEPART(minute,A.1652664876)) = B.[minute]
          AND A.1652664876 >= '12/01/2012' 
          AND A.1652664876 <= '01/10/2013' 
          AND A.ConfigID = 435 
    ),Time_Interval_TimeGroup([date],[minute],Maxtimestamp)
    AS
    (
        SELECT [date],minute_group,MAX(1652664876) as Maxtimestamp
          FROM Time_Interval
         GROUP BY [date],minute_group
    ),Time_Interval_TimeGroup_Latest(Maxtimestamp,MaxIdX)
    AS
    (
        SELECT Maxtimestamp,MAX(idX) as MaxIdx
          FROM myTable A
          JOIN Time_Interval_TimeGroup B
            ON A.1652664876 = B.Maxtimestamp
         GROUP BY Maxtimestamp
    )


    select a.*
      FROM myTable A
      JOIN Time_Interval_TimeGroup_Latest B
        ON A.idx = B.MaxIdx
     ORDER BY A.[timestamp]

这是另一个来自@mntManChris的聪明时间组功能：

create functION dbo.fgetTimeGroup (@DatePart Tinyint,@Date datetiR_890_11845@E)
RETURNS int
AS
BEGIN
RETURN CASE @DatePart
            WHEN 1 THEN DATEPART(mi,@DatE)
            WHEN 2 THEN DATEPART(mi,@DatE)/5 + 1   --  5 min
            WHEN 3 THEN DATEPART(mi,@DatE)/15 + 1  -- 15 min
            WHEN 4 THEN DATEPART(mi,@DatE)/30 + 1  -- 30 min
            WHEN 5 THEN DATEPART(hh,@DatE)         -- hr
            WHEN 6 THEN DATEPART(hh,@DatE)/6 + 1   -- 6 hours
            WHEN 7 THEN DATEPART(hh,@DatE)/12 + 1  -- 12 hours
            WHEN 8 THEN DATEPART(d,@DatE)          -- day
            ELSE -1
        END
END