DISTINCT (SQL)

Synopsis

SELECT [DISTINCT [BY (item {,item2})] ]  |  [ALL]
  select-item {,select-item2}

概要

オプションの DISTINCT 節は、SELECT キーワードの後、オプションの TOP 節および最初の select-item の前に記述します。

DISTINCT 節は、SELECT 文の結果セットに適用されます。これは、返される行を、個別 (一意) の値ごとに 1 つの任意の行に制限します。DISTINCT 節が指定されない場合、既定では SELECT 条件を満たすすべての行を表示します。ALL 節は、DEFAULT 節を指定しないことと同義です。ALL を指定した場合の SELECT では、SELECT 条件を満たす、テーブル内のすべての行が返されます。

DISTINCT 節には、以下の 2 つの形式があります。

• SELECT DISTINCT：select-item の値の一意な組み合わせごとに 1 行を返します。1 つまたは複数の select-items を指定できます。例えば、以下のクエリでは、Home_State および Age の値の一意な組み合わせごとに、Home_State および Age の値を含む行が返されます。

  SELECT DISTINCT Home_State,Age FROM Sample.Person

  

• SELECT DISTINCT BY (item)：item の値の一意な組み合わせごとに 1 行を返します。単一の item、またはコンマ区切りの item のリストを指定できます。指定された item または item のリストは、括弧で囲む必要があります。BY キーワードの括弧の間は、スペースを指定しても省略してもかまいません。select-item リストには、指定した item を含めることができます (必須ではありません)。例えば、以下のクエリでは、Home_State および Age の値の一意な組み合わせごとに、Name および Age の値を含む行が返されます。

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

  

  item フィールドは、列名で指定する必要があります。有効な値としては、列名 (DISTINCT BY (City)) や、%ID (すべての行を返す) や、列名を指定するスカラ関数 (DISTINCT BY (ROUND(Age,-1))) や、列名を指定する照合関数 (DISTINCT BY (%EXACT(City))) があります。列エイリアスでフィールドを指定できません。これを試行すると、SQLCODE -29 エラーが生成されます。列番号でフィールドを指定することはできません。これはリテラルとして解釈され、1 つの行を返します。DISTINCT 節内の item 値としてリテラルを指定すると、1 行が返されます。どの行が返されるかは不確定となります。したがって、7、'Chicago'、''、0、または NULL のどれを指定しても、1 行が返されます。ただし、コンマ区切りリスト内の item 値としてリテラルを指定すると、そのリテラルは無視され、DISTINCT が指定されたフィールド名の一意の組み合わせごとに任意の 1 つの行を選択します。

DISTINCT 節は、TOP 節の前に適用されます。両方が指定されている場合は、SELECT では、一意な値を含む行と、TOP 節で指定された一意な値の行数のみが返されます。

DISTINCT 節で指定された列に NULL (値を含まない) 行がある場合、DISTINCT では、以下の例で示すように、個別 (一意の) 値として NULL 値を含む 1 行が返されます。

SELECT DISTINCT FavoriteColors FROM Sample.Person

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

埋め込み SQL の単純なクエリで DISTINCT 節を指定しても効果はありません。このタイプの埋め込み SQL の場合、SELECT では、データは必ず 1 行しか返されないためです。ただし、埋め込み SQL のカーソルベースのクエリでは、複数のデータ行を返すことができます。カーソルベースのクエリの DISTINCT 節では、一意な値の行のみが返されます。

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

Caution:

唯一の item または select-item が集約関数である DISTINCT 節を GROUP BY 節と共に使用すると、DISTINCT 節は無視されます。DISTINCT、集約関数、および GROUP BY を意図的に組み合わせるには、サブクエリを使用します。詳細とプログラムの例は、"GROUP BY 節" のリファレンス・ページを参照してください。

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

