Cmdlets for SAS Xpt

Build 24.0.9062

Window 関数

Window 関数を使用すると、集計関数のように行のセットに対して1つの計算結果を返すのではなく、行のグループ（Window）から計算フィールドを作成し、各行に対して1つの計算結果を返すことができます。本製品 は、以下のWindow 関数構文をサポートしています。

Note：Window 関数のサポートは本製品 の実験的な機能です。この機能は、SQL-92準拠という本製品 のコアスコープを超えています。そのため、Window 関数のパフォーマンスは最適ではない場合があります。

Window 関数句

OVER

OVER 句は、Window 関数が実行されるWindow を定義します。 

SELECT A, B, <window function> OVER (<window frame>) FROM TableName

<window function> はサポートされているWindow 関数句を指し、<window frame> はWindow を定義するロジックを指定する1つ以上の句を指します。

PARTITION BY

PARTITION BY 句は、Window をパーティションと呼ばれるサブウィンドウに細分化します。PARTITION BY 句で指定された列の一意な値ごとに、その値を持つすべてのレコードがまとめて個々のパーティションを形成します。 

SELECT A, B, <window function> OVER (PARTITION BY A ORDER BY B) From SampleTable_1

<window function> はサポートされているWindow 関数句を指します。

Window 関数

本製品 は、計算、ランク付け、分析のためのWindow 関数をサポートします。

計算

このWindow 関数は、Window 内のレコードに対して数学的操作を実行します。

COUNT()

各パーティションのレコード数を計算します。計算される列のデータ型は"int" です。

各パーティションでは、すべてのレコードにそのパーティション内のレコードの合計数が表示されます。 

SELECT Name, Role, Earnings, COUNT() OVER (PARTITION BY Role) FROM Employees

COUNT_BIG()

各パーティションのレコード数を計算します。計算される列のデータ型は"bigint" です。

各パーティションでは、すべてのレコードにそのパーティション内のレコードの合計数が表示されます。 

SELECT Name, Role, Earnings, COUNT_BIG() OVER (PARTITION BY Role) FROM Employees

MIN(numeric_column)

パーティションごとの数値列の最小値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の最小値が表示されます。 

SELECT Name, Role, Earnings, MIN(Earnings) OVER (PARTITION BY Role) FROM Employees

MAX(numeric_column)

パーティションごとの数値列の最大値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の最大値が表示されます。 

SELECT Name, Role, Earnings, MAX(Earnings) OVER (PARTITION BY Role) FROM Employees

SUM(numeric_column)

パーティションごとの数値列の合計を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の合計が表示されます。 

SELECT Name, Role, Earnings, SUM(Earnings) OVER (PARTITION BY Role) FROM Employees

AVG(numeric_column)

パーティションごとの数値列の平均値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の平均値が表示されます。 

SELECT Name, Role, Earnings, AVG(Earnings) OVER (PARTITION BY Role) FROM Employees

MEDIAN(numeric_column)

パーティションごとの数値列の中央値を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の中央値が表示されます。 

SELECT Name, Role, Earnings, MEDIAN(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEV(numeric_column)

パーティションごとの数値列の標準偏差を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の標準偏差が表示されます。 

SELECT Name, Role, Earnings, STDEV(Earnings) OVER (PARTITION BY Role) FROM Employees

STDEVP(numeric_column)

パーティションごとの数値列の母標準偏差を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の母標準偏差が表示されます。 

SELECT Name, Role, Earnings, STDEVP(Earnings) OVER (PARTITION BY Role) FROM Employees

VAR(numeric_column)

パーティションごとの数値列の統計的標準分散を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の統計的標準分散が表示されます。 

SELECT Name, Role, Earnings, VAR(Earnings) OVER (PARTITION BY Role) FROM Employees

VARP(numeric_column)

パーティションごとの数値列の母集団分散を計算します。

各パーティションでは、すべてのレコードにそのパーティションのレコード全体のnumeric_column の母集団分散が表示されます。 

SELECT Name, Role, Earnings, VARP(Earnings) OVER (PARTITION BY Role) FROM Employees

ランク付け

これらのWindow 関数は、Window とそのパーティション内にあるレコードをランク付けします。

RANK()

必須のORDER BY 句で指定されたカラムの値に基づいて、Window 内の各レコードにランク番号を割り当てます。

2つ以上のレコードがランク付けされたカラムで同じ値を持つ場合、それらはすべて同じランク番号を受け取り、ランクカウントは内部的にインクリメントされ、ORDER BY カラムで重複する値を持つ各レコードのランク番号を1つ先にスキップします。 

SELECT Id, Column1, RANK() OVER (ORDER BY Column1) AS Rank FROM SampleTable_1

PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。 

SELECT Id, Column1, RANK() OVER (PARTITION BY Id ORDER BY Column1) AS Rank FROM SampleTable_1

DENSE_RANK()

RANK() 関数のように動作しますが、ランク付けされたカラムで重複した値を持つ各レコードの内部ランクカウンタをインクリメントしません。

つまり、ORDER BY カラムで同じ値を持つレコードは同じランク番号を持ちますが、この関数がランク番号をスキップすることはありません。 

SELECT Id, Column1, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Column1) AS Rank FROM SampleTable_1

