Finding the difference within a SQL Server query and dividing by seconds elapsed between records

Hi I have a table of meter values in a SQL Server database, which contains a table with the following colum开发者_如何学Pythonns:

Timestamp, meterID, rawValue

I am trying to graph the water usage rate using a query and google charts, the problem is that I need to calculate the rate from the raw meter values which are updated every 15 to 30 minutes.

I want to run a query that returns the values for a specific water meter.

MeterID, Timestamp, (rawValue-previousRawValue)/(timestamp difference in seconds)

any help is much appreciated.

Edit 1: I have modified index definition to eliminate LookUp operator => fewer logical reads.

Edit 2: I have added the second solution based on quirky update method. Please read this article (Solving the Running Total and Ordinal Rank Problems) written by Jeff Moden.

First solution can be tested with SQL Server 2005/2008:

--Create test table
CREATE TABLE dbo.MeterValues
(
    ID INT IDENTITY(1,1) PRIMARY KEY
    ,1680729079 DATETIME NOT NULL
    ,MeterID INT NOT NULL
    ,RawValue INT NOT NULL
);

CREATE UNIQUE INDEX IUN_MeterValues_MeterID_Timestamp
--SQL Server 2008
ON  dbo.MeterValues (MeterID, 1680729079)
INCLUDE (RawValue)
--SQL Server 2005
--ON  dbo.MeterValues (MeterID, 1680729079,RawValue)
--DROP INDEX dbo.MeterValues.IUN_MeterValues_MeterID_Timestamp

--Insert some values
INSERT  dbo.MeterValues (1680729079, MeterID, RawValue)
SELECT  '2011-01-01T00:00:00', 1, 100
UNION ALL
SELECT  '2011-01-01T00:00:15', 1, 105
UNION ALL
SELECT  '2011-01-01T00:00:30', 1, 102
UNION ALL
SELECT  '2011-01-01T00:00:45', 1, 108
UNION ALL
SELECT  '2011-01-01T00:01:00', 1, 109

UNION ALL
SELECT  '2011-01-01T00:00:00', 2, 1000
UNION ALL
SELECT  '2011-01-01T00:00:15', 2,  900
UNION ALL
SELECT  '2011-01-01T00:00:30', 2, 1105
UNION ALL
SELECT  '2011-01-01T00:00:45', 2, 1050
UNION ALL
SELECT  '2011-01-01T00:01:00', 2,  910;

--Check test data
SELECT  *
FROM    dbo.MeterValues mv
ORDER BY mv.MeterID, mv.ID DESC;

--Solution
WITH ValuesWithRowNumber
AS
(
    SELECT  mv.MeterID
            ,mv.RawValue
            ,mv.1680729079
            ,ROW_NUMBER() OVER(PARTITION BY mv.MeterID ORDER BY mv.1680729079 ASC) RowNum
    FROM    dbo.MeterValues mv
)
SELECT  crt.MeterID
        ,crt.1680729079 AS CrtTimestamp
        ,prev.1680729079 AS PrevTimestamp
        ,crt.RawValue AS CrtRawValue
        ,prev.RawValue AS PrevRawValue
        ,(crt.RawValue - prev.RawValue)*1.00/DATEDIFF(SECOND, prev.1680729079, crt.1680729079) Diff
        ,STR((crt.RawValue - prev.RawValue)*1.00/DATEDIFF(SECOND, prev.1680729079, crt.1680729079)*100, 10, 2)+'%' [Percent]
FROM    ValuesWithRowNumber crt --crt=current
LEFT JOIN ValuesWithRowNumber prev ON crt.MeterID = prev.MeterID --prev=previous
AND     crt.RowNum - 1 = prev.RowNum
ORDER BY crt.MeterID, crt.1680729079 DESC;

--By, by
DROP TABLE dbo.MeterValues;

Results:

