Home

Analyzing the Distribution of Records

     

The First and Last Values of a Selected Field

   

The First Value of a Selected Field

Analyzing the Distribution of Records

We have already seen how to create groups (partitions) of records. One of the ways you can analyze your records is to get some statistics about each group. Transact-SQL provides a series of functions, named analytic functions that allow you to get the variations and tendencies of records within a group.

Inside of each group of records, you may want to know what value comes first based on a column of your choice. To get this information, you can call the FIRST_VALUE() function. Its syntax is:

FIRST_VALUE ( [scalar_expression ) 
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
    RETURNS Data Type of scalar_expression

The FIRST_VALUE() function takes as argument one the columns of the table or view on which data selection is made. The function is followed by a call to OVER() that requires arranging the records.

Practical LearningPractical Learning: Getting the First Value of a Selected Field

  1. Start Microsoft SQL Server and connect
  2. Right-click the name of the server and click New Query
  3. Right-click the name of the server and click Start PowerShell
  4. Type SQLCMD and press Enter
  5. To see the list of properties from the LambdaPropertiesManagement1 database (the database was created in the previous lesson), :
  6. If you use only one column for your SQL statement, if you pass it both to the  FIRST_VALUE() function and to OVER(), the statement would produce the lowest value of the column passed to the FIRST_VALUE() function. To see an example, type the following code and press Enter after each line (if your PowerShell is not working, type the code in the Query Editor and press F5 to execute):
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT FIRST_VALUE(props.MonthlyRate) OVER(ORDER BY props.MonthlyRate) [First Monthly Rate]
    FROM Listing.Properties props;
    GO
    Getting the First Value of a Selected Field

    The result is an instance of each record but with the lowest monthly rate of all our properties, which is 740

  7. Switch to the Query Editor in the Microsoft SQL Server Management Studio
  8. You can pass a different value to the   FIRST_VALUE() function and to OVER(). To see an example, type the following statement:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType [Property Type],
           props.MonthlyRate Rate,
           props.City,
           FIRST_VALUE(props.MonthlyRate) OVER(ORDER BY props.PropertyType) [First Monthly Rate in Selected Property Type]
    FROM Listing.Properties props;
    GO
  9. To execute, on the main menu, click Query -> Execute. Notice that the result consists of the first value of the monthly rate (passed to the function) in conjunction with the property type (passed to OVER() and the value is the same for all records:
     

    Getting the First Value of a Selected Field

  10. To create different groups of records, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType [Property Type],
           props.MonthlyRate Rate,
           props.City,
           FIRST_VALUE(props.MonthlyRate) OVER(PARTITION BY props.PropertyType ORDER BY props.PropertyType) [First Monthly Rate in Selected Property Type]
    FROM Listing.Properties props;
    GO
  11. Press F5 to execute. This time, groups (partitions) are created based on the column applied to PARTITION BY. The value produced by the FIRST_VALUE() function is the first value of the column passed to that function:
     
    Getting the First Value of a Selected Field Getting the First Value of a Selected Field

The Last Value of a Selected Field

As opposed to the first value of a group of records, you may want to get the last one. To do this, you can call the LAST_VALUE() function. Its syntax is:

LAST_VALUE ( [scalar_expression ) 
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )
    RETURNS Data Type of scalar_expression

The LAST_VALUE() function takes one the columns of the table or view as argument. The function follows the same logic as FIRST_VALUE(), in reverse.

Practical LearningPractical Learning: Getting the First Value of a Selected Field

  1. Switch to the PowerShell window.
    To call the LAST_VALUE() function, type the following code and press Enter after each line:
    SELECT LAST_VALUE(props.MonthlyRate) OVER(ORDER BY props.MonthlyRate) [Last Monthly Rate]
    FROM Listing.Properties props;
    GO
    Getting the Last Value of a Selected Field
     
    Notice the difference with the values from the FIRST_VALUE() function
  2. Switch to the Microsoft SQL Server Management Studio and change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType [Property Type],
           props.MonthlyRate Rate,
           props.City,
           LAST_VALUE(props.MonthlyRate) OVER(ORDER BY props.PropertyType) [Last Monthly Rate in Selected Property Type]
    FROM Listing.Properties props;
    GO
  3. To execute, on the main menu, click Query -> Execute. Notice that the result consists of the first value of the monthly rate (passed to the function) in conjunction with the property type (passed to OVER() and the value is the same for all records:
     

    Getting the Last Value of a Selected Field

  4. To create different groups of records, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType [Property Type],
           props.MonthlyRate Rate,
           props.City,
           LAST_VALUE(props.MonthlyRate) OVER(PARTITION BY props.PropertyType ORDER BY props.PropertyType) [Last Monthly Rate in Selected Property Type]
    FROM Listing.Properties props;
    GO
  5. Press F5 to execute. This time, groups (partitions) are created based on the column applied to PARTITION BY. The value produced by the FIRST_VALUE() function is the first value of the column passed to that function:
     
    Getting the Last Value of a Selected Field
     
    Getting the Last Value of a Selected Field
  6. Click inside the Query Editor, press Ctrl + A, and press Delete

The Distribution of Records

 

Introduction

A percentage is a fractional number from a 100-scale. The fractions go from 1 to 100 and each number is represented by following it with the % symbol. Examples are 1%, 2%, 3%, 4%, 5%, and so on. Imagine you are in charge of managing rent for 100 houses or properties, or you are teaching a course to 100 students. Based on these examples, each house (or student) represents 1% of the total number of houses (or students). 2 houses (or students) represent 2% of the total, and so on.

A fraction is a section of a whole thing. For example, you can cut a cantaloupe in two parts or fractions. Each fraction is half the whole:

Percentile of a Continuous Distributrion

A whole can have many fractions. For our example of managing rent for 100 houses or teaching a course to 100 students, for management purposes you can split the houses (or students) in half. Each half would be a fraction and would have 50 houses (or students). You can number the houses (or students) in the first group from 1 to 50 and the houses (or student) in the second group from 51 to 100. In the same way, you can divide the number of houses (or students) in 4 groups. Each group would contain 25 houses (or students). You can number the first group from 1 to 25, the second group from 26 to 50, and so on.

For some statistical reasons, you may want to take some action for the house or the student in the 25th position, which is the last one in the 25% range. Or imagine you want to take some action for the house or the student in the 50th position, which is the last in the 50% range.

A percentile is a positional (position or location) value that corresponds to the percentage value of a whole. Actually, to get the percentiles, the whole must be divided in equal parts and each part can be given a name. If the whole is divided in 4 parts, each part is called a quartile:

  • The first part is called the first quartile. Its internal positions go from the 1st quartile to the 25th quartile, which include the 2nd percentile, the third percentile, and so on
  • The second part is called the second quartile. Its internal positions go from the 26th quartile to the 50th quartile
  • The third part is called the third quartile. Its internal positions go from the 51st quartile to the 75th quartile
  • The fourth part is called the fourth quartile. Its internal positions go from the 76th quartile to the 100th quartile
The Distribution of Records

If the whole is divided in two, the divider is called a median, and is made of two quartiles (the first quartile and the second quartile).

Transact-SQL provides a series of functions used to get percentile-related statistics.

The Percentage Rank

When you have a group of values, each occupies a certain position. If the list is arranged (ordered), each value holds an incrementing position as 1, 2, 3, and so on. This position is also referred to as the rank. For statistical purposes, that rank can be treated as a weight (or importance). To give the same weight or the same importance to each value, the position, which is the value of the rank, is divided by the total number of values. This produces a fraction that can be, or is, converted to a percentage value. To let you get this information, Transact-SQL provides a function named PERCENT_RANK. Its syntax is:

PERCENT_RANK() OVER( [ partition_by_clause ] order_by_clause )
    RETURNS float(53)

The PERCENT_RANK() function takes no argument. The function is followed by a call to OVER() that requires arranging the records.

Practical LearningPractical Learning: Getting the Percentage Distribution of Records

  1. Switch to the PowerShell window.
    To get the positional distribution of the records, type the following code and press Enter after each line:
    SELECT props.PropertyNumber [Property #],
           props.PropertyType Type,
           props.MonthlyRate Rate,
           PERCENT_RANK() OVER(ORDER BY props.MonthlyRate) "Rank Distribution"
    FROM Listing.Properties props;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that values produced by the PERCENT_RANK() function range from 0 (the lowest) to 1:
     
    Getting the Percentage Distribution of Records
     
    Getting the Percentage Distribution of Records

  3. Notice that the values between the extremes are decimal fractions between 0 and 1. Also notice that the first plus the last values equal 1, the second plus the before-last value = 1, the third value from the beginning plus the third value back from the last = 1, and so on.
    Notice that the distribution of fractions is for all records. To show the distribution for each type of property, type the following code and press Enter after each line (always remember that you can press the up arrow key to locate a line):
    SELECT props.PropertyNumber [Property #],
           props.PropertyType Type,
           props.MonthlyRate Rate,
           PERCENT_RANK() OVER(PARTITION BY props.PropertyType ORDER BY props.MonthlyRate) "Rank Distribution"
    FROM Listing.Properties props;
    GO
    Getting the Percentage Distribution of Records
     
    Getting the Percentage Distribution of Records
  4. Switch to the Query Editor in Microsoft SQL Server Management Studio.
    To display the values in percent, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyNumber [Property #],
           props.PropertyType Type,
           props.MonthlyRate Rate,
           FORMAT(PERCENT_RANK() OVER(ORDER BY props.MonthlyRate), N'P') "Rank Distribution"
    FROM Listing.Properties props;
    GO
  5. To execute, on the main menu, click Query -> Execute.:
     
    Getting the Percentage Distribution of Records Getting the Percentage Distribution of Records
  6. To specify a partition, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyNumber [Property #],
           props.PropertyType Type,
           props.MonthlyRate Rate,
           FORMAT(PERCENT_RANK() OVER(PARTITION BY props.PropertyType ORDER BY props.MonthlyRate), N'P') "Rank Distribution"
    FROM Listing.Properties props;
    GO
  7. To execute, press F5
     
    Getting the Percentage Distribution of Records Getting the Percentage Distribution of Records

    Getting the Percentage Distribution of Records
     
  8. Click inside the Query Editor, press Ctrl + A, and press Delete

The Discrete Percentile of a Distribution

If a table is not empty, one of its obvious characteristics is that the table has values that have been created (the values exist and are not hidden) and each value occupies a specific position (the values of a table of a database are not truly inserted; each value is added to the end of the existing list, which is referred to as appending a value to the list). The fact that the values exist and are known, we say that they are spread or distributed. By definition, data or record distribution is the characteristic that the values of a table are available (to be accessed and used).

Discrete distribution is the characteristic that:

  • The values of a table are known and available, which means they are distributed
  • The number of values at a certain time is known and constant. This means that the number of values can be counted. We also say that the number is finite

A percentile discrete distribution is a value that represents the percentage position (the percentile) of a value of a column of a table. To let you calculate the percentile discrete distribution, Transact-SQL provides a function named PERCENTILE_DISC (the DISC stands for discrete). Its syntax is:

Percentile of a Continuous Distributrion
PERCENTILE_DISC ( numeric_literal ) 
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )
    RETURNS Data Type of order_by_expression;

The PERCENTILE_DISC() function takes one argument and requires some conditions. The value passed to the function must be a decimal value between 0.00 and 1.00. The value represents the fraction by which the percentile will be calculated. We will see how that number influences the result. After calling the function, you must add a WITHIN GROUP clause that resembles a function. In the parentheses of that clause, you arrange the values of a column of your choice. Then, you must call OVER(). If you don't pass a parameter to it, the result is the first value of the column passed to WITHIN GROUP(). Otherwise, you can create partitions over but you must not create another arrangement of records OVER().

Practical LearningPractical Learning: Getting the Discrete Percentile

  1. To get a percentile of a continuous distribution of the values of a column, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER() "Mohnthly Rate of First City Selected"
    FROM Listing.Properties props;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that the function produces the first value of the column passed to WITHIN GROUP:
     

    Getting the Discrete Percentile

  3. To create partitions, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.PropertyType) "Mohnthly Rate of First City Selected"
    FROM Listing.Properties props;
    GO
  4. To execute, on the main menu, click Query -> Execute
     
    Getting the Discrete Percentile
    Getting the Discrete Percentile Getting the Discrete Percentile
  5. To change the rate of the percentile distribution, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0.25) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.PropertyType) "First City Within Group"
    FROM Listing.Properties props;
    GO
  6. To execute, press F5:
     
    Getting the Discrete Percentile
    Getting the Discrete Percentile Getting the Discrete Percentile
  7. To try a different rate of the percentile distribution, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0.50) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.PropertyType) "Mohnthly Rate of First City Selected"
    FROM Listing.Properties props;
    GO
  8. Press F5 to execute
     
    Getting the Discrete Percentile
    Getting the Discrete Percentile Getting the Discrete Percentile
  9. To try a different rate of the percentile distribution, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0.75) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.PropertyType) "Mohnthly Rate of First City Selected"
    FROM Listing.Properties props;
    GO
  10. Press F5 to execute
  11. To change the order within, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_DISC(0.850) WITHIN GROUP(ORDER BY props.City) OVER(PARTITION BY props.PropertyType) "Mohnthly Rate of First City Selected"
    FROM Listing.Properties props;
    GO
  12. To execute, press F5
  13. Click inside the Query Editor, press Ctrl + A, and press Delete

