(window.webpackJsonp=window.webpackJsonp||[]).push([[3267],{3674:function(t,e,s){"use strict";s.r(e);var a=s(31),n=Object(a.a)({},(function(){var t=this,e=t.$createElement,s=t._self._c||e;return s("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[s("h1",{attrs:{id:"functions-analytic"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#functions-analytic"}},[t._v("#")]),t._v(" Functions (Analytic)")]),t._v(" "),s("p",[t._v("You use analytic functions to determine values based on groups of values. For example, you can use this type of function to determine running totals, percentages, or the top result within a group.")]),t._v(" "),s("h2",{attrs:{id:"lag-and-lead"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#lag-and-lead"}},[t._v("#")]),t._v(" LAG and LEAD")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("LAG")]),t._v(" function provides data on rows before the current row in the same result set. For example, in a "),s("code",[t._v("SELECT")]),t._v(" statement, you can compare values in the current row with values in a previous row.")]),t._v(" "),s("p",[t._v("You use a scalar expression to specify the values that should be compared. The offset parameter is the number of rows before the current row that will be used in the comparison. If you don't specify the number of rows, the default value of one row is used.")]),t._v(" "),s("p",[t._v("The default parameter specifies the value that should be returned when the expression at offset has a "),s("code",[t._v("NULL")]),t._v(" value. If you don't specify a value, a value of "),s("code",[t._v("NULL")]),t._v(" is returned.")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("LEAD")]),t._v(" function provides data on rows after the current row in the row set. For example, in a "),s("code",[t._v("SELECT")]),t._v(" statement, you can compare values in the current row with values in the following row.")]),t._v(" "),s("p",[t._v("You specify the values that should be compared using a scalar expression. The offset parameter is the number of rows after the current row that will be used in the comparison.")]),t._v(" "),s("p",[t._v("You specify the value that should be returned when the expression at offset has a "),s("code",[t._v("NULL")]),t._v(" value using the default parameter. If you don't specify these parameters, the default of one row is used and a value of "),s("code",[t._v("NULL")]),t._v(" is returned.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" SalesYTD"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       LEAD"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("SalesYTD"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("0")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Lead value"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       LAG"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("SalesYTD"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("0")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Lag value"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" SalesPerson"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("This example uses the LEAD and LAG functions to compare the sales values for each employee to date with those of the employees listed above and below, with records ordered based on the BusinessEntityID column.")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("BusinessEntityID")]),t._v(" "),s("th",[t._v("SalesYTD")]),t._v(" "),s("th",[t._v("Lead value")]),t._v(" "),s("th",[t._v("Lag value")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("274")]),t._v(" "),s("td",[t._v("559697.5639")]),t._v(" "),s("td",[t._v("3763178.1787")]),t._v(" "),s("td",[t._v("0.0000")])]),t._v(" "),s("tr",[s("td",[t._v("275")]),t._v(" "),s("td",[t._v("3763178.1787")]),t._v(" "),s("td",[t._v("4251368.5497")]),t._v(" "),s("td",[t._v("559697.5639")])]),t._v(" "),s("tr",[s("td",[t._v("276")]),t._v(" "),s("td",[t._v("4251368.5497")]),t._v(" "),s("td",[t._v("3189418.3662")]),t._v(" "),s("td",[t._v("3763178.1787")])]),t._v(" "),s("tr",[s("td",[t._v("277")]),t._v(" "),s("td",[t._v("3189418.3662")]),t._v(" "),s("td",[t._v("1453719.4653")]),t._v(" "),s("td",[t._v("4251368.5497")])]),t._v(" "),s("tr",[s("td",[t._v("278")]),t._v(" "),s("td",[t._v("1453719.4653")]),t._v(" "),s("td",[t._v("2315185.6110")]),t._v(" "),s("td",[t._v("3189418.3662")])]),t._v(" "),s("tr",[s("td",[t._v("279")]),t._v(" "),s("td",[t._v("2315185.6110")]),t._v(" "),s("td",[t._v("1352577.1325")]),t._v(" "),s("td",[t._v("1453719.4653")])])])]),t._v(" "),s("h2",{attrs:{id:"percentile-disc-and-percentile-cont"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#percentile-disc-and-percentile-cont"}},[t._v("#")]),t._v(" PERCENTILE_DISC and PERCENTILE_CONT")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("PERCENTILE_DISC")]),t._v(" function lists the value of the first entry where the cumulative distribution is higher than the percentile that you provide using the "),s("code",[t._v("numeric_literal")]),t._v(" parameter.")]),t._v(" "),s("p",[t._v("The values are grouped by rowset or partition, as specified by the "),s("code",[t._v("WITHIN GROUP")]),t._v(" clause.")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("PERCENTILE_CONT")]),t._v(" function is similar to the "),s("code",[t._v("PERCENTILE_DISC")]),t._v(" function, but returns the average of the sum of the first matching entry and the next entry.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       CUME_DIST"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ASC")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Cumulative Distribution"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       PERCENTILE_DISC"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.5")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WITHIN")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n          "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Percentile Discreet"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employee"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("To find the exact value from the row that matches or exceeds the 0.5 percentile, you pass the percentile as the numeric literal in the "),s("code",[t._v("PERCENTILE_DISC")]),t._v(" function. The Percentile Discreet column in a result set lists the value of the row at which the cumulative distribution is higher than the specified percentile.")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("BusinessEntityID")]),t._v(" "),s("th",[t._v("JobTitle")]),t._v(" "),s("th",[t._v("SickLeaveHours")]),t._v(" "),s("th",[t._v("Cumulative Distribution")]),t._v(" "),s("th",[t._v("Percentile Discreet")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("272")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("55")]),t._v(" "),s("td",[t._v("0.25")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("268")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.75")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("269")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.75")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("267")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("57")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[s("strong",[t._v("56")])])])])]),t._v(" "),s("p",[t._v("To base the calculation on a set of values, you use the "),s("code",[t._v("PERCENTILE_CONT")]),t._v(' function. The "Percentile Continuous" column in the results lists the average value of the sum of the result value and the next highest matching value.')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       CUME_DIST"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ASC")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Cumulative Distribution"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       PERCENTILE_DISC"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.5")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WITHIN")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n          "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Percentile Discreet"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       PERCENTILE_CONT"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.5")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WITHIN")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n          "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Percentile Continuous"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employee"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("BusinessEntityID")]),t._v(" "),s("th",[t._v("JobTitle")]),t._v(" "),s("th",[t._v("SickLeaveHours")]),t._v(" "),s("th",[t._v("Cumulative Distribution")]),t._v(" "),s("th",[t._v("Percentile Discreet")]),t._v(" "),s("th",[t._v("Percentile Continuous")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("272")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("55")]),t._v(" "),s("td",[t._v("0.25")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("268")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.75")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("269")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.75")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[s("strong",[t._v("56")])])]),t._v(" "),s("tr",[s("td",[t._v("267")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("57")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[s("strong",[t._v("56")])])])])]),t._v(" "),s("h2",{attrs:{id:"first-value"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#first-value"}},[t._v("#")]),t._v(" FIRST_VALUE")]),t._v(" "),s("p",[t._v("You use the "),s("code",[t._v("FIRST_VALUE")]),t._v(" function to determine the first value in an ordered result set, which you identify using a scalar expression.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" StateProvinceID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" Name"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" TaxRate"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       FIRST_VALUE"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("StateProvinceID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" TaxRate "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ASC")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" FirstValue\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" SalesTaxRate"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("In this example, the "),s("code",[t._v("FIRST_VALUE")]),t._v(" function is used to return the "),s("code",[t._v("ID")]),t._v(" of the state or province with the lowest tax rate. The "),s("code",[t._v("OVER")]),t._v(" clause is used to order the tax rates to obtain the lowest rate.")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("StateProvinceID")]),t._v(" "),s("th",[t._v("Name")]),t._v(" "),s("th",[t._v("TaxRate")]),t._v(" "),s("th",[t._v("FirstValue")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("74")]),t._v(" "),s("td",[t._v("Utah State Sales Tax")]),t._v(" "),s("td",[t._v("5.00")]),t._v(" "),s("td",[t._v("74")])]),t._v(" "),s("tr",[s("td",[t._v("36")]),t._v(" "),s("td",[t._v("Minnesota State Sales Tax")]),t._v(" "),s("td",[t._v("6.75")]),t._v(" "),s("td",[t._v("74")])]),t._v(" "),s("tr",[s("td",[t._v("30")]),t._v(" "),s("td",[t._v("Massachusetts State Sales Tax")]),t._v(" "),s("td",[t._v("7.00")]),t._v(" "),s("td",[t._v("74")])]),t._v(" "),s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("Canadian GST")]),t._v(" "),s("td",[t._v("7.00")]),t._v(" "),s("td",[t._v("74")])]),t._v(" "),s("tr",[s("td",[t._v("57")]),t._v(" "),s("td",[t._v("Canadian GST")]),t._v(" "),s("td",[t._v("7.00")]),t._v(" "),s("td",[t._v("74")])]),t._v(" "),s("tr",[s("td",[t._v("63")]),t._v(" "),s("td",[t._v("Canadian GST")]),t._v(" "),s("td",[t._v("7.00")]),t._v(" "),s("td",[t._v("74")])])])]),t._v(" "),s("h2",{attrs:{id:"last-value"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#last-value"}},[t._v("#")]),t._v(" LAST_VALUE")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("LAST_VALUE")]),t._v(" function provides the last value in an ordered result set, which you specify using a scalar expression.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TerritoryID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" StartDate"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" BusinessentityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       LAST_VALUE"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("BusinessentityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n        "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" TerritoryID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" LastValue\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" SalesTerritoryHistory"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("This example uses the "),s("code",[t._v("LAST_VALUE")]),t._v(" function to return the last value for each rowset in the ordered values.")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("TerritoryID")]),t._v(" "),s("th",[t._v("StartDate")]),t._v(" "),s("th",[t._v("BusinessentityID")]),t._v(" "),s("th",[t._v("LastValue")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("2005-07-01 00.00.00.000")]),t._v(" "),s("td",[t._v("280")]),t._v(" "),s("td",[t._v("283")])]),t._v(" "),s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("2006-11-01 00.00.00.000")]),t._v(" "),s("td",[t._v("284")]),t._v(" "),s("td",[t._v("283")])]),t._v(" "),s("tr",[s("td",[t._v("1")]),t._v(" "),s("td",[t._v("2005-07-01 00.00.00.000")]),t._v(" "),s("td",[t._v("283")]),t._v(" "),s("td",[t._v("283")])]),t._v(" "),s("tr",[s("td",[t._v("2")]),t._v(" "),s("td",[t._v("2007-01-01 00.00.00.000")]),t._v(" "),s("td",[t._v("277")]),t._v(" "),s("td",[t._v("275")])]),t._v(" "),s("tr",[s("td",[t._v("2")]),t._v(" "),s("td",[t._v("2005-07-01 00.00.00.000")]),t._v(" "),s("td",[t._v("275")]),t._v(" "),s("td",[t._v("275")])]),t._v(" "),s("tr",[s("td",[t._v("3")]),t._v(" "),s("td",[t._v("2007-01-01 00.00.00.000")]),t._v(" "),s("td",[t._v("275")]),t._v(" "),s("td",[t._v("277")])])])]),t._v(" "),s("h2",{attrs:{id:"percent-rank-and-cume-dist"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#percent-rank-and-cume-dist"}},[t._v("#")]),t._v(" PERCENT_RANK and CUME_DIST")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("PERCENT_RANK")]),t._v(" function calculates the ranking of a row relative to the row set. The percentage is based on the number of rows in the group that have a lower value than the current row.")]),t._v(" "),s("p",[t._v("The first value in the result set always has a percent rank of zero. The value for the highest-ranked – or last – value in the set is always one.")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("CUME_DIST")]),t._v(" function calculates the relative position of a specified value in a group of values, by determining the percentage of values less than or equal to that value. This is called the cumulative distribution.")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" BusinessEntityID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" JobTitle"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" SickLeaveHours"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nPERCENT_RANK"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DESC")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Percent Rank"')]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\nCUME_DIST"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("OVER")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("PARTITION")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" JobTitle "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" SickLeaveHours "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DESC")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n       "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token string"}},[t._v('"Cumulative Distribution"')]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employee"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("In this example, you use an "),s("code",[t._v("ORDER")]),t._v(" clause to partition – or group – the rows retrieved by the "),s("code",[t._v("SELECT")]),t._v(" statement based on employees' job titles, with the results in each group sorted based on the numbers of sick leave hours that employees have used.")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("BusinessEntityID")]),t._v(" "),s("th",[t._v("JobTitle")]),t._v(" "),s("th",[t._v("SickLeaveHours")]),t._v(" "),s("th",[t._v("Percent Rank")]),t._v(" "),s("th",[t._v("Cumulative Distribution")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("267")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("57")]),t._v(" "),s("td",[t._v("0")]),t._v(" "),s("td",[t._v("0.25")])]),t._v(" "),s("tr",[s("td",[t._v("268")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.333333333333333")]),t._v(" "),s("td",[t._v("0.75")])]),t._v(" "),s("tr",[s("td",[t._v("269")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("56")]),t._v(" "),s("td",[t._v("0.333333333333333")]),t._v(" "),s("td",[t._v("0.75")])]),t._v(" "),s("tr",[s("td",[t._v("272")]),t._v(" "),s("td",[t._v("Application Specialist")]),t._v(" "),s("td",[t._v("55")]),t._v(" "),s("td",[t._v("1")]),t._v(" "),s("td",[t._v("1")])]),t._v(" "),s("tr",[s("td",[t._v("262")]),t._v(" "),s("td",[t._v("Assitant to the Cheif Financial Officer")]),t._v(" "),s("td",[t._v("48")]),t._v(" "),s("td",[t._v("0")]),t._v(" "),s("td",[t._v("1")])]),t._v(" "),s("tr",[s("td",[t._v("239")]),t._v(" "),s("td",[t._v("Benefits Specialist")]),t._v(" "),s("td",[t._v("45")]),t._v(" "),s("td",[t._v("0")]),t._v(" "),s("td",[t._v("1")])]),t._v(" "),s("tr",[s("td",[t._v("252")]),t._v(" "),s("td",[t._v("Buyer")]),t._v(" "),s("td",[t._v("50")]),t._v(" "),s("td",[t._v("0")]),t._v(" "),s("td",[t._v("0.111111111111111")])]),t._v(" "),s("tr",[s("td",[t._v("251")]),t._v(" "),s("td",[t._v("Buyer")]),t._v(" "),s("td",[t._v("49")]),t._v(" "),s("td",[t._v("0.125")]),t._v(" "),s("td",[t._v("0.333333333333333")])]),t._v(" "),s("tr",[s("td",[t._v("256")]),t._v(" "),s("td",[t._v("Buyer")]),t._v(" "),s("td",[t._v("49")]),t._v(" "),s("td",[t._v("0.125")]),t._v(" "),s("td",[t._v("0.333333333333333")])]),t._v(" "),s("tr",[s("td",[t._v("253")]),t._v(" "),s("td",[t._v("Buyer")]),t._v(" "),s("td",[t._v("48")]),t._v(" "),s("td",[t._v("0.375")]),t._v(" "),s("td",[t._v("0.555555555555555")])]),t._v(" "),s("tr",[s("td",[t._v("254")]),t._v(" "),s("td",[t._v("Buyer")]),t._v(" "),s("td",[t._v("48")]),t._v(" "),s("td",[t._v("0.375")]),t._v(" "),s("td",[t._v("0.555555555555555")])])])]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("PERCENT_RANK")]),t._v(" function ranks the entries within each group. For each entry, it returns the percentage of entries in the same group that have lower values.")]),t._v(" "),s("p",[t._v("The "),s("code",[t._v("CUME_DIST")]),t._v(" function is similar, except that it returns the percentage of values less than or equal to the current value.")]),t._v(" "),s("h4",{attrs:{id:"syntax"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#syntax"}},[t._v("#")]),t._v(" Syntax")]),t._v(" "),s("li",[t._v("FIRST_VALUE ( scalar_expression )\nOVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )")]),t._v(" "),s("li",[t._v("LAST_VALUE ( scalar_expression )\nOVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )")]),t._v(" "),s("li",[t._v("LAG (scalar_expression [,offset] [,default])\nOVER ( [ partition_by_clause ] order_by_clause )")]),t._v(" "),s("li",[t._v("LEAD ( scalar_expression [ ,offset ] , [ default ] )\n OVER ( [ partition_by_clause ] order_by_clause )")]),t._v("\n1. PERCENT_RANK( ) OVER ( [ partition_by_clause ] order_by_clause )\n"),s("li",[t._v("CUME_DIST( )\n     OVER ( [ partition_by_clause ] order_by_clause )")]),t._v(" "),s("li",[t._v("PERCENTILE_DISC ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )\nOVER ( [ "),s("partition_by_clause",[t._v(" ] )")])],1),t._v(" "),s("li",[t._v("PERCENTILE_CONT ( numeric_literal ) WITHIN GROUP ( ORDER BY order_by_expression [ ASC | DESC ] )\nOVER ( [ "),s("partition_by_clause",[t._v(" ] )")])],1)])}),[],!1,null,null,null);e.default=n.exports}}]);