HAVING

1 つ以上の制限条件を指定する SELECT 節です。

Synopsis

SELECT field
FROM table
GROUP BY field
HAVING condition-expression

SELECT aggregatefunc(field %AFTERHAVING)
FROM table
[GROUP BY field]
HAVING condition-expression

引数

condition-expression

どのデータ値が取得されるかを規定する 1 つまたは複数のブーリアン述語で構成される式。

概要

オプションの HAVING 節は、FROM 節とオプションの WHERE 節および GROUP BY 節の後、オプションの ORDER BY 節の前に記述されます。

SELECT 文の HAVING 節は、クエリで選択された特定の行の適格/不適格を決めます。適格な行とは、condition-expression が True である行です。condition-expression は、AND および OR 論理演算子によってリンク可能な論理テスト (述語) の一式です。詳細は、"WHERE" 節を参照してください。

HAVING 節は、データ・セット全体ではなく、グループに対して処理を実行できる WHERE 節に似ています。したがって、ほとんどの場合、HAVING 節は、%AFTERHAVING キーワードを使用した集約関数で使用するか、GROUP BY 節と組み合わせて使用するか、またはその両方で使用します。

HAVING 節の condition-expression では、集約関数を指定することもできます。WHERE 節の condition-expression では、集約関数は指定できません。詳細は、以下の例を参照してください。

SELECT Name,Age,AVG(Age) AS AvgAge
FROM Sample.Person
HAVING Age > AVG(Age)
ORDER BY Age

HAVING 節は、サブ母集団の集約と、全体の母集団の集約とを頻繁に比較します。

select-item リストの集約関数

HAVING 節は、返す行を選択します。既定では、この行の選択によって select-item リストの集約関数の値は決定されません。この理由は、HAVING 節は、select-item リストの集約関数の後で解析されるからです。

以下の例では、Age > 65 の行のみが返されます。ただし、AVG(Age) は、HAVING 節で選択された行だけでなく、すべての行に基づいて計算されます。

SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person
HAVING Age > 65
 ORDER BY Age

これを WHERE 節と比較すると、WHERE 節は、返す行と、select-item リストの集約関数に含める行の値を選択します。

SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person
WHERE Age > 65
ORDER BY Age

HAVING 節は、集約値のみを返すクエリで使用できます。

集約しきい値： HAVING 節は、集約しきい値を使用して、(クエリの集約値を含む) 1 行を返すか 0 行を返すかを決定します。したがって、HAVING 節を使用することで、集約しきい値に達した場合のみ集約計算を返すことができます。以下の例では、テーブルの行数が 100 行以上の場合のみ、テーブルのすべての行の Age 値の平均が返されます。行数が 100 行未満の場合、すべての行の Age 値の平均は無意味なことがあるので、返されません。
```
SELECT AVG(Age) FROM Sample.Person HAVING COUNT(*)>99
```
複数行：集約関数が含まれる HAVING 節が指定され、GROUP BY 節が指定されていない場合は、HAVING 節の条件を満たす行の数が返されます。集約関数値は、テーブルのすべての行に基づいて計算されます。
```
SELECT AVG(Age) FROM Sample.Person HAVING %ID<10
```
これは、1 行を返す、集約関数が含まれる WHERE 節とは対照的です。集約関数値は、WHERE 節の条件を満たす行に基づいて計算されます。
```
SELECT AVG(Age) FROM Sample.Person WHERE %ID<10
```

%AFTERHAVING

%AFTERHAVING キーワードは select-item リストで集約関数と共に使用して、HAVING 節の条件の適用後に、集約演算を実行することを指定できます。

SELECT Name,Age,AVG(Age) AS AvgAge,
 AVG(Age %AFTERHAVING) AS AvgMiddleAge
 FROM Sample.Person
 HAVING Age > 40 AND Age < 65
 ORDER BY Age

以下の両方の考慮事項を満たした場合のみ、%AFTERHAVING キーワードは意味のある結果を返します。

select-item リストには、非集約のフィールド参照である項目を少なくとも 1 つ含める必要があります。このフィールド参照の対象は、FROM 節で指定された任意のテーブルの任意のフィールド、つまり、暗黙結合 (矢印構文)、%ID エイリアス、またはアスタリスク (*) を使用して参照されるフィールドになります。
HAVING 節の条件は、少なくとも 1 つの非集約条件に適用する必要があります。したがって、HAVING Age>50、HAVING Age>AVG(Age)、または HAVING Age>50 AND MAX(Age)>75 は有効な条件ですが、HAVING Age>50 OR MAX(Age)>75 は有効な条件ではありません。