DISTINCT は、フィールドに対して定義された照合タイプに基づいて、文字列値をグループ化します。既定では、文字列データ型フィールドは大文字と小文字が区別されない SQLUPPER 照合で定義されます。現在のネームスペースにおける既定の文字列の照合の定義およびフィールド/プロパティの定義における既定以外のフィールドの照合タイプの指定の詳細は、"InterSystems 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 およびオブジェクトの設定]、[SQL] の順に選択します。[GROUP BY および DISTINCT クエリで元の値を生成する] オプションを表示して編集します (この最適化は GROUP BY 節にも有効です)。既定の設定は “いいえ” です。

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

詳細は、"システム管理ガイド" にリストされている SQL およびオブジェクトの設定ページを参照してください。

また、$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドの FastDistinct オプションを使用して、このオプションをシステム全体で設定することもできます。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[DISTINCT の最適化を有効にする] 設定が表示されます。既定値は 1 です。

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)
        QUIT:(SQLCODE'=0)
   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 が設定されることはありません。

例

以下のクエリでは、各個別の Home_State 値ごとに 1 行が返されます。

SELECT DISTINCT Home_State FROM Sample.Person
ORDER BY Home_State

以下のクエリでは、各個別の Home_State 値ごとに 1 行が返されますが、その行の追加フィールドが返されます。どの行が取得されるかは予測できません。

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

以下のクエリでは、Home_State 値と Office_State 値の各個別の組み合わせごとに 1 行が返されます。データに応じて、前の例よりも多くの行が返されるか、前の例と同じ数の行が返されます。

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

以下のクエリでは、DISTINCT BY が使用され、各個別の名前の長さごとに 1 行が返されます。

SELECT DISTINCT BY ($LENGTH(Name)) Name,$LENGTH(Name) AS lname
FROM Sample.Person
ORDER BY lname

以下のクエリでは、DISTINCT BY が使用され、FavoriteColors %List 値の各個別の先頭要素ごとに 1 行が返されます。これは、FavoriteColors が NULL である個別の 1 行を表示します。

SELECT DISTINCT BY ($LIST(FavoriteColors,1)) Name,FavoriteColors,$LIST(FavoriteColors,1) AS FirstColor
FROM Sample.Person

以下のクエリは、Sample.Person から取得した最初の 20 個の異なる Home_State 値を照合順の昇順で返します。“上位” 行には、ORDER BY 節により、Sample.Person での全行の順序が反映されます。

SELECT DISTINCT TOP 20 Home_State FROM Sample.Person ORDER BY Home_State

以下のクエリは、メイン・クエリと WHERE 節サブクエリの両方で DISTINCT を使用しています。これによって、Sample.Employee にも含まれる、Sample.Person の最初の 20 個の異なる Home_State 値が返されます。サブクエリ DISTINCT を使用しないと、Sample.Employee からランダムに選択した Home_State 値と一致する Sample.Person の異なる Home_State 値を取得します。

SELECT DISTINCT TOP 20 Home_State FROM Sample.Person 
WHERE Home_State IN(SELECT DISTINCT TOP 20 Home_State FROM Sample.Employee)
ORDER BY Home_State

以下のクエリでは、個別の FavoriteColor 値のうち最初の 20 個が返されます。これには、ORDER BY 節により、Sample.Person 内での全行の順序が反映されます。FavoriteColors フィールドに NULL が含まれていることが判明しているため、FavoriteColors が NULL である重複しない 1 行が照合順の先頭になります。

SELECT DISTINCT BY (FavoriteColors) TOP 20 FavoriteColors,Name FROM Sample.Person 
      ORDER BY FavoriteColors

また、上記の例では FavoriteColors はリスト・フィールドなので、照合順序には要素のバイト長も加味されます。したがって、3 文字の要素 (RED) が先頭にある重複しないリスト値は、4 文字の要素 (BLUE) が先頭にあるリスト値の前にリストされます。

関連項目

• SELECT 文

• GROUP BY 節

• ORDER BY 節

• TOP 節

• 集約関数

• "InterSystems SQL の使用法" の “データベースの問い合わせ” の章

• "InterSystems SQL の使用法" の “照合” の章