識別子、リテラル、および関数は、式に組み合わせることができます。式は、クエリ内のほとんどの場所で使用できます:SELECT
、FROM
(結合条件を指定する場合)、WHERE
、GROUP BY
、HAVING
、またはORDER BY
。
CData Virtuality Server は次のタイプの式をサポートします:
- カラム識別子
- リテラル
- Scalar Functions
- 配列
- Case とSearched Case
- スカラーサブクエリ
- パラメータ参照
Column Identifiers
列識別子は、SELECT
文の出力列、INSERT
とUPDATE
文の列とその値、WHERE
とFROM
句で使用される基準を指定するために使用されます。GROUP BY
, HAVING
, ORDER BY
句でも使用されています。列識別子の構文については、Identifiersセクションを参照してください。
Literals
リテラル値は固定値を表します。これらのデータ型は、「標準的な」データ型であればどれでもかまいません。
構文ルール:
- 整数値には、その値を保持するのに十分な大きさの整数データ型(integer、long、biginteger)が割り当てられます;
- 浮動小数点値は常にdoubleとして解析されます;
- キーワード'NULL'は存在しないか未知の値を表し、本質的に型付けされていません。多くの場合、NULLリテラル値にはコンテキストに基づいた暗黙の型が割り当てられます。例えば、関数 '5 + NULL' では、値 '5' の型に合わせて NULL 値に 'integer' 型が割り当てられます。暗黙的な文脈を持たない問い合わせの
SELECT
Clauseで使用されるNULLリテラルは、'string'型に割り当てられます。
Some examples of simple literal values
'
abc'
- エスケープされたシングルクォート:
'
isn'
'
t true'
5
- 科学的表記法:
37.75e01
- 正確な数値型
BigDecimal
:100.0
true
false
- ユニコード文字:
'\u0027'
- バイナリー:
X'0F0A'
DATE/TIME リテラルには、JDBC Escaped Literal Syntaxまたは ANSI キーワード構文を使用できます:
JDBC Escaped Literal Syntax | ANSI keyword syntax | |
---|---|---|
Date Literal | {d '...' } | DATE '...' |
Time Literal | {t '...' } | TIME '...' |
Timestamp Literal | {ts '...' } | TIMESTAMP '...' |
Using Unicode characters
SQL文字列には、任意のUnicode文字(コード0~65535)を指定できます。通常、バックスラッシュを使って通常の方法で特殊文字を含めようとすると、DVによって文字どおりに解釈されます。特殊文字を使用するには、文字列定数を囲む最初のアポストロフィ文字の前に'E'文字を使用する必要があります。
構文は以下のコードをサポートしています:\と、Unicode 文字を指定する標準的な方法: \uXXXX、X は 16 進数です。
例
SQL code | Output |
---|---|
select 'some\ntext' | some\ntext |
select E 'some\ntext' | some text |
select E 'danke sch\u00f6n' | danke schön |
Arrays
配列は、1つのデータ型の要素を並べたリストです。各要素には、アクセス可能なインデックス値があります。
Defining Arrays
General Array Syntax
(expr, expr ... [,])
ARRAY(expr, ...)
Empty Arrays
()
(,)
ARRAY()
Single Element Arrays
(expr,)
ARRAY(expr)
Using Arrays
配列を扱う際の注意点をいくつか挙げておきます:
- すなわち、配列の最初の要素は
ARRAY[1]
として参照されなければなりません; - パーサが単一要素の式を括弧付きの配列として認識するためには、末尾のカンマが必要です;
- 配列のすべての要素が同じ型を持っている場合、配列は一致する基本型を持つことになります。要素の型が異なる場合、配列の基本型は
object
になります。
そして、配列の定義と使い方です:
BEGIN
// Defining an array
DECLARE
OBJECT favourite_birds = ARRAY(
'seagull'
,
'eagle'
,
'nightingale'
,
'sparrow'
);
// Using the array
SELECT
favourite_birds[2]
END
//
Output
eagle
CData Virtuality Server には配列を扱うための特別な関数もあります。Array Functions .
Aggregate Functions
Aggregate関数は、明示的または暗黙的なGROUP BYによって生成されたグループから値の設定を取り、グループから計算された単一のスカラー値を返します。
CData Virtuality Server は以下の集約機能をサポートしています:
To view the full table, click the expand button in its top right corner
Function | Description |
---|---|
| Counts the number of values (including nulls and duplicates) in a group |
| Counts number of values (excluding nulls) in a group |
| Sum of values (excluding nulls) in a group |
| Average of values (excluding nulls) in a group |
| Minimum value in a group (excluding null) |
| Maximum value in a group (excluding null) |
| Returns |
| Returns |
| Biased variance (excluding null) logically equals |
| Sample variance (excluding null) logically equals |
| Standard deviation (excluding null) logically equals |
| Sample standard deviation (excluding null) logically equals |
| CSV text aggregation of all expressions in each row of a group.
|
| XML concatenation of all XML expressions in a group (excluding null). The ORDER BY clause cannot reference alias names or use positional orderingSELECT XMLELEMENT( "Department" , XMLAGG(XMLELEMENT( "Names" , a.FirstName|| ' ' || a.LastName) ORDER BY LastName)) as "Name_List" FROM data_source.addresses a WHERE a.departmentID = 3; |
| Creates a JSON array result as a Clob including null value. The ORDER BY clause cannot reference alias names or use positional ordering. See also the JSONArray function.SELECT jsonArray_Agg(col1 order by col1 nulls first ) // The return may be as follows: [ null , null ,1,2,3] |
| Creates a lob result from the concatenation of x using the delimiter
string_agg(col1, ',' ORDER BY col1 ASC ) // The return may be as follows: 'a,b,c' |
| Creates an array with a base type matching the expression
SELECT array_agg(a ORDER BY b DESC ) FROM test_tables.test_a;; SELECT array_agg( "name" ) from "SYSADMIN.Connections" ;; |
| User-defined aggregate function |
Syntax Rules
一部の集約関数には、式の前にキーワード ' DISTINCT
' が含まれている場合があります。これは、重複する式の値は無視されるべきであることを示します。DISTINCT
はCOUNT(*)
では使用できず、MIN
MAX
では意味を持ちません(結果は変更されません)が、COUNT
、SUM
、AVG
では使用できます。
- 集約関数は、
FROM
、GROUP BY
、WHERE
、クエリ式が介在しない句では使用できません; - 集約関数は、クエリ式を挟まずに別の集約関数の中に入れ子にすることはできません;
- 集約関数は、他の関数の中に入れ子にすることができます;
任意の集約関数は、オプションで次の形式の
FILTER
句を取ることができます:FILTER ( WHERE condition )
条件は、サブクエリや相関変数を含まないブール値式であれば何でもかまいません。フィルタは、グループ化操作の前に、各行に対して論理的に評価されます。
FALSE
の場合、集約関数は指定された行の値を累積しません;- ユーザー定義の集約関数は、通常の関数ではなくAggregate であることを区別するために、他のAggregate 固有の構成要素が使用されていない場合、
ALL
を指定する必要があります。アグリゲートの詳細については、GROUP BY
、HAVING
。
Window Functions
CData Virtuality Server はANSI SQL 2003 Window 関数をサポートしています。Window関数は、GROUP BY
句を必要とせずに、結果セットのサブセットに集約関数を適用することを可能にします。Window 関数はAggregate関数と似ていますが、OVER
句またはWindow指定を使用する必要があります。
Usage
aggregate|ranking OVER ([PARTITION BY ...] [ORDER BY ...] [<FRAME-CLAUSE>])
aggregateは任意の集約関数です。順位は
ROW_NUMBER()
, RANK()
, DENSE_RANK()
のいずれかになります。
Syntax Rules
- Window 関数は、クエリ式の
SELECT
およびORDER BY
句でのみ使用できます; - Window 関数を入れ子にすることはできません;
- パーティショニングとorder by 式にサブクエリまたは外部参照を含めることはできません;
- ランキング(
ROW_NUMBER
,RANK
,DENSE_RANK
)関数は、ウィンドウ指定ORDER BY
句の使用を必要とします; XMLAGG ORDER BY
句は、ウィンドウの場合は使用できません;- ウィンドウ仕様
ORDER BY
句は、エイリアス名を参照したり、位置順序を使用したりすることはできません; - WINDOW 仕様が注文されている場合、WINDOW 集合体は
DISTINCT
を使用することはできません; - Frame句は、PARTITION句 (
PARTITION BY
、ORDER BY
のうち少なくとも1つが存在すること)と組み合わせてのみ使用できます。
Frame Clause
Frame句によって、ユーザはパーティション内の行の動的なグループ、もしくは範囲を指定することができます(ウィンドウのスライドフレームに似ています)。使い方は2通り:
[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end
frame_startおよび frame_endのいずれかを指定します:
UNBOUNDED PRECEDING
value PRECEDING (ROWS only)
CURRENT ROW
value FOLLOWING (ROWS only)
UNBOUNDED FOLLOWING
frame_startを UNBOUNDED FOLLOWING
にすることはできず、frame_end を UNBOUNDED PRECEDING
にすることはできず、frame_end をframe_start よりも前にすることはできません。例えば、RANGE BETWEEN CURRENT ROW AND
value PRECEDING
は許されません。
Examples of Usage
my_table
というテーブルがあるとします:
x | y |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 3 |
SELECT
x,
SUM
(y) OVER (PARTITION
BY
y
ROWS
BETWEEN
2 PRECEDING
AND
CURRENT
ROW)
AS
window_column
FROM
data_source.my_table;
-- Result:
-- |-----------|---------------|
-- | x | window_column |
-- |-----------|---------------|
-- | 1 | 1 |
-- | 2 | 2 |
-- | 3 | 3 |
-- | 4 | 2 |
-- | 5 | 3 |
-- |-----------|---------------|
SELECT
x,
COUNT
(y) OVER (PARTITION
BY
y RANGE
BETWEEN
CURRENT
ROW
AND
UNBOUNDED FOLLOWING)
AS
window_column
FROM
data_source.my_table;
-- Result:
-- |-----------|---------------|
-- | x | window_column |
-- |-----------|---------------|
-- | 1 | 3 |
-- | 2 | 3 |
-- | 3 | 3 |
-- | 4 | 1 |
-- | 5 | 1 |
-- |-----------|---------------|
SELECT
x, y*100/
SUM
(y) OVER (PARTITION
BY
y RANGE
BETWEEN
UNBOUNDED PRECEDING
AND
UNBOUNDED FOLLOWING)
AS
window_column
FROM
data_source.my_table;
-- Result:
-- |-----------|---------------|
-- | x | window_column |
-- |-----------|---------------|
-- | 1 | 33 |
-- | 2 | 33 |
-- | 3 | 33 |
-- | 4 | 100 |
-- | 5 | 100 |
-- |-----------|---------------|
Function Definitions
Function | Description |
---|---|
| Functionally same as |
| Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is equal to the count of prior rows |
| Assigns a number to each unique ordering value within each partition starting at 1, such that the next rank is sequential |
| Returns |
| Returns |
| Returns |
| Returns |
Processing
Window 関数は、SELECT
句からの出力を作成する直前に論理的に処理されます。GROUP BY
句がある場合、Window 関数は入れ子になった集約を使用することができます。Window関数の存在による出力順序への影響は保証されていません。SELECT
ステートメントには、ORDER BY
句がないと、順序が予測できません。
CData Virtuality Server は同じWindow 指定を持つすべての Window 関数を一緒に処理します。一般的に、SELECT
句に入力される行値に対する完全なパスは、各ユニークなウィンドウ指定に対して必要となります。各Window指定に対して、PARTITION BY
句に従って値がグループ化されます。PARTITION BY
句が指定されない場合、入力全体が1つのパーティションとして扱われます。出力値は、現在の行値、そのピア(つまり、順序に関して同じ行)、および、パーティション内の順序に基づくすべての前の行値に基づいて決定されます。ROW_NUMBER
関数は、ピアの数に関係なく、すべての行に一意の値を割り当てます。
SELECT
name
, salary,
max
(salary) over (partition
by
name
)
as
max_sal,
rank() over (
order
by
salary)
as
rank, dense_rank() over (
order
by
salary)
as
dense_rank,
row_number() over (
order
by
salary)
as
row_num
FROM
data_source.employees
name | salary | max_sal | rank | dense_rank | row_num |
---|---|---|---|---|---|
John | 100000 | 100000 | 2 | 2 | 2 |
Henry | 50000 | 50000 | 5 | 4 | 5 |
John | 60000 | 100000 | 3 | 3 | 3 |
Suzie | 60000 | 150000 | 3 | 3 | 4 |
Suzie | 150000 | 150000 | 1 | 1 | 1 |
See Also
Duplicate Removal with ROW_NUMBER() and PARTITION 重複を削除するためにWindow 関数がどのように使用できるかについては、こちらをご覧ください。
Case and Searched Case
CData Virtuality Server は、 CASE
、スカラー式の条件ロジックをサポートします。対応している形式は以下のとおりです:
CASE <expr> ( WHEN <expr> THEN <expr>)+ [ELSE expr] END
CASE ( WHEN <criteria> THEN <expr>)+ [ELSE expr] END
各フォームでは、条件ロジックに基づいた出力が可能です。最初の形式は初期式から始まり、WHEN
式を値が一致するまで評価し、THEN
式を出力します。WHEN
にマッチしない場合は、ELSE
式が出力されます。WHEN
にマッチせず、ELSE
も指定されない場合、NULL リテラル値が出力されます。2番目の形式(検索されたCASE式)は、WHEN
句を検索し、評価する任意の条件を指定します。いずれかのCriteriaがtrueと評価された場合、THEN
式が評価され、出力されます。WHEN
が真でない場合、ELSE
が評価され、NULL
が存在しない場合は出力されます。
Scalar Subqueries
サブクエリは、SELECT
、WHERE
、HAVING
、単一のスカラー値を生成するためにのみ使用できます。スカラーSubqueryは、SELECT
句に1つの列を持ち、0または1行を返す必要があります。行が返されない場合は、NULL がスカラーサブクエリの値として返されます。その他の種類の Subqueries については、Subqueries。
Parameter References
パラメータは '?' 記号で指定します。パラメータは、JDBC のPreparedStatement
またはCallableStatements
でのみ使用できます。各パラメータは、JDBC API の 1 ベースのインデックスで指定された値にリンクされています。