使用自联接T获取超过1小时的数据

Question

我有张这样的桌子(claimsTable)，

 Time              Terminal_ID  Claims data_from
------------------------------------------------

2017-10-19 06:03:00     1        561     2
2017-10-19 06:04:00     1        562     2 
2017-10-19 06:05:00     1        562.3   2
2017-10-19 06:06:00     1        563     2
2017-10-19 06:03:00     9        471     2
2017-10-19 06:04:00     9        471.9   2
2017-10-19 06:05:00     9        472.3   2
2017-10-19 06:06:00     9        473     2
2017-10-19 06:07:00     1        567     1
2017-10-19 06:08:00     1        567.6   1 
2017-10-19 06:09:00     1        568.2   1
2017-10-19 06:10:00     1        569     1
2017-10-19 06:07:00     9        475     1
2017-10-19 06:08:00     9        475.9   1
2017-10-19 06:09:00     9        476.3   1
2017-10-19 06:10:00     9        476.3   1

对于每个ID，都有价值天的数据。我只展示了上面的一些数据。现在，我检查来自data_from =1的每个data_from的最老数据，如下所示

select min(Time), Terminal_ID
from claimsTable 
where data_from = 1 
group by Terminal_ID

我得到每个ID的2017-10-19 06:07:00。

接下来，我检查来自data_from =2的每个data_from的最新数据，如下所示，

select max(Time), Terminal_ID 
from claimsTable 
where data_from = 2
group by Terminal_ID

现在，我得到了每个2017-10-19 06:06:00的Terminal_ID。

现在，我想从最新的时间( data_from =2的最大值(时间))中得到60分钟的数据，以从data_from = 1的最老数据中计算每小时的avg。

所以，我做了这样的自我连接检查，

select 
      t1.[Time], t1. Terminal_ID
from 
    claimsTable t1
inner join 
    claimsTable t2
    on t1.Terminal_ID = t2. Terminal_ID
where t1. Terminal_ID = t2. Terminal_ID and 
      t1.[Time] between dateadd(mi,-59,t2.[Time]) and t1.[Time]

这没有给出所需的检查，因为我没有在t2中使用t2函数，在t1中使用min函数。当我做一个自我连接时，我不知道如何将它们包括在内。

我的预期输出表：

Time              Terminal_ID  Claims data_from
------------------------------------------------
2017-10-19 06:03:00     1        561     2
2017-10-19 06:04:00     1        562     2 
2017-10-19 06:05:00     1        562.3   2
2017-10-19 06:06:00     1        563     2
2017-10-19 06:07:00     1        567     1
2017-10-19 06:03:00     9        471     2
2017-10-19 06:04:00     9        471.9   2
2017-10-19 06:05:00     9        472.3   2
2017-10-19 06:06:00     9        473     2
2017-10-19 06:07:00     9        475     1

如何检查从data_from= 2开始最老的数据，即data_from = 1的前60分钟的数据？

Answer 1

我已经尝试过计算Avg在mintime(data_from = 1 )和(Max - 60 mins AND data_from == 2)之间的所有记录。

问题是，我在这两种情况下都得到了NULL，这可能是因为查询中缺少了什么-或者数据不够。

如果有任何问题请告诉我，请试着运行查询。

这是创建示例数据的查询和查询：

    -- CREATE SAMPLE DATA
    DROP TABLE #claimsTable

    CREATE TABLE #claimsTable
    (
        [Time] DateTime,
        Terminal_ID INT,
        Claims FLOAT,
        data_from INT

    )

    INSERT INTO #claimsTable
    VALUES

        (N'2017-10-19 06:03:00',     1 ,       561  ,   2),
        (N'2017-10-19 06:04:00',     1 ,       562  ,   2), 
        (N'2017-10-19 06:05:00',     1 ,       562.3,   2),
        (N'2017-10-19 06:06:00',     1 ,       563  ,   2),
        (N'2017-10-19 06:03:00',     9 ,       471  ,   2),
        (N'2017-10-19 06:04:00',     9 ,       471.9,   2),
        (N'2017-10-19 06:05:00',     9 ,       472.3,   2),
        (N'2017-10-19 06:09:00',     9 ,       473  ,   2),
        (N'2017-10-19 06:07:00',     1 ,       567  ,   1),
        (N'2017-10-19 06:08:00',     1 ,       567.6,   1), 
        (N'2017-10-19 06:09:00',     1 ,       568.2,   1),
        (N'2017-10-19 06:10:00',     1 ,       569  ,   1),
        (N'2017-10-19 06:05:00',     9 ,       475  ,   1),
        (N'2017-10-19 06:08:00',     9 ,       475.9,   1),
        (N'2017-10-19 06:09:00',     9 ,       476.3,   1)

我在示例数据中更改1或2次，以更好地理解我的查询所生成的结果。

实际的查询从这里开始：

    Select
        A.TerminalId,
        Avrg = AVG(data_between.Claims)
    From
        (
        -- this inner query returns
        /*
            TerminalId  |           MaxTime (data_from == 2)    |   Min Time (data_from == 1)
            -------------------------------------------------------------------------------------
            9           |   2017-10-19 06:09:00.000             |   2017-10-19 06:05:00.000
            1           |   2017-10-19 06:06:00.000             |   2017-10-19 06:07:00.000
        */

            Select
                TerminalId = data_from_2.Terminal_ID,
                MaxTime2 = MAX(data_from_2.[Time]),
                MinTime1 = data_from_1.[Time]

            From
                #claimsTable data_from_2
                -- This will get MIN the data_from = 1 for each terminal_id
                CROSS APPLY (
                    SELECT TOP (1) 
                        * 
                    FROM #claimsTable  a

                    WHERE a.data_from = 1 AND a.Terminal_ID = data_from_2.Terminal_ID

                    ORDER BY a.[Time] ASC

                ) data_from_1
            --
            Where data_from_2.data_from = 2
            -- group by to get the Max.Time for each terminal
            GROUP BY data_from_2.Terminal_ID, data_from_1.[Time]
        ) A

        -- join with claimsTable again to get the data between mintime(data_from = 1 ) and (Max - 60 mins) so we can calculate avg

        LEFT JOIN #claimsTable data_between on data_between.Terminal_ID = A.TerminalId AND data_between.[Time] BETWEEN A.MinTime1 AND DATEADD(MINUTE, -60, A.MaxTime2)
        --
        GROUP BY A.TerminalId

Answer 2

只需在自联接的time列上使用max函数即可。

select 
      max(t2.[Time]), 
      t1.Terminal_ID
from 
    claimsTable t1
full join 
    claimsTable t2
    on t1.Terminal_ID = t2. Terminal_ID
    and t2.time <= dateadd(mi,-59,t1.[Time]) 
group by
    t1. Terminal_ID