• Marek Vavrovic

Window Functions


FIRST_VALUE

LAST_VALUE

Previous day (Difference between current date and previous date value)

LAG() (Calculating price difference between dates)


Main key words: Preceding, Following, Unbounded, Current


This is a script for an aggregated running total. We can use framing to further improve the result of the query.

Default behaviour of this query is to use range (RANGE UNBOUNDED PRECEDING) and the range operator is going to use temporally database. By executing this query like this we have 31K scans and 18K reads against that temp DB. We can force this to use memory instead of temp DB by adding framing.


Table 'Worktable'. Scan count 31467, logical reads 189937


This is a default behaviour of this query, using RANGE UNBOUNDED PRECIDING. This is equal to: SUM(totaldue) over (Order by SalesOrderID) and this force the query using temporally database .



ROWS UNBOUNDED PRECEDING says give me all of the rows whiting the current partition up until the current row. The performance is different now, because we forced this query into the memory by using ROWS UNBOUNDED PRECEDING clause.



When you are using RANGE UNBOUNDED PRECEDING you can get inaccurate result back. Range is a default behaviour of this query, that's why you can't see it in the code.

Using to calculate the price difference between the current day and the previous one can fix the problem with the incorrect calculation of the running total and force the query into the memory (no scans and reads on the temp DB). This is one of the ways how to fix the code.

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

The first row in this case is going to be the last row. This kind of notation turns the logic of the running total upside down.



ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

What this does is, it takes the current row, 1 previous value, 1 following value and sum them up. In order to do 1 PRECEDING ADN 1 FOLLOWING you must use ROWS option. You can use other aggregation function to achieve different results.


ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING


FIRST_VALUE

LAST_VALUE


I have some stock data and I want to retrieve the value on the beginning of the month and the value on the end of the month for each row that particular month.



SELECT [StockId]

,[QuoteId]

,[QuoteDay]

,[QuoteClose]

,FIRST_VALUE(QuoteClose) OVER(Partition by StockID, Month(QuoteDay),YEAR(QuoteDay) ORDER BY QuoteDay) FistClose

,LAST_VALUE(QuoteClose) OVER(Partition by StockID, Month(QuoteDay),YEAR(QuoteDay) ORDER BY QuoteDay

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastClose

FROM [TAdb].[dbo].[Quotes]


Previous Day Closing Price


I want to calculate the price difference between the current day and the previous one. I am using ROW_NUMBER() function and self join. The Stock is closed during the weekends so I do not have a continues set of dates which is a requirement when working with time intelligence functions.


With PreviousDay

as

(

Select ROW_NUMBER() OVER( ORDER BY StockID) [Index],

StockId, QuoteId,QuoteDay,QuoteClose AS _Close

from Quotes

)


Select pd1.QuoteDay, pd1._Close,pd2._Close AS PreviousDayClose,

pd1._Close - pd2._Close As PriceDifference

from PreviousDay pd1

LEFT JOIN PreviousDay pd2

On pd1.[Index] =pd2.[Index] +1


LAG()

Using LAG() function to calculate the price difference between the current day and the previous one

Select

StockId, QuoteId,QuoteDay,QuoteClose AS _Close,

LAG(QuoteClose) OVER(ORDER BY StockID) AS PrevClose,

QuoteClose - LAG(QuoteClose) OVER(ORDER BY StockID) AS PriceDifference

from Quotes



6 views0 comments

Recent Posts

See All