The Continuous Distribution of a Percentile

One of the most common aspects of the values of a column is that they are usually different. This means that:

 
  •  The values of the table are known and available, meaning they are distributed
  • Whenever you (randomly) select a value, it is likely to be different from the other values
  • Whenever you (randomly) select a value, your selection can be any of the values. This means that the number of possible selections is infinite

These characteristics mean that the distribution is continuous. To let you evaluate the continuous distribution of a percentile, Transact-SQL provides the PERCENTILE_CONT() function. Its syntax is:

PERCENTILE_CONT ( numeric_literal ) 
    WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )
    OVER ( [ <partition_by_clause> ] )

The PERCENTILE_CONT() function takes one argument that must be a decimal value between 0.00 and 1.00. Calling the function is followed by the WITHIN GROUP() clause. In the parentheses of that clause, you must arrange the values of a column of your choice. The column must be numeric-based. Strings are not allowed. This is followed by a call to OVER() in which you can create partitions but no ORDER BY.

The Continuous Distribution of a Percentile

Practical LearningPractical Learning: Getting the Percentage Distribution of Records

  1. To get a percentile of a continuous distribution of the values of a column, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
           PERCENTILE_CONT(0) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER() "Continuous Percentile"
    FROM Listing.Properties props;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that the function produces the lowest value of the first selected column:
     
    Getting the Percentile Continuous Distribution of Records Getting the Percentile Continuous Distribution of Records
  3. To create partitions, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
           PERCENTILE_CONT(0) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.MonthlyRate) "Continuous Percentile"
    FROM Listing.Properties props;
    GO
  4. To execute, on the main menu, click Query -> Execute:
     
    Getting the Percentile Continuous Distribution of Records Getting the Percentile Continuous Distribution of Records
  5. To try another percentile distribution, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           props.MonthlyRate,
           PERCENTILE_CONT(1) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER() "Continuous Percentile"
    FROM Listing.Properties props;
    GO
  6. To execute, press F5
  7. Change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.City,
           PERCENTILE_CONT(1) WITHIN GROUP(ORDER BY props.MonthlyRate) OVER(PARTITION BY props.MonthlyRate) "Continuous Percentile"
    FROM Listing.Properties props;
    GO
  8. Press F5 to execute
  9. Click inside the Query Editor, press Ctrl + A, and press Delete