MeterID     CrtTimestamp            PrevTimestamp           CrtRawValue PrevRawValue Diff                                    Percent
----------- ----------------------- ----------------------- ----------- ------------ --------------------------------------- -----------
1           2011-01-01 00:01:00.000 2011-01-01 00:00:45.000 109         108          0.0666666666666                               6.67%
1           2011-01-01 00:00:45.000 2011-01-01 00:00:30.000 108         102          0.4000000000000                              40.00%
1           2011-01-01 00:00:30.000 2011-01-01 00:00:15.000 102         105          -0.2000000000000                            -20.00%
1           2011-01-01 00:00:15.000 2011-01-01 00:00:00.000 105         100          0.3333333333333                              33.33%
1           2011-01-01 00:00:00.000 NULL                    100         NULL         NULL                                    NULL
2           2011-01-01 00:01:00.000 2011-01-01 00:00:45.000 910         1050         -9.3333333333333                           -933.33%
2           2011-01-01 00:00:45.000 2011-01-01 00:00:30.000 1050        1105         -3.6666666666666                           -366.67%
2           2011-01-01 00:00:30.000 2011-01-01 00:00:15.000 1105        900          13.6666666666666                           1366.67%
2           2011-01-01 00:00:15.000 2011-01-01 00:00:00.000 900         1000         -6.6666666666666                           -666.67%
2           2011-01-01 00:00:00.000 NULL                    1000        NULL         NULL                                    NULL

The second solution can/should work with SQL 2000/2005/2008 (please read "The RULES" section from Jeff Moden article):

--Create test table
CREATE TABLE dbo.MeterValues
(
    MeterID INT NOT NULL
    ,1680729079 DATETIME NOT NULL    
    ,RawValue INT NOT NULL
    ,Diff NUMERIC(10,3) NULL
    ,PRIMARY KEY CLUSTERED(MeterID,1680729079)
);

--Insert some values
INSERT  dbo.MeterValues (1680729079, MeterID, RawValue)
SELECT  '2011-01-01T00:00:00', 1, 100
UNION ALL
SELECT  '2011-01-01T00:00:15', 1, 105
UNION ALL
SELECT  '2011-01-01T00:00:30', 1, 102
UNION ALL
SELECT  '2011-01-01T00:00:45', 1, 108
UNION ALL
SELECT  '2011-01-01T00:01:00', 1, 109

UNION ALL
SELECT  '2011-01-01T00:00:00', 2, 1000
UNION ALL
SELECT  '2011-01-01T00:00:15', 2,  900
UNION ALL
SELECT  '2011-01-01T00:00:30', 2, 1105
UNION ALL
SELECT  '2011-01-01T00:00:45', 2, 1050
UNION ALL
SELECT  '2011-01-01T00:01:00', 2,  910;

--Check test data
SELECT  *
FROM    dbo.MeterValues mv
ORDER BY mv.MeterID, mv.1680729079;

DECLARE @OldRawValue INT
        ,@Diff NUMERIC(10,3)
        ,@OldMeterID INT
        ,@OldTimestamp DATETIME;

PRINT '*****Star*****'              
--Calculations
UPDATE  dbo.MeterValues WITH(TABLOCKX)
SET     @Diff = CASE WHEN @OldMeterID = MeterID THEN (RawValue - @OldRawValue)*1.00/DATEDIFF(SECOND,@OldTimeStamp,[TimeStamp]) END 
        ,Diff = @Diff
        ,@OldRawValue = RawValue
        ,@OldMeterID = MeterID
        ,@OldTimestamp = 1680729079        
OPTION(MAXDOP 1);

--Results
SELECT  *
FROM    dbo.MeterValues mv
ORDER BY mv.MeterID, mv.1680729079;
PRINT '*****Stop*****'

--By, by
DROP TABLE dbo.MeterValues;

Results:

MeterID     Timestamp               RawValue    Diff
----------- ----------------------- ----------- ---------------------------------------
1           2011-01-01 00:01:00.000 109         0.067
1           2011-01-01 00:00:45.000 108         0.400
1           2011-01-01 00:00:30.000 102         -0.200
1           2011-01-01 00:00:15.000 105         0.333
1           2011-01-01 00:00:00.000 100         NULL
2           2011-01-01 00:01:00.000 910         -9.333
2           2011-01-01 00:00:45.000 1050        -3.667
2           2011-01-01 00:00:30.000 1105        13.667
2           2011-01-01 00:00:15.000 900         -6.667
2           2011-01-01 00:00:00.000 1000        NULL

Try this

Select a.metered,a.timestamp,
   (a.rawValue-b.rawValue)/(a.timestamp-b.timestamp)

