サーチ…


前書き

分析関数を使用して、値のグループに基づいて値を決定します。たとえば、このタイプの関数を使用して、実行中の合計、パーセンテージ、またはグループ内の最上位の結果を判断できます。

構文

  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を返しIDOVER句は最低税率を得るために税率を注文するために使用されます。

StateProvinceID 税率 FirstValue
74 ユタ州の消費税 5.00 74
36 ミネソタ州販売税 6.75 74
30 マサチューセッツ州販売税 7.00 74
1 カナダのGST 7.00 74
57 カナダのGST 7.00 74
63 カナダのGST 7.00 74

LAST_VALUE

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

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

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

テリトリーID 開始日ビジネスID LastValue
1 2005-07-01 00.00.00.000 280 283
1 2006-11-01 00.00.00.000 284 283
1 2005-07-01 00.00.00.000 283 283
2 2007-01-01 00.00.00.000 277 275
2 2005-07-01 00.00.00.000 275 275
3 2007-01-01 00.00.00.000 275 277

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列に基づいてレコードを並べ替えます。

BusinessEntityID SalesYTD リード値ラグ値
274 559697.5639 3763178.1787 0.0000
275 3763178.1787 4251368.5497 559697.5639
276 4251368.5497 3189418.3662 3763178.1787
277 3189418.3662 1453719.4653 4251368.5497
278 1453719.4653 2315185.6110 3189418.3662
279 2315185.6110 1352577.1325 1453719.4653

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文で取り出された行をパーティション化またはグループ化し、各グループの結果を従業員が使用した病気休暇の数に基づいてソートします。

BusinessEntityID 職名 SickLeaveHours パーセントランク累積分布
267 アプリケーションスペシャリスト 57 0 0.25
268 アプリケーションスペシャリスト 56 0.333333333333333 0.75
269 アプリケーションスペシャリスト 56 0.333333333333333 0.75
272 アプリケーションスペシャリスト 55 1 1
262 チーフ・ファイナンシャル・オフィサーとのアシスタント 48 0 1
239 特典スペシャリスト 45 0 1
252 買い手 50 0 0.111111111111111
251 買い手 49 0.125 0.333333333333333
256 買い手 49 0.125 0.333333333333333
253 買い手 48 0.375 0.555555555555555
254 買い手 48 0.375 0.555555555555555

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列には、累積分布が指定された百分位数よりも高い行の値がリストされます。

BusinessEntityID 職名 SickLeaveHours 累積分布 Percentile Discreet
272 アプリケーションスペシャリスト 55 0.25 56
268 アプリケーションスペシャリスト 56 0.75 56
269 アプリケーションスペシャリスト 56 0.75 56
267 アプリケーションスペシャリスト 57 1 56

計算を一連の値に基づいて行うには、 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;
BusinessEntityID 職名 SickLeaveHours 累積分布 Percentile Discreet パーセンテイル連続
272 アプリケーションスペシャリスト 55 0.25 56 56
268 アプリケーションスペシャリスト 56 0.75 56 56
269 アプリケーションスペシャリスト 56 0.75 56 56
267 アプリケーションスペシャリスト 57 1 56 56


Modified text is an extract of the original Stack Overflow Documentation
ライセンスを受けた CC BY-SA 3.0
所属していない Stack Overflow