获取时间持续时间

Question

我有一张表，如下所示:我想得到每一个速度大于100的记录的持续时间。我为此编写了一个函数，它运行得很好，但我的问题是，我有数百万条记录--这些表--所以当我从存储过程调用这些函数时，需要花费太多的时间。

请你对此提出其他选择好吗？

这是我的表结构和数据

---------------------------------------
ID            SPEED          TRACKTIME
---------------------------------------
1              50        2014-10-01 00:20:00
2              80        2014-10-01 00:21:00
3              110       2014-10-01 00:21:30
4              105       2014-10-01 00:22:10
5              97        2014-10-01 00:23:00
6              65        2014-10-01 00:25:00
7              107       2014-10-01 00:26:00
8              101       2014-10-01 00:27:00
9              41        2014-10-01 00:29:00
10             70        2014-10-01 00:30:00
-------------------------------------

我的职能：

CREATE FUNCTION [dbo].[udf_GetOverspeedMinute]
(
    @vehicleID      INT,
    @starttime      DATETIME,
    @endtime        DATETIME,
    @speedlimit     DECIMAL(18, 2)
)
RETURNS @tmp_overspeed TABLE(ID INT IDENTITY(1, 1), TimeInMinute INT)
AS
BEGIN
    DECLARE @tmp_tracking TABLE (
                ID INT IDENTITY(1, 1) PRIMARY KEY,
                TrackingID INT,
                Speed DECIMAL(18, 2),
                TrackTime DATETIME
            )

    INSERT INTO @tmp_tracking
      (
        TrackingID,
        Speed,
        TrackTime
      )
    SELECT Tracking.ID,
           Speed,
           TrackTime
    FROM   Tracking WITH(NOLOCK)
    WHERE  VehicleID = @vehicleID
           AND TrackTime BETWEEN @starttime AND @endtime

    ;WITH cte_speed AS (
        SELECT RN = ROW_NUMBER() OVER(ORDER BY TrackTime),
               *
        FROM   @tmp_tracking
    )    

    INSERT INTO @tmp_overspeed
      (
        TimeInMinute
      )
    SELECT SUM(
               DATEDIFF(MINUTE, [Current Row].TrackTime, [Next Row].TrackTime)
           )
    FROM   cte_speed [Current Row]
           LEFT JOIN cte_speed [Next Row]
                ON  [Next Row].RN = [Current Row].RN + 1
    WHERE  [Current Row].Speed > @speedlimit

    RETURN
END

Answer 1

您没有说明您使用的是什么DB，但是这种语法看起来像。

此外，您没有正确地声明表结构--该函数引用的是VehicleID，它没有出现在示例数据中，也没有说明数据类型或存在哪些索引和键。所有这些都可能意义重大。

如果数据被拉到一个表变量中并在那里进行处理，这可能是一个问题--如果每辆车有很多行，这会击中磁盘，并由于IO而导致速度慢。

对于Server 2012或更高版本，使用窗口函数很容易做到这一点：

SELECT ID, DATEDIFF(MINUTE
            , TrackTime
            , LEAD(TimeTrack) OVER (ORDER BY TimeTrack)
            )
FROM Tracking
WHERE VehicleID = @VehicleID
AND TrackTime BETWEEN @starttime AND @endtime
AND Speed > @speedlimit
ORDER BY TimeTrack

为了提高效率，您至少需要一个覆盖VehicleID的索引。更好的是，要么覆盖VehicleID & TrackTime (可能包括速度和ID)，要么让VehicleID & TrackTime作为您的聚集索引(但是对于明确的建议，我们需要更多地了解表看到的其他查询:选择聚集索引来优化这个查询可能会显示其他查询)。

我希望您的索引是正确的--这应该只传递一次数据，使用和索引查找，而不是扫描(或者更糟糕的是，表扫描)，并且除非每辆车的数据量非常大，否则不会将数据假脱机到磁盘。

如果您希望获得多个车辆的结果，那么需要告诉lead函数按VehicleID进行拆分，使其成为LEAD(TimeTrack) OVER (PARTITION BY VehicleID ORDER BY TimeTrack)，并将VehicleID添加到main ordering子句中。