The Cumulative Distribution of a Series

The cumulative distribution of the values of a column is the probability that a certain value can be found at a certain position or a value less than that position. To let you can calculate it, Transact-SQL provides the CUME_DIST() function. Its syntax is:

CUME_DIST()
    OVER( [ partition_by_clause ] order_by_clause ) 
    RETURNS float(53)

The CUME_DIST() function takes no argument and it is followed by a call to OVER() that requires arranging the records.

The Cumulative Distribution of a Series

Practical LearningPractical Learning: Getting the Cumulative Distribution of a Series

  1. To find the cumulative distribution of monthly rates, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType Type,
           props.MonthlyRate Rate,
           CUME_DIST() OVER(ORDER BY props.MonthlyRate) N'Cumulative Distribution'
    FROM Listing.Properties props;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that the cumulative distribution of each monthly rate is the same:
     
    Cumulative Distribution Cumulative Distribution
  3. To evaluate the cumulative distribution of the monthly rate for each type of property, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType Type,
           props.MonthlyRate Rate,
           CUME_DIST() OVER(PARTITION BY props.PropertyType ORDER BY props.MonthlyRate) N'Cumulative Distribution'
    FROM Listing.Properties props;
    GO
  4. Press F5 to execute. Once again, the cumulative distribution of each monthly rate is the same:
     
    Cumulative Distribution Cumulative Distribution
    Cumulative Distribution
  5. To display the values in percent, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType Type,
           props.MonthlyRate Rate,
           FORMAT(CUME_DIST() OVER(PARTITION BY props.PropertyType ORDER BY props.MonthlyRate), N'P') N'Cumulative Distribution'
    FROM Listing.Properties props;
    GO
  6. Click inside the Query Editor, press Ctrl + A, and press Delete

