DISTINCT

DISTINCT および ORDER BY

ORDER BY 節は、DISTINCT 節の前に適用されます。したがって、DISTINCT と ORDER BY を組み合わせて使用して、特定の行からの値が返るようにすることができます。

例えば、以下のプログラムでは、非重複 Home_State 値ごとに 1 行が返されます。この行は Name 値の照合順の昇順で並べ替えられているため、 Home_State ごとに返される行は、最も大きな Name 値が含まれる行となります。

SELECT DISTINCT BY (Home_State) Home_State,Name FROM Sample.Person
ORDER BY Name

詳細とプログラム例は、"ORDER BY 節" のリファレンス・ページを参照してください。

DISTINCT および GROUP BY

DISTINCT および GROUP BY は、指定されたフィールド (1 つまたは複数) でレコードをグループ化し、そのフィールドの一意な値ごとに 1 レコードを返します。それらの大きな違いは、DISTINCT では集約関数がグループ化の前に計算されるということです。GROUP BY では、集約関数はグループ化の後に計算されます。以下の例で、この違いを示します。

SELECT DISTINCT BY (ROUND(Age,-1)) Age,AVG(Age) AS AvgAge FROM Sample.Person
 /* AVG(Age) returns average of all ages in table */

SELECT Age,AVG(Age) AS AvgAge FROM Sample.Person GROUP BY ROUND(Age,-1)
 /* AVG(Age) returns an average age for each age group */

DISTINCT 節の指定では 1 つまたは複数の集約関数フィールドを使用できますが、集約関数は単一の値を返すため、これはあまり役に立ちません。したがって、以下の例では、1 つの行が返されます。

SELECT DISTINCT BY (AVG(Age)) Name,Age,AVG(Age) FROM Sample.Person

大文字/小文字の区別と DISTINCT の最適化

DISTINCT は、フィールドに対して定義された照合タイプに基づいて、文字列値をグループ化します。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合の定義およびフィールド/プロパティの定義における既定以外のフィールドの照合タイプの指定の詳細は、"Caché SQL の使用法" の “照合” の章を参照してください。

フィールド/プロパティの照合タイプが SQLUPPER である場合、グループ化されたフィールド値はすべて大文字で返されます。大文字/小文字の区別を元のままにして値をグループ化するには、またはグループ化されたフィールドの返り値を元の大文字/小文字区別で表示するには、%EXACT 照合関数を使用します。以下に例を示します。この例は、Home_City フィールドが照合タイプ SQLUPPER で定義されており、‘New York’ と ‘new york’ の値が含まれていることを前提としています。

SELECT DISTINCT BY (Home_City) Name,Home_City FROM Sample.Person
/* groups together Home_City values by their uppercase letter values
   returns the name of each grouped city in uppercase letters. 
   Thus, 'NEW YORK' is returned.                          */

SELECT DISTINCT BY (Home_City) Name,%EXACT(Home_City) FROM Sample.Person
/* groups together Home_City values by their uppercase letter values
   returns the name of each grouped city in original letter case. 
   Thus, 'New York' or 'new york' may be returned, but not both. */

SELECT DISTINCT BY (%EXACT(Home_City)) Name,Home_City FROM Sample.Person
/* groups together Home_City values by their original letter case
   returns the name of each grouped city in original letter case.
   Thus, both 'New York' and 'new york' are returned.
   Optimization is not used.                       */

管理ポータルを使用して、DISTINCT 句を含むクエリのパフォーマンスを最適化できます。システム, 構成, 一般SQL設定 を選択し、[DISTINCT最適化有効] オプションを表示して編集します (この最適化は GROUP BY 節にも有効です)。既定は “はい” です。詳細は、"Caché 詳細構成設定リファレンス" で説明されている SQL 構成設定を参照してください。

SetFastDistinct()Opens in a new tab メソッドを使用して、このシステム全体のオプションを 1 または 0 に設定することもできます。

  WRITE $SYSTEM.SQL.SetFastDistinct(1)

この最適化は選択したフィールドのインデックスを利用します。そのため、インデックスが 1 つまたは複数の選択したフィールドに対して存在する場合にのみ意味があります。インデックス内に格納されるときにフィールド値が照合され、アルファベット文字列はすべて大文字で返されます。このシステム全体用オプションを設定した後に、特定のクエリのその設定を、%EXACT 照合関数を使用することで大文字/小文字区別を維持するように上書きできます。

DISTINCT のその他の使用法

• アスタリスク構文：構文 DISTINCT * は、正しい使用法ですが役立ちません。これは定義上、すべての行に、重複しない一意の識別子が含まれているためです。構文 DISTINCT BY (*) は、正しい使用法ではありません。

• サブクエリ：サブクエリでの DISTINCT 節の使用は正しい使用法となりますが、サブクエリは単一の値を返すため、役には立ちません。

• 選択する行データがない：DISTINCT 節は、テーブル・データにアクセスしない SELECT で使用できます。SELECT に FROM 節が含まれている場合には、DISTINCT を指定すると、このような非テーブル値を含む 1 行が返されます。DISTINCT (または TOP) を指定しないと、SELECT によって、FROM 節のテーブルにある行と同じ値を含む同数の行が返されます。SELECT に FROM 節が含まれていない場合の DISTINCT の使用は、正しい使用法ですが役立ちません。詳細は、"FROM" 節を参照してください。

• 集約関数 ： 集約関数内で DISTINCT 節を使用することで、集約に含める対象の個別 (一意) のフィールド値のみを選択できます。SELECT DISTINCT 節とは異なり、集約関数内の DISTINCT には、個別 (一意の) 値として NULL が含まれません。MAX および MIN 集約関数は、エラーなしで DISTINCT 節の構文を解析しますが、この構文はいかなる処理も実行しません。

DISTINCT と %ROWID

DISTINCT キーワードを指定すると、カーソル・ベースの埋め込み SQL クエリで %ROWID 変数が設定されなくなります。DISTINCT によって返される行が制限されない場合であっても、%ROWID は設定されません。以下に例を示します。

   SET %ROWID=999
   &sql(DECLARE EmpCursor CURSOR FOR 
        SELECT DISTINCT Name, Home_State
        INTO :name,:state FROM Sample.Person
        WHERE Home_State %STARTSWITH 'M')
   &sql(OPEN EmpCursor)
   FOR { &sql(FETCH EmpCursor)
        QUIT:SQLCODE  
        WRITE !,"RowID: ",%ROWID," row count: ",%ROWCOUNT
        WRITE " Name=",name," State=",state
  }
   &sql(CLOSE EmpCursor)

クエリ動作のこの変更は、カーソル・ベースの埋め込み SQL SELECT クエリにのみ適用されます。ダイナミック SQL SELECT クエリと非カーソルの埋め込み SQL SELECT クエリでは、%ROWID が設定されることはありません。

DISTINCT とトランザクション処理

DISTINCT キーワードを指定すると、クエリは、現在のトランザクションにまだコミットされていないデータを含む現在のすべてのデータを取得します。トランザクションの READ COMMITTED 分離モード・パラメータ (設定されている場合) は無視され、すべてのデータは READ UNCOMMITTED モードで取得されます。詳細は、"Caché SQL の使用法" の “データベースの変更“ の章にある "トランザクション処理" を参照してください。