SQL
関数（アナリティック）

前書き

構文

1. FIRST_VALUE（scalar_expression）OVER（[partition_by_clause] order_by_clause [行範囲句]）
2. LAST_VALUE（scalar_expression）OVER（[partition_by_clause] order_by_clause [行範囲句]）
3. LAG（scalar_expression [、offset] [、default]）OVER（[partition_by_clause] order_by_clause）
4. LEAD（scalar_expression [、offset]、[デフォルト]）OVER（[partition_by_clause] order_by_clause）
5. PERCENT_RANK（）OVER（[partition_by_clause] order_by_clause）
6. CUME_DIST（）OVER（[partition_by_clause] order_by_clause）
7. PERCENTILE_DISC（numeric_literal）WITHIN GROUP（ORDER BY order_by_expression [ASC | DESC]）オーバー（[<partition_by_clause>]）
8. PERCENTILE_CONT（numeric_literal）WITHIN GROUP（ORDER BY order_by_expression [ASC | DESC]）OVER（[<partition_by_clause>]）

FIRST_VALUE

FIRST_VALUE関数を使用して、スカラー式を使用して識別する順序付き結果セットの最初の値を決定します。

SELECT StateProvinceID, Name, TaxRate,
       FIRST_VALUE(StateProvinceID)
        OVER(ORDER BY TaxRate ASC) AS FirstValue
FROM SalesTaxRate;

この例では、 FIRST_VALUE関数を使用して、最低税率の州または州のIDを返しID 。 OVER句は最低税率を得るために税率を注文するために使用されます。

LAST_VALUE

LAST_VALUEファンクションは、スカラー式を使用して指定した順序付き結果セットの最後の値を提供します。

SELECT TerritoryID, StartDate, BusinessentityID,
       LAST_VALUE(BusinessentityID) 
        OVER(ORDER BY TerritoryID) AS LastValue
FROM SalesTerritoryHistory;

この例では、 LAST_VALUE関数を使用して、 LAST_VALUEれた値の各行セットの最後の値を返します。

LAGとLEAD

LAG関数は、同じ結果セットの現在の行の前にある行のデータを提供します。たとえば、 SELECT文では、現在の行の値と前の行の値を比較できます。

スカラー式を使用して、比較する値を指定します。 offsetパラメータは、比較に使用される現在の行の前の行の数です。行数を指定しない場合は、1行のデフォルト値が使用されます。

デフォルトのパラメータは、offsetの式がNULL値を持つ場合に返される値を指定します。値を指定しない場合は、 NULL値が戻されます。

LEAD関数は、行セットの現在の行の後にある行のデータを提供します。たとえば、 SELECT文では、現在の行の値と次の行の値を比較できます。

スカラー式を使用して比較する値を指定します。 offsetパラメータは、比較に使用される現在の行の後の行の数です。

デフォルトのパラメータを使用して、offsetの式にNULL値がある場合にNULL値を指定します。これらのパラメーターを指定しないと、1行のデフォルトが使用され、 NULL値が戻されます。

SELECT BusinessEntityID, SalesYTD,
       LEAD(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lead value",
       LAG(SalesYTD, 1, 0) OVER(ORDER BY BusinessEntityID) AS "Lag value"
FROM SalesPerson;

この例では、LEAD関数とLAG関数を使用して、各従業員の売上値を上と下の従業員の売上値と比較し、BusinessEntityID列に基づいてレコードを並べ替えます。

PERCENT_RANKとCUME_DIST

PERCENT_RANKファンクションは、行セットに対する行のランキングを計算します。割合は、現在の行よりも低い値を持つグループ内の行の数に基づいています。

結果セットの最初の値は常にパーセントランクを持ちます。セット内の最高位または最後の値の値は常に1です。

CUME_DIST関数は、値のグループ内の指定された値の相対位置を、その値以下の値の割合を決定することによって計算します。これは累積分布と呼ばれます。

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
PERCENT_RANK() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Percent Rank",
CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours DESC)
       AS "Cumulative Distribution"
FROM Employee;

この例では、 ORDER句を使用して、従業員の役職に基づいてSELECT文で取り出された行をパーティション化またはグループ化し、各グループの結果を従業員が使用した病気休暇の数に基づいてソートします。

PERCENT_RANK機能は、各グループ内のエントリをランク付けします。各エントリについて、同じグループ内の値の低いエントリの割合を返します。

CUME_DIST関数は、現在の値以下の値の割合を返す点を除いて、同様です。

PERCENTILE_DISCとPERCENTILE_CONT

PERCENTILE_DISC関数は、累積分布がnumeric_literalパラメーターを使用してnumeric_literalた百分位数よりも高い最初の項目の値をリストします。

値は、 WITHIN GROUP句で指定された行セットまたはパーティションによってグループ化されます。

PERCENTILE_CONT機能は同様であるPERCENTILE_DISC関数が、最初の一致するエントリと次のエントリの和の平均値を返します。

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours)
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet"
FROM Employee;

0.5パーセンタイルと一致する、またはそれを超える行からの正確な値を見つけるには、 PERCENTILE_DISC関数の数値リテラルとして渡します。結果セットのPercentile Discreet列には、累積分布が指定された百分位数よりも高い行の値がリストされます。

計算を一連の値に基づいて行うには、 PERCENTILE_CONT関数を使用します。結果の「Percentile Continuous」列には、結果値と次に一致する値の合計の平均値が表示されます。

SELECT BusinessEntityID, JobTitle, SickLeaveHours,
       CUME_DIST() OVER(PARTITION BY JobTitle ORDER BY SickLeaveHours ASC)
       AS "Cumulative Distribution",
       PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Discreet",
       PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY SickLeaveHours) 
          OVER(PARTITION BY JobTitle) AS "Percentile Continuous"
FROM Employee;