Geting the Previous Record of a Group

One of the most common operations of data analysis is to perform comparisons and one of the goals of comparing records is to know how they change from one occurrence to another. To let you perform such comparisons, Transact-SQL provides the LAG() function. Its syntax is:

Geting the Previous Record of a Group
LAG(scalar_expression [, offset] [, default])
    OVER( [ partition_by_clause ] order_by_clause )
    RETURNS Data Type of scalar_expression

The LAG() function takes as argument one the columns of the table or view on which data selection is made. The function is followed by a call to OVER() that requires arranging the records.

By default, the LAG() function produces the value of the previous record passed to it. If you want, you can indicate how many records to jump back. That's the role of the offset argument.

If a record has no value, then the LAG() function would return the previous value as NULL, as shown in the last column of the following result:

Getting the Previous Value of a Record

If you don't want the table to display NULL for previous records that have no value, then pass the default argument.

Practical LearningPractical Learning: Getting the Previous Value of a Record

  1. To get the monthly rates of previous records, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
           props.City,
           LAG(props.MonthlyRate) OVER(ORDER BY props.MonthlyRate) "Comparison With Previous Value"
    FROM Listing.Properties props;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that each record of the last column holds the value of the previous record of the monthly rate:
     
    Getting the Previous Value of a Record
      
    Getting the Previous Value of a Record
  3. To test another column, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
           props.City,
           LAG(props.City) OVER(ORDER BY props.MonthlyRate) "Previous Monthly Rate"
    FROM Listing.Properties props;
    GO
  4. To execute, on the main menu, click Query -> Execute
  5. To create partitions, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
           props.City,
           LAG(props.MonthlyRate) OVER(PARTITION BY props.City ORDER BY props.MonthlyRate) "Previous Monthly Rate In Group"
    FROM Listing.Properties props;
    GO
  6. Press F5 to execute. This time, groups (partitions) are created based on the column applied to PARTITION BY:
     
    Getting the Previous Value of a Record
      
    Getting the Previous Value of a Record
  7. To indicate the number of records by which to jump back, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
    	   props.City,
           LAG(props.MonthlyRate, 3) OVER(PARTITION BY props.City ORDER BY props.MonthlyRate) "Back 3 Previous Monthly Rates"
    FROM Listing.Properties props;
    GO
  8. Press F5 to execute. Notice that the value produced by the function is 3 values back:
     
    Getting the Previous Value of a Record
      
    Getting the Previous Value of a Record
  9. To indicate what to select for previous records that have no value, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT props.PropertyType,
           props.MonthlyRate,
    	   props.City,
           LAG(props.MonthlyRate, 1, 0) OVER(PARTITION BY props.City ORDER BY props.MonthlyRate) "Previous Monthly Rates"
    FROM Listing.Properties props;
    GO
  10. Press F5 to execute:
     
    Getting the Previous Value of a Record
      
    Getting the Previous Value of a Record
  11. Click inside the Query Editor, press Ctrl + A, and press Delete