From meters A
Join (selec top 2 rawValue,Timestamp 
      From meters where metered = @meter
      order by timestamp DESC) b
On b.timestamp <> a.timestamp and a.meterId=B.meterId

Added a DESC to the timestamp in the middle query. This will cause the most recent two timestamps to be returned, and then the JOIN will "filter out" the one that matches the current row from A

I made a few minor changes to both my query and @Bogdan's query to make them as similar as possible, then compared them. Bogdan's modified query is at the bottom of this post.

Stacked together in the same query, according to the SQL Server Query Execution Plan, mine is 53% of the query cost, and Bogdan's is 47%.

For the data set offered in the Bogdan's post:

• My query: 6 scans and 27 logical reads
• Bogdan's: 6 scans and 72 logical reads

I added values every 15secs up to 5mins for both meterID 1 and 2, for a total of 42 records, then reran the queries with SQL Server Profiler.

Where my query wins on reads, Bogdan's still wins on CPU and Duration.

          CPU  SCANS  READS  DURATION
--------------------------------------
Mine       47     22    313     249ms
Bogdan's   16     22    972      15ms
--------------------------------------

I'm making a few assumptions, like that your MeterID is an INT. Change that as necessary.

I'm also assuming since you want to run the query for a specific meter ID, that it will be passed in as a parameter to a stored procedure.

This should work on SQL Server 2005 and later.

I do a few things that might distract from the actual solution. The core logic is really within the WHILE loop.

CREATE PROCEDURE [dbo].[GetMeterResults]
    @MeterID INT
AS
BEGIN

    -- create a temp table to store results
    CREATE TABLE #tempResults
    (
        MeterID INT,
        1680729079 DATETIME,
        Result FLOAT
    )

    DECLARE  
        @Timestamp DATETIME, 
        @RawValue INT,
        @LastTimestamp DATETIME, 
        @LastRawValue INT,
        @FirstRun BIT = 1

    DECLARE cr CURSOR FAST_FORWARD FOR 
    SELECT 
        1680729079, 
        RawValue 
    FROM 
        YourTable
    WHERE
        MeterID = @MeterID
    ORDER BY
        1680729079

    OPEN cr
    FETCH NEXT FROM cr INTO @Timestamp, @RawValue

    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF (@FirstRun = 1)
        BEGIN -- the first run
            SELECT @FirstRun = 0 -- flip the bit for all future runs
        END
        ELSE -- all subsequent runs after the first 
        BEGIN       
            INSERT INTO 
                #tempResults
            SELECT 
                @MeterID,
                @Timestamp, 
                (@RawValue - @LastRawValue) * 1.00 / DATEDIFF(s, @LastTimestamp, @Timestamp)
        END

            -- save the current values for comparison on the next run       
        SELECT  
            @LastTimestamp = @Timestamp, 
            @LastRawValue = @RawValue

        FETCH NEXT FROM cr INTO @Timestamp, @RawValue
    END

    CLOSE CR
    DEALLOCATE CR

    -- return the result set
    SELECT
        *
    FROM
        #tempResults

    -- clean up the temp table
    DROP TABLE #tempResults
END
GO

Bogdan's modified query that filters by MeterID for an apples-to-apples comparison with my query above:

DECLARE @MeterID INT = 1;

WITH ValuesWithRowNumber
AS
(
    SELECT  mv.MeterID
            ,mv.RawValue
            ,mv.1680729079
            ,ROW_NUMBER() OVER(PARTITION BY mv.MeterID ORDER BY mv.1680729079 ASC) RowNum
    FROM    dbo.MeterValues mv
    WHERE mv.MeterID = @MeterID
)
SELECT  crt.MeterID
        ,crt.1680729079 AS CrtTimestamp
        ,prev.1680729079 AS PrevTimestamp
        ,crt.RawValue AS CrtRawValue
        ,prev.RawValue AS PrevRawValue
        ,(crt.RawValue - prev.RawValue)*1.00/DATEDIFF(SECOND, prev.1680729079, crt.1680729079) Diff
FROM    ValuesWithRowNumber crt --crt=current
JOIN ValuesWithRowNumber prev ON crt.RowNum - 1 = prev.RowNum
ORDER BY crt.1680729079;