SQL Server 2012 - Sliding Functions Using OVER
Let's say I have the following schema:
-- Create the dbo.Transaction table
CREATE TABLE [dbo].[Transaction] (
[TransactionId] INT NOT NULL IDENTITY,
[AccountId] INT NOT NULL,
[TransactionDate] DateTime2(7) NOT NULL,
[Amount] decimal(9,3) NOT NULL
CONSTRAINT [PK_Transaction] PRIMARY KEY ([TransactionId])
);
And the next request:
Select
AccountId,
TransactionDate,
Amount,
AverageAmount = Avg(Amount) Over (Partition By AccountId Order By TransactionDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
TransactionCount = Count(Amount) Over (Partition By AccountId Order By TransactionDate ROWS 2 PRECEDING),
MinimumAmount = Min(Amount) Over (Partition By AccountId Order By TransactionDate ROWS 2 PRECEDING),
MaximumAmount = Max(Amount) Over (Partition By AccountId Order By TransactionDate ROWS 2 PRECEDING),
SumAmount = Sum(Amount) Over (Partition By AccountId Order By TransactionDate ROWS 2 PRECEDING)
From dbo.[Transaction]
Order By AccountId, TransactionDate
How do I execute this request if it was contained in a UDF or stored proc and the sliding interval (2 in this example) was not known until runtime passed as a parameter to the UDF / stored proc? It seems SQL 2012 does not allow using a variable here.
+3
source to share
1 answer
As you mentioned, SQL Server only supports integer literals for PRECEDING and FOLLOWING clauses in OVER.
There are two options available: dynamic sql and rewriting the query to avoid using PRECEDING
Dynamic sql is the simplest, but I'll be careful about putting it in a UDF.
set @sql = N'Select AccountId, ... ROWS '
+ cast(@sz as varchar(10)) + N' PRECEDING) ...'
exec sp_executesql @sql
However, window functions are just fancy syntax. You can rewrite the query without them:
DECLARE @sz INT
SET @sz = 2
;
WITH q AS ( SELECT AccountId ,
TransactionDate ,
Amount ,
ROW_NUMBER() OVER ( PARTITION BY AccountId
ORDER BY TransactionDate ) rw
FROM [Transaction]
)
SELECT accountID ,
TransactionDate ,
Amount ,
( SELECT AVG(q1.Amount) FROM q q1
WHERE q1.accountid = q.accountid
AND q1.rw BETWEEN q.rw - @sz AND q.rw
) AverageAmount,
( SELECT COUNT(q1.Amount) FROM q q1
WHERE q1.accountid = q.accountid
AND q1.rw BETWEEN q.rw - @sz AND q.rw
) TransactionAmount
-- etc.
FROM q
ORDER BY AccountID, TransactionDate
Here's another way to re-write the request:
DECLARE @sz INT
SET @sz = 2;
WITH q AS ( SELECT AccountId ,
TransactionDate ,
Amount ,
ROW_NUMBER() OVER ( PARTITION BY AccountId
ORDER BY TransactionDate ) rw
FROM [Transaction]
)
SELECT q.accountID ,
q.TransactionDate ,
q.Amount ,
AVG(q1.Amount) AverageAmount ,
COUNT(q1.Amount) TransactionAmount ,
MAX(q1.Amount) MaxAmount ,
MIN(q1.Amount) MinAmount
-- etc.
FROM q
INNER JOIN q q1 ON q1.accountid = q.accountid
AND q1.rw BETWEEN q.rw - @sz AND q.rw
GROUP BY q.accountid ,
q.transactiondate ,
q.amount
+6
source to share