Windowing Functions In Sql Server

Q.1. What is Windowing? Explain The Usage of The Over Clause.

Ans: Windowing refers to the concept of creating a "window" or a view over a result set where each row is related to its neighboring rows based on specified criteria. The window allows us to perform calculations or aggregate functions over a subset of rows within the result set, rather than considering the entire result set as a whole.The Over Clause is used in conjunction with window functions and provides a way to define the window's boundaries. It enables us to partition the result set into groups based on specific columns using the PARTITION BY clause. Each partition will have its own separate window. The ORDER BY clause inside the OVER clause determines the order of rows within each partition.

Window functions can then be applied to the rows within each window, and they can perform calculations on the values in the window to generate new result set columns.

Using the Over Clause, we can perform various tasks, including:

• Calculate aggregations (SUM, COUNT, MIN, MAX, AVG) over a window.
• Rank rows based on specific criteria (RANK, DENSE_RANK, ROW_NUMBER).
• Compute moving or running aggregations within each window.
• Frame the window to support sliding calculations using ROWS or RANGE clauses.

Q.2. How to Run Aggregations With Each Window Using The Order By clause?

Ans: To perform running aggregations within each window using the ORDER BY clause, we can use the Over Clause with SQL Server 2012 or later versions. For example:

SELECT AcctId, TxnDate, Amount,
SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate) AS RAvg,
COUNT(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate) AS RCnt,
MIN(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate) AS RMin,
MAX(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate) AS RMax,
SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate) AS RSum
FROM Transactions

In this example, we are calculating running aggregations (RAvg, RCnt, RMin, RMax, RSum) for each account (partitioned by AcctId) based on the transaction date (ordered by TxnDate).

Q.3. How to Produce Sliding Aggregations? Explain With The Help of an Example.

Ans: To produce sliding aggregations, we can use the ROWS or RANGE clause in the OVER clause to control the window size. This allows us to perform calculations on a specified number of preceding or following rows within each window. For example:

SELECT AcctId, TxnDate, Amount,
AVG(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SAvg,
COUNT(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SCnt,
MIN(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SMin,
MAX(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SMax,
SUM(Amount) OVER (PARTITION BY AcctId ORDER BY TxnDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS SSum
FROM Transactions

In this example, we are calculating sliding aggregations (SAvg, SCnt, SMin, SMax, SSum) for each account (partitioned by AcctId) based on the transaction date, considering the current row and the two preceding rows.

Q.4. Compare The Usage of RANGE and ROWS for Calculating Window Functions?

Ans: RANGE and ROWS are used in the OVER clause to define the window frame and specify how rows are included in the window for calculating window functions.

• ROWS: With ROWS, you explicitly define a fixed number of rows relative to the current row to include in the window frame. For example, "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW" includes the current row and the two preceding rows.
• RANGE: With RANGE, you define a window frame that includes rows based on their values and not their physical positions. It's used with ordered data, and the window frame is determined by the values specified in the ORDER BY clause. For example, "RANGE BETWEEN 10 PRECEDING AND 10 FOLLOWING" includes rows whose values are within 10 units (according to the ORDER BY) of the current row's value.

When using RANGE, rows with the same values as the current row are included in the window, even if they are not adjacent in the result set. This means that RANGE can merge rows with the same values within the defined range, while ROWS considers each row individually based on its physical position.

Q.5. What are The New Conversion Functions Available in Sql Server 2012?

Ans: SQL Server 2012 introduced several new conversion functions:

• TRY_CONVERT: It is a "safe" replacement for the CONVERT function. It attempts to convert the input value to the specified data type and returns NULL if the conversion fails instead of throwing an error.
• PARSE: This function allows parsing and converting various data formats and culturally sensitive strings. It can understand different date, time, and currency formats based on the provided culture.
• TRY_PARSE: Similar to TRY_CONVERT, this function attempts to parse and convert the input value but returns NULL if the parsing fails, rather than raising an error.

These functions provide more flexibility and safety when dealing with data conversions in SQL Server.

Q.6. How do The Functions DATEFROMPARTS, TIMEFROMPARTS, DATETIME2FROMPARTS, DATETIMEOFFSETFROMPARTS, DATETIMEFROMPARTS, SMALLDATETIMEFROMPARTS Work?

Ans: These functions allow you to construct date and time values explicitly using their individual components. Here's how they work:

• DATEFROMPARTS: Takes three arguments (year, month, day) and returns a date value with the specified year, month, and day.
• TIMEFROMPARTS: Takes five arguments (hour, minute, seconds, milliseconds, and precision) and returns a time value with the specified components.
• DATETIME2FROMPARTS: Similar to TIMEFROMPARTS but constructs a datetime2 value with the specified year, month, day, hour, minute, seconds, milliseconds, and precision.
• DATETIMEOFFSETFROMPARTS: Similar to DATETIME2FROMPARTS, but includes a time zone offset.
• DATETIMEFROMPARTS: Constructs a datetime value with the specified year, month, day, hour, minute, and seconds.
• SMALLDATETIMEFROMPARTS: Constructs a smalldatetime value with the specified year, month, day, hour, and minute.

These functions provide a straightforward way to create date and time values without worrying about formatting issues or cultural differences.

Q.7. How Does The EOMONTH Function Works?

Ans: The EOMONTH function returns the last day of the month for a given date. It takes one or two arguments: the input date and an optional offset (the number of months to add or subtract from the input date). If the offset is not provided, EOMONTH returns the last day of the input date's month.

For Example:

SELECT EOMONTH('2023-07-10') AS LastDayOfMonth -- Output: 2023-07-31
SELECT EOMONTH('2023-07-10', 1) AS LastDayOfNextMonth -- Output: 2023-08-31
SELECT EOMONTH('2023-07-10', -1) AS LastDayOfPreviousMonth -- Output: 2023-06-30

Q.8. How Can You Use The Function Choose?

Ans: The CHOOSE function allows you to select a value from a list of options based on the provided index. It takes an integer as its first argument, which represents the index of the value to choose from the subsequent list of values. If the index is outside the range of the provided values, CHOOSE returns NULL.

For Example:

DECLARE @CardTypeId INT = 2 -- Master card
SELECT CHOOSE(@CardTypeId, 'Amex', 'Master', 'Visa', 'Discover') AS CardType

In this example, the @CardTypeId is set to 2, so the CHOOSE function returns the value 'Master', which is the second value in the list.

Q.9. How Can You Use The IIF Function?

Ans: The IIF function is used to perform a simple conditional check and return one of two values based on the result of the condition. It takes three arguments: a Boolean expression to evaluate, the value to return if the condition is true, and the value to return if the condition is false.

For Example:

DECLARE @Num1 INT = 45
DECLARE @Num2 INT = 40
SELECT IIF(@Num1 > @Num2, 'larger', 'not larger') AS Result

In this example, if @Num1 is greater than @Num2, the IIF function returns 'larger', otherwise, it returns 'not larger'.

Q.10. How to Use The CONCAT Function?

Ans: The CONCAT function is used to concatenate strings in SQL Server. It takes multiple string arguments and returns the concatenated result. Unlike the concatenation operator (+), CONCAT handles NULL values and returns the non-NULL values concatenated together.

For Example:

SELECT CONCAT('Happy', ' Birthday ', 8, '/', NULL, '30') AS Greeting

In this example, the CONCAT function concatenates the strings 'Happy', ' Birthday ', the integer 8, a NULL value, and the string '30' into the result 'Happy Birthday 8/30'.

Q.11. How Can You Use The FORMAT Function?

Ans: The FORMAT function is used to format date, time, and currency values in SQL Server. It provides a wide range of formatting options, and you can specify the desired format using format codes. The function takes the value to be formatted as the first parameter and the format code as the second parameter. An optional third parameter can be used to specify the culture for formatting.

For Example:

DECLARE @d DATETIME2 = '2011-02-01 16:05:00.0000007'
SELECT FORMAT(@d, 'ddd M/d/yyyy h:mm tt') AS DateAndTime

The FORMAT function formats the datetime value @d as 'Tue 2/1/2011 4:05 PM'. The format code 'ddd M/d/yyyy h:mm tt' specifies the day of the week (ddd), the month (M), the day of the month (d), the year (yyyy), the hour (h), the minute (mm), and the AM/PM designator (tt).

Q.12. How LOG Function is Used in SQL SERVER 2012?

Ans: The LOG function in SQL Server is used to calculate the natural logarithm of a given number. Before SQL Server 2012, the LOG function could only calculate the natural logarithm or the base-10 logarithm using the LOG10 function.In SQL Server 2012 and later versions, the LOG function was enhanced to allow specifying the desired base for the logarithm. It now takes two arguments: the number to calculate the logarithm for and the base of the logarithm. For example:

SELECT LOG(11, 2) AS Base2LogOf11

In this example, we calculate the base-2 logarithm of 11, and the result will be approximately 3.4594.

Conclusion

This blog post has equipped you with the knowledge and skills to elevate your SQL expertise. Windowing, Over Clause, advanced functions – they're no longer elusive concepts. With these tools at your disposal, you can navigate complex data scenarios, perform intricate calculations, and present results with finesse. Whether you're an SQL novice or a seasoned pro, mastering these techniques empowers you to tackle data challenges head-on and derive meaningful insights.