Getting the Next Record of a Group

As opposed to the previous value of a record, you may want to get the next value. To let you get this information, Transact-SQL provides the LEAD() function. Its syntax is:

LEAD(scalar_expression [, offset] [, default])
    OVER( [ partition_by_clause ] order_by_clause )
    RETURNS Data Type of scalar_expression

The LEAD() function takes the exact same argument as the LAG() function and it follows the same logic, in reverse.

Practical LearningPractical Learning: Getting the Next Value of a Record

  1. To get the monthly rates of next records, type the following code:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT t.FirstName        "First Name",
           t.LastName         "Last Name",
           t.MaritalStatus    "Status",
           t.NumberOfChildren "Children Count",
           LEAD(t.LastName) OVER(ORDER BY t.MaritalStatus) "Next Last Name"
    FROM Rentals.Tenants t;
    GO
  2. To execute, right-click inside the Query Editor and click Execute. Notice that each record of the last column holds the value of the next last name:
     
    Getting the Next Value of a Record
  3. To create partitions, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT t.FirstName        "First Name",
           t.LastName         "Last Name",
           t.MaritalStatus    "Status",
           LEAD(t.LastName)	OVER(PARTITION BY t.MaritalStatus ORDER BY t.MaritalStatus) "Next Last Name In Partition",
    	   t.NumberOfChildren "Children Count"
    FROM Rentals.Tenants t;
    GO
  4. Press F5 to execute. This time, groups (partitions) are created based on the column applied to PARTITION BY:
     
    Getting the Next Value of a Record
  5. To indicate the number of records by which to jump next, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT t.FirstName        "First Name",
           t.LastName         "Last Name",
           t.MaritalStatus    "Status",
           LEAD(t.LastName, 2) OVER(PARTITION BY t.MaritalStatus ORDER BY t.MaritalStatus) "Last Name In Next 2 Positions",
           t.NumberOfChildren "Children Count"
    FROM Rentals.Tenants t;
    GO
  6. Press F5 to execute. Notice that the value produced by the function is 2 values ahead:
     
    Getting the Next Value of a Record
  7. To indicate what to select for previous records that have no value, change the statement as follows:
    USE LambdaPropertiesManagement1;
    GO
    
    SELECT t.FirstName        "First Name",
           t.LastName         "Last Name",
           t.MaritalStatus    "Status",
           LEAD(t.LastName, 2, 1234) OVER(PARTITION BY t.MaritalStatus ORDER BY t.MaritalStatus) "Last Name In Next 2 Positions",
           t.NumberOfChildren "Children Count"
    FROM Rentals.Tenants t;
    GO
  8. Press F5 to execute:
     
    Getting the Next Value of a Record
  9. Close Microsoft SQL Server
  10. When asked whether you want to save, click No

Previous Copyright © 2012-2013 FunctionX Next