PARTITION BY 句を追加すると、各パーティションに対して個別のランクが計算されます。 

SELECT Id, Column1, DENSE_RANK() OVER (PARTITION BY Id ORDER BY Column1) AS Rank FROM SampleTable_1

ROW_NUMBER()

各レコードの行番号を計算します。OVER 句の中のORDER BY 句は必須です。 

SELECT Name, Role, Earnings, ROW_NUMBER() OVER (ORDER BY Role) FROM Employees
PARTITION BY で複数のパーティションを定義すると、パーティションごとに新しい行番号のセットが計算されます。 
SELECT Name, Role, Earnings, ROW_NUMBER() OVER (PARTITION BY Role ORDER BY Earnings) FROM Employees

NTILE()

順序付けされたパーティションの行を、指定された数にほぼ等しいグループ、またはバケットに分割します。各グループに1から始まるバケット番号を割り当てます。グループ内の各行に対して、NTILE() 関数は、その行が属するグループを表すバケット番号を割り当てます。

NTILE() の構文は次のとおりです。 

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)
NTILE() がサポートするパラメータを以下に示します。

  • buckets：行を分割するバケット数。バケットには、正の整数として返される式やサブクエリを指定します。これはWindow 関数ではありません。
  • PARTITION BY：結果セットの行をNTILE() 関数が適用されるパーティションに分割します。
  • ORDER BY は、NTILE() が適用される各パーティションの行の論理的な順序を指定する句です。

行数がバケット数で割り切れない場合、NTILE() 関数は2つのサイズのグループを1つの差をつけて返します。OVER() 句のORDER BY で設定された順序に従い、大きなグループは小さなグループに常に先行します。

行の合計がバケット数で割り切れる場合、この関数は行をバケットに均等に分割します。 次のステートメントは、10個の整数を格納するntile_demo という新しいテーブルを作成します。 

CREATE TABLE sales.ntile_demo (
	v INT NOT NULL
);
	
INSERT INTO sales.ntile_demo(v) 
VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);	
	
SELECT * FROM sales.ntile_demo;
このステートメントでは、NTILE() 関数を使用して、10行を3つのグループに分割します。 
SELECT 
	v, 
	NTILE (3) OVER (
		ORDER BY v
	) buckets
FROM 
	sales.ntile_demo;

分析

このWindow 関数は、Window 内のレコードに対して分析操作を実行します。

PERCENT_RANK()

各行の相対ランクSQL パーセンタイルを計算します。0より大きな値を返しますが、最大値は1です。NULL 値はカウントしません。この関数は非決定的です。

PERCENT_RANK() の構文は次のとおりです。 

PERCENT_RANK() OVER (
      [PARTITION BY partition_expression, ... ]
      ORDER BY sort_expression [ASC | DESC], ...
  )
この構文では以下のパラメータを使用します。

  • PARTITION BY：デフォルトでは、SQL Server はデータセット全体を1つのセットとして扱います。PARTITION BY 句を指定すると、データを複数のセットに分けることができます。Percent_Rank 関数は、各セットの分析計算を行います。このパラメータはオプションです。
  • ORDER BY：データを昇順または降順に並べ替えます。このパラメータは必須です。

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9062