以下の例では、HAVING 節と GROUP BY 節を使用して、州の平均年齢を返すと共に、テーブルのすべての行の平均年齢よりも高齢な人について州の平均年齢を返しています。また、サブクエリを使用して、テーブルのすべての行の平均年齢を返しています。

SELECT Home_State,(SELECT AVG(Age) FROM Sample.Person) AS AvgAgeAllRecs,
       AVG(Age) AS AvgAgeByState,AVG(Age %AFTERHAVING) AS AvgOlderByState 
FROM Sample.Person
GROUP BY Home_State
HAVING Age > AVG(Age)
ORDER BY Home_State

論理述語

SQL の述語は以下のカテゴリに分類されます。

Note:

HAVING 節では、%CONTAINS 比較述語や %CONTAINSTERM 比較述語、または FOR SOME %ELEMENT 述語を使用できません。これらの比較述語は、WHERE 節でのみ使用できます。

述語の大文字と小文字の区別

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

%INLIST、包含関係演算子 ([)、%MATCHES、および %PATTERN 述語は、フィールドの既定の照合を使用しません。常に大文字と小文字を区別する EXACT 照合が使用されます。

2 つのリテラル文字列の熟語の比較は、常に大文字小文字を区別します。

述語の条件と %NOINDEX

述語の条件の前に %NOINDEX キーワードを置くと、クエリ・オプティマイザが条件でインデックスを使用することを防ぐことができます。これは、多数の行を満たす範囲条件を指定する場合に最も便利です。例えば、HAVING %NOINDEX Age >= 1 のようにします。詳細は、“Caché SQL 最適化ガイド” の “インデックスの最適化オプション” を参照してください。

等値比較述語

以下は、使用できる比較述語です。

SQL 等値比較述語

述語	処理
=	等しい
<>	等しくない
!=	等しくない
>	より大きい
<	より小さい
>=	以上
<=	以下

以下の例は、比較述語を使用しています。21 より小さい Age ごとに 1 つのレコードが返されます。

SELECT Name, Age FROM Sample.Person
GROUP BY Age
HAVING Age < 21
ORDER BY Age

SQL は照合 (値がソートされる順番) という点から比較演算子を定義します。まったく同様の方法で照合する場合の 2 つの値は等しくなります。2 つ目の値の後に照合される場合、値は別の値よりも大きくなります。文字列データ型のフィールド照合は、フィールドの既定の照合に基づきます。既定では、大文字と小文字は区別されません。そのため、2 つの文字列フィールドの値の比較または文字列フィールド値と文字列リテラルとの比較では、既定では大文字と小文字は区別されません。例えば、Home_State フィールドの値が大文字の 2 文字の文字列の場合、以下のようになります。

式	値
'MA' = Home_State	値 MA に対して True
'ma' = Home_State	値 MA に対して True
'VA' < Home_State	値 VT、WA、WI、WV、WY に対して True
'ar' >= Home_State	値 AK、AL、AR に対して True

ただし、2 つのリテラル文字列の比較では大文字と小文字が区別され、WHERE 'ma'='MA' は常に FALSE です。

BETWEEN 述語

これは、「以上」と「以下」の組み合わせと同じ働きをします。以下の例は、BETWEEN 述語を使用しています。18 と 35 を含む、18 から 35 までの Age ごとに 1 つのレコードが返されます。

SELECT Name, Age FROM Sample.Person
GROUP BY Age
HAVING Age BETWEEN 18 AND 35
ORDER BY Age

詳細は、このドキュメントのリファレンスの "BETWEEN" を参照してください。

IN および %INLIST 述語

IN 述語は、構造化されていない一連の項目に値を一致させるために使用されます。

%INLIST 述語は、値をリスト構造の要素に一致させるための Caché の拡張機能です。

どちらの述語を使用しても、等値比較やサブクエリ比較を実行できます。

IN には 2 つの形式があります。1 つ目は、OR 演算子で複数の等値比較を結合する省略表現として使用します。以下はその例です。

SELECT Name, Home_State FROM Sample.Person
GROUP BY Home_State
HAVING Home_State IN ('ME','NH','VT','MA','RI','CT')

上記の文は、Home_State が括弧のリスト内の値と等しい場合、True と評価します。リストの要素は定数または式を指定できます。等式テストと同様に、照合が IN 比較に適用されます。既定では、IN 比較でフィールド定義の照合タイプが使用されます。既定では、文字列フィールドは大文字と小文字が区別されない SQLUPPER として定義されます。

IN 述語の等値比較に日付または時刻を使用すると、適切なデータ型変換が自動的に実行されます。HAVING 節フィールドが TimeStamp 型の場合、Date 型または Time 型の値は Timestamp に変換されます。HAVING 節フィールドが Date 型の場合、TimeStamp 型または String 型の値は Date に変換されます。HAVING 節フィールドが Time 型の場合、TimeStamp 型または String 型の値は Time に変換されます。

以下の 2 つの例は、同じ等値比較を実行し、同じデータを返します。GROUP BY フィールドは、成功した等値比較ごとに 1 つのレコードのみを返すことを指定しています。DOB フィールドは Date データ型です。

SELECT Name,DOB FROM Sample.Person 
GROUP BY DOB
HAVING DOB IN ({d '1951-02-02'},{d '1987-02-28'})

SELECT Name,DOB FROM Sample.Person
GROUP BY DOB
HAVING DOB IN ({ts '1951-02-02 02:37:00'},{ts '1987-02-28 16:58:10'})

詳細は、"日付/時刻文" を参照してください。

%INLIST 述語は、リスト構造の要素に対して等値比較を実行するために使用できます。%INLIST は EXACT 照合を使用します。そのため、既定では、%INLIST の文字列比較では大文字と小文字が区別されます。リスト構造の詳細は、SQL の $LIST 関数を参照してください。

以下の例では、%INLIST を使用して、FavoriteColors リスト・フィールドの要素に文字列値を一致させます。

SELECT Name,FavoriteColors FROM Sample.Person 
HAVING 'Red' %INLIST FavoriteColors

ここでは、FavoriteColors に “Red” 要素が含まれるすべてのレコードが返されます。

以下の埋め込み SQL の例では、Home_State 列の値を northne (New England 北部の州) リストの要素に一致させます。

   SET northne=$LISTBUILD("VT","NH","ME")
   &sql(DECLARE StateCursor CURSOR FOR 
        SELECT Name,Home_State
        INTO :name,:state FROM Sample.Person
        HAVING Home_State %INLIST :northne)
   &sql(OPEN StateCursor)
   NEW SQLCODE,%ROWCOUNT,%ROWID
   FOR { &sql(FETCH StateCursor)
        QUIT:SQLCODE  
        WRITE !,"#",%ROWCOUNT," Name=",name," State=",state,!
 }
   WRITE !,"Final Fetch SQLCODE: ",SQLCODE
   &sql(CLOSE StateCursor)

サブクエリで IN または %INLIST を使用し、列の値 (あるいは他の式) がサブクエリの行の値と等しいかどうかをテストできます。以下はその例です。

SELECT Name,Home_State FROM Sample.Person
HAVING Name IN 
 (SELECT Name FROM Sample.Employee
 HAVING Salary < 50000)

サブクエリは、SELECT リスト内に必ず 1 項目を持ちます。

詳細は、このドキュメントのリファレンスの "IN" と "%INLIST" を参照してください。

%STARTSWITH 述語

Caché %STARTSWITH 比較演算子により、文字列や数字の先頭文字列との部分的マッチングを実行できます。以下の例は、%STARTSWITH を使用します。まず年齢で選択され、その中の “S” で始まる Name のレコードが返されます。

SELECT Name,Age FROM Sample.Person
WHERE Age > 30
HAVING Name %STARTSWITH 'S'
ORDER BY Name

%STARTSWITH の比較では、他の文字列フィールドの比較と同様に大文字と小文字が区別されません。詳細は、このドキュメントのリファレンスの "%STARTSWITH" を参照してください。

包含関係演算子 ([)

包含関係演算子は開始ブラケット記号 ([) です。これを使用して、部分文字列 (文字列または数値) とフィールドの値の任意の部分とのマッチングができます。比較は常に大文字と小文字が区別されます。以下の例は、HAVING 節で包含関係演算子を使用して、Home_State の値に “K” を含むレコードを選択し、これらの州で %AFTERHAVING カウントを実行します。

SELECT Home_State,COUNT(Home_State) AS States,
   COUNT(Home_State %AFTERHAVING) AS KStates
 FROM Sample.Person
 HAVING Home_State [ 'K'

FOR SOME 述語

HAVING 節の FOR SOME 述語は、1 つ以上のフィールド値の条件テストに基づいて結果セットを返すかどうかを指定します。この述語の構文は以下のとおりです。

FOR SOME (table[AS t-alias]) (fieldcondition)

FOR SOME は、fieldcondition が True に評価される必要があるということを指定します。指定された条件に 1 つ以上のフィールド値が一致する必要があります。table には、単一のテーブル、またはコンマ区切りのテーブルのリストを指定可能であり、オプションでテーブル・エイリアスを指定できます。fieldcondition には、指定された table 内の 1 つまたは複数のフィールドのために 1 つまたは複数の条件を指定します。table　引数と fieldcondition 引数は、どちらも括弧で区切る必要があります。

以下の例は、FOR SOME 述語の使用法を示しています。

SELECT Name,Age
FROM Sample.Person
HAVING FOR SOME (Sample.Person)(Age>20)
ORDER BY Age

前述の例では、少なくとも 1 つのフィールドに 20 より大きい Age 値が含まれる場合、すべてのレコードが返されます。それ以外の場合、レコードは返されません。

詳細は、このドキュメントのリファレンスの "FOR SOME" を参照してください。

NULL 述語

定義されていない値を検出します。すべての NULL 値またはすべての NULL でない値を検出できます。

SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NULL

SELECT Name, FavoriteColors FROM Sample.Person
HAVING FavoriteColors IS NOT NULL 
ORDER BY FavoriteColors

GROUP BY 節を使用すると、指定されたフィールドの NULL でない値ごとに 1 つのレコードを返すことができます。

SELECT Name, FavoriteColors FROM Sample.Person
GROUP BY FavoriteColors
HAVING FavoriteColors IS NOT NULL 
ORDER BY FavoriteColors

詳細は、このドキュメントのリファレンスの "NULL" を参照してください。

EXISTS 述語

サブクエリが空のセットを評価するかどうかをテストするために、サブクエリを使用して処理します。

SELECT t1.disease FROM illness_tab t1 WHERE EXISTS 
 (SELECT t2.disease FROM disease_registry t2 
 WHERE t1.disease = t2.disease 
 HAVING COUNT(t2.disease) > 100)

詳細は、このドキュメントのリファレンスの "EXISTS" を参照してください。

LIKE、%MATCHES、および %PATTERN 述語

これらの 3 つの述語を使用してパターン・マッチングを実行できます。

LIKE では、リテラルとワイルドカードを使用してパターン・マッチを実行できます。リテラル文字の既知の部分文字列、または既知のシーケンス内にいくつかの既知の部分文字列を含むデータ値を返す場合は、LIKE を使用します。LIKE は、大文字と小文字の比較に、そのターゲットの照合を使用します。
%MATCHES では、リテラル、ワイルドカード、リスト、および範囲を使用してパターン・マッチを実行できます。リテラル文字の既知の部分文字列、指定された文字のリストまたは範囲にマッチする 1 つ以上のリテラル文字、または既知のシーケンス内にそうしたいくつかの部分文字列を含むデータ値を返す場合は、%MATCHES を使用します。 %MATCHES は、大文字と小文字の比較に EXACT 照合を使用します。
%PATTERN を使用すると、文字タイプのパターンを指定できます。例えば、'1U4L1",".A' (1 つの大文字、4 つの小文字、1 つのリテラル・コンマ、その後に任意数の大文字または小文字が続くパターン) などです。文字タイプの既知のシーケンスを含むデータ値を返す場合は、%PATTERN を使用します。%PATTERN はデータ値は重要でないけれども、その値の文字タイプ形式が重要である場合に役立ちます。%PATTERN では既知のリテラル文字も指定できます。リテラル比較では、常に大文字と小文字が区別される EXACT 照会を使用します。

文字列の最初の文字で比較を実行するには、%STARTSWITH 述語を使用します。

例

以下の例は、21 歳未満の人が 1 人以上いる各州の行を返します。行ごとに、その州の平均年齢、最低年齢、および最高年齢を返します。

SELECT Home_State, MIN(Age) AS Youngest,
  AVG(Age) AS AvgAge, MAX(Age) AS Oldest
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age < 21
 ORDER BY Youngest

以下の例は、21 歳未満の人が 1 人以上いる各州の行を返します。行ごとに、その州の平均年齢、最低年齢、および最高年齢を返します。また、%AFTERHAVING キーワードを使用して、州内の 21 歳未満の人の平均年齢 (AvgYouth) と 21 歳未満の人の最高年齢 (OldestYouth) を返します。

SELECT Home_State,AVG(Age) AS AvgAge,
   AVG(Age %AFTERHAVING) AS AvgYouth,
   MIN(Age) AS Youngest, MAX(Age) AS Oldest,
   MAX(Age %AFTERHAVING) AS OldestYouth
 FROM Sample.Person
 GROUP BY Home_State
 HAVING Age < 21
 ORDER BY AvgAge

%AFTERHAVING のその他の例は、個々の集約関数の説明を参照してください。