A <firstterm>table expression</firstterm> computes a table. The
table expression contains a <literal>FROM</literal> clause that is
optionally followed by <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
<literal>HAVING</literal> clauses. Trivial table expressions simply refer
to a table on disk, a so-called base table, but more complex
expressions can be used to modify or combine base tables in various
ways.
テーブル式はテーブルを計算するためのものです。
テーブル式にはFROM
句が含まれており、その後ろにオプションとしてWHERE
句、GROUP BY
句、HAVING
句を付けることができます。
単純なテーブル式は、単にディスク上のいわゆる基本テーブルと呼ばれるテーブルを参照するだけです。
しかし複雑な式では、様々な方法で基本テーブルを修正したり、結合させて使用することができます。
The optional <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
<literal>HAVING</literal> clauses in the table expression specify a
pipeline of successive transformations performed on the table
derived in the <literal>FROM</literal> clause. All these transformations
produce a virtual table that provides the rows that are passed to
the select list to compute the output rows of the query.
テーブル式のオプションWHERE
句、GROUP BY
句、およびHAVING
句は、FROM
句で派生したテーブル上に対して次々に変換を実行するパイプラインを指定します。
これらの変換によって仮想テーブルが1つ生成されます。
そしてこの仮想テーブルの行が選択リストに渡され、問い合わせの出力行が計算されます。
FROM
句 #
The <link linkend="sql-from"><literal>FROM</literal></link> clause derives a
table from one or more other tables given in a comma-separated
table reference list.
FROM
句は、カンマで分けられたテーブル参照リストで与えられる1つ以上のテーブルから、1つのテーブルを派生します。
FROMtable_reference
[,table_reference
[, ...]]
A table reference can be a table name (possibly schema-qualified),
or a derived table such as a subquery, a <literal>JOIN</literal> construct, or
complex combinations of these. If more than one table reference is
listed in the <literal>FROM</literal> clause, the tables are cross-joined
(that is, the Cartesian product of their rows is formed; see below).
The result of the <literal>FROM</literal> list is an intermediate virtual
table that can then be subject to
transformations by the <literal>WHERE</literal>, <literal>GROUP BY</literal>,
and <literal>HAVING</literal> clauses and is finally the result of the
overall table expression.
テーブル参照は、テーブル名(スキーマで修飾することもできます)、あるいは、副問い合わせ、JOIN
による結合、これらの複雑な組み合わせなどの派生テーブルとすることができます。
FROM
句に複数のテーブル参照がある場合、クロス結合されます(テーブルの行のデカルト積が形成されます。下記を参照)。
FROM
リストの結果はWHERE
句、GROUP BY
句、およびHAVING
句での変換対象となる中間的な仮想テーブルになり、最終的にはテーブル式全体の結果となります。
When a table reference names a table that is the parent of a
table inheritance hierarchy, the table reference produces rows of
not only that table but all of its descendant tables, unless the
key word <literal>ONLY</literal> precedes the table name. However, the
reference produces only the columns that appear in the named table
— any columns added in subtables are ignored.
テーブル参照で、テーブルの継承階層の親テーブルの名前を指定すると、テーブル名の前にONLY
キーワードがない場合は、テーブル参照はそのテーブルだけでなくその子テーブルに継承されたすべての行を生成します。
しかし、この参照は名前を指定したテーブルに現れる列のみを生成し、子テーブルで追加された列は無視されます。
Instead of writing <literal>ONLY</literal> before the table name, you can write
<literal>*</literal> after the table name to explicitly specify that descendant
tables are included. There is no real reason to use this syntax any more,
because searching descendant tables is now always the default behavior.
However, it is supported for compatibility with older releases.
テーブル名の前にONLY
を記述する代わりに、テーブル名の後に*
を記述して、子テーブルが含まれることを明示的に指定することができます。
子テーブルを検索するのが今は常にデフォルトの振る舞いですので、この文法を使う本当の理由はもうありません。
しかし、古いリリースとの互換性のためにサポートされています。
A joined table is a table derived from two other (real or derived) tables according to the rules of the particular join type. Inner, outer, and cross-joins are available. The general syntax of a joined table is 結合テーブルは、2つの(実または派生)テーブルから、指定した結合種類の規則に従って派生したテーブルです。 内部結合、外部結合、およびクロス結合が使用可能です。 テーブル結合の一般的な構文は次のとおりです
T1
join_type
T2
[join_condition
]
Joins of all types can be chained together, or nested: either or
both <replaceable>T1</replaceable> and
<replaceable>T2</replaceable> can be joined tables. Parentheses
can be used around <literal>JOIN</literal> clauses to control the join
order. In the absence of parentheses, <literal>JOIN</literal> clauses
nest left-to-right.
すべての結合は、互いに結び付けたり、あるいは入れ子にしたりすることができます。
T1
とT2
のどちらか、あるいは両方が、結合テーブルになることがあります。
括弧でJOIN
句を括ることで結合の順序を制御することができます。
括弧がない場合、JOIN
句は左から右に入れ子にします。
結合の種類
<title>Join Types</title>T1
CROSS JOINT2
For every possible combination of rows from
<replaceable>T1</replaceable> and
<replaceable>T2</replaceable> (i.e., a Cartesian product),
the joined table will contain a
row consisting of all columns in <replaceable>T1</replaceable>
followed by all columns in <replaceable>T2</replaceable>. If
the tables have N and M rows respectively, the joined
table will have N * M rows.
T1
およびT2
からのすべての可能な行の組み合わせ(つまりデカルト積)に対し、結合されたテーブルはT1
のすべての列の後にT2
のすべての列が続く行を含みます。
テーブルがそれぞれN行とM行で構成されているとすると、結合されたテーブルの行数は N * M 行となります。
<literal>FROM <replaceable>T1</replaceable> CROSS JOIN
<replaceable>T2</replaceable></literal> is equivalent to
<literal>FROM <replaceable>T1</replaceable> INNER JOIN
<replaceable>T2</replaceable> ON TRUE</literal> (see below).
It is also equivalent to
<literal>FROM <replaceable>T1</replaceable>,
<replaceable>T2</replaceable></literal>.
FROM
は T1
CROSS JOIN T2
FROM
と同じです(下記を参照)。
また T1
INNER JOIN T2
ON TRUEFROM
とも同じです。
T1
, T2
This latter equivalence does not hold exactly when more than two
tables appear, because <literal>JOIN</literal> binds more tightly than
comma. For example
<literal>FROM <replaceable>T1</replaceable> CROSS JOIN
<replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
ON <replaceable>condition</replaceable></literal>
is not the same as
<literal>FROM <replaceable>T1</replaceable>,
<replaceable>T2</replaceable> INNER JOIN <replaceable>T3</replaceable>
ON <replaceable>condition</replaceable></literal>
because the <replaceable>condition</replaceable> can
reference <replaceable>T1</replaceable> in the first case but not
the second.
3つ以上のテーブルが現れた場合、この後者の等価性は厳密には保たれてはいません。
なぜなら、JOIN
はカンマより強固に結合するためです。
例えば
FROM
は
T1
CROSS JOIN
T2
INNER JOIN T3
ON condition
FROM
と同じではありません。
なぜなら最初のケースではT1
,
T2
INNER JOIN T3
ON condition
condition
がT1
を参照できますが、2番目ではできないからです。
T1
{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
ONboolean_expression
T1
{ [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
USING (join column list
)T1
NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOINT2
The words <literal>INNER</literal> and
<literal>OUTER</literal> are optional in all forms.
<literal>INNER</literal> is the default;
<literal>LEFT</literal>, <literal>RIGHT</literal>, and
<literal>FULL</literal> imply an outer join.
INNER
やOUTER
は省略可能です。
INNER
がデフォルトとなります。
LEFT
、RIGHT
、FULL
は外部結合を意味します。
The <firstterm>join condition</firstterm> is specified in the
<literal>ON</literal> or <literal>USING</literal> clause, or implicitly by
the word <literal>NATURAL</literal>. The join condition determines
which rows from the two source tables are considered to
<quote>match</quote>, as explained in detail below.
結合条件は、ON
句かUSING
句で指定するか、またはNATURAL
記述で暗黙的に指定します。
結合条件は、以下で詳しく説明するように、2つの元となるテーブルのどの行が「一致するか」を決めます。
The possible types of qualified join are: 限定的な結合には次のものがあります。
INNER JOIN
(内部結合)For each row R1 of T1, the joined table has a row for each row in T2 that satisfies the join condition with R1. T1の各行R1に対して、T2において行R1との結合条件を満たしている各行が、結合されたテーブルに含まれます。
LEFT OUTER JOIN
(左外部結合)
First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1. まず、内部結合が行われます。 その後、T2のどの行との結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 したがって、連結されたテーブルは常にT1の行それぞれに少なくとも1つの行があります。
RIGHT OUTER JOIN
(右外部結合)
First, an inner join is performed. Then, for each row in T2 that does not satisfy the join condition with any row in T1, a joined row is added with null values in columns of T1. This is the converse of a left join: the result table will always have a row for each row in T2. まず、内部結合が行われます。 その後、T1のどの行の結合条件も満たさないT2の各行については、T1の列をNULL値として結合行が追加されます。 これは左結合の反対です。 結果のテーブルは、T2の行が常に入ります。
FULL OUTER JOIN
(完全外部結合)First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row is added with null values in columns of T2. Also, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added. まず、内部結合が行われます。 その後、T2のどの行の結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 さらに、T1のどの行でも結合条件を満たさないT2の各行に対して、T1の列をNULL値として結合行が追加されます。
The <literal>ON</literal> clause is the most general kind of join
condition: it takes a Boolean value expression of the same
kind as is used in a <literal>WHERE</literal> clause. A pair of rows
from <replaceable>T1</replaceable> and <replaceable>T2</replaceable> match if the
<literal>ON</literal> expression evaluates to true.
ON
句は最も汎用的な結合条件であり、WHERE
句で使われるものと同じ論理値評価式となります。
ON
式の評価が真となる場合、T1
およびT2
の対応する行が一致します。
The <literal>USING</literal> clause is a shorthand that allows you to take
advantage of the specific situation where both sides of the join use
the same name for the joining column(s). It takes a
comma-separated list of the shared column names
and forms a join condition that includes an equality comparison
for each one. For example, joining <replaceable>T1</replaceable>
and <replaceable>T2</replaceable> with <literal>USING (a, b)</literal> produces
the join condition <literal>ON <replaceable>T1</replaceable>.a
= <replaceable>T2</replaceable>.a AND <replaceable>T1</replaceable>.b
= <replaceable>T2</replaceable>.b</literal>.
USING
句は、結合の両側で結合列に同じ名前を使っているという特別な状況の利点を活かすことができる省略形です。
それは、結合テーブルが共通で持つ列名をカンマで区切ったリストから、それぞれの列の等価性を結合条件として生成します。
例えば, T1
とT2
をUSING (a, b)
を使用して結合する場合は、ON
という結合条件を生成します。
T1
.a = T2
.a AND T1
.b = T2
.b
Furthermore, the output of <literal>JOIN USING</literal> suppresses
redundant columns: there is no need to print both of the matched
columns, since they must have equal values. While <literal>JOIN
ON</literal> produces all columns from <replaceable>T1</replaceable> followed by all
columns from <replaceable>T2</replaceable>, <literal>JOIN USING</literal> produces one
output column for each of the listed column pairs (in the listed
order), followed by any remaining columns from <replaceable>T1</replaceable>,
followed by any remaining columns from <replaceable>T2</replaceable>.
さらに、JOIN USING
の出力は、冗長列を抑制します。マッチした列は両方が同じ値を待つので両方を出力する必要がありません。
JOIN ON
は T1
からのすべての列と、それに続く T2
からのすべての列を生成します。
JOIN USING
は指定された列のペアのそれぞれについて1つの出力(結合リストでの指定順)、続いてT1
の残りの列、その後にT2
の残りの列を出力します。
Finally, <literal>NATURAL</literal> is a shorthand form of
<literal>USING</literal>: it forms a <literal>USING</literal> list
consisting of all column names that appear in both
input tables. As with <literal>USING</literal>, these columns appear
only once in the output table. If there are no common
column names, <literal>NATURAL JOIN</literal> behaves like
<literal>CROSS JOIN</literal>.
《マッチ度[73.264781]》最後に、NATURAL
はUSING
の略記形式で、2つの入力テーブルの両方に含まれているすべての列名で構成されるUSING
リストを形成します。
USING
と同様、これらの列は出力テーブルに一度だけ現れます。
共通する列が存在しない場合、NATURAL JOIN
はJOIN ... ON TRUE
と同様に動作し、クロス積結合を生成します。
《機械翻訳》最後に、NATURAL
はUSING
の省略形フォームです。
これは、両方の入力テーブルに表示されるすべてのリスト名で構成されるUSING
カラムを形成します。
USING
と同様に、これらの列は出力テーブルに1回だけ表示されます。
共通のカラム名がない場合、自然結合
はクロス結合
のように動作します。
<literal>USING</literal> is reasonably safe from column changes
in the joined relations since only the listed columns
are combined. <literal>NATURAL</literal> is considerably more risky since
any schema changes to either relation that cause a new matching
column name to be present will cause the join to combine that new
column as well.
USING
は、リストされている列のみ結合するのでリレーションの列の変更から適度に安全です。
NATURAL
は、USING
よりもかなり危険です。
いずれかのリレーションのスキーマ変更により新しくマッチする列名が作られると、結合にその新しい列も使われるようになってしまうからです。
To put this together, assume we have tables <literal>t1</literal>:
まとめとして、 以下のテーブルt1
num | name -----+------ 1 | a 2 | b 3 | c
and <literal>t2</literal>:
および、テーブルt2
num | value -----+------- 1 | xxx 3 | yyy 5 | zzz
then we get the following results for the various joins: を想定すると、以下のように様々な結合に関する結果が得られます。
=>
SELECT * FROM t1 CROSS JOIN t2;
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 1 | a | 3 | yyy 1 | a | 5 | zzz 2 | b | 1 | xxx 2 | b | 3 | yyy 2 | b | 5 | zzz 3 | c | 1 | xxx 3 | c | 3 | yyy 3 | c | 5 | zzz (9 rows)=>
SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 rows)=>
SELECT * FROM t1 INNER JOIN t2 USING (num);
num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows)=>
SELECT * FROM t1 NATURAL INNER JOIN t2;
num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows)=>
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 rows)=>
SELECT * FROM t1 LEFT JOIN t2 USING (num);
num | name | value -----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy (3 rows)=>
SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz (3 rows)=>
SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy | | 5 | zzz (4 rows)
The join condition specified with <literal>ON</literal> can also contain
conditions that do not relate directly to the join. This can
prove useful for some queries but needs to be thought out
carefully. For example:
ON
で指定される結合条件には、結合に直接関係しない条件も含めることができます。
これは一部の問い合わせにおいては便利ですが、使用の際には注意が必要です。
例を示します。
=>
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | | (3 rows)
Notice that placing the restriction in the <literal>WHERE</literal> clause
produces a different result:
WHERE
句の中に制約を記述すると異なる結果になることに注意してください。
=>
SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx (1 row)
This is because a restriction placed in the <literal>ON</literal>
clause is processed <emphasis>before</emphasis> the join, while
a restriction placed in the <literal>WHERE</literal> clause is processed
<emphasis>after</emphasis> the join.
That does not matter with inner joins, but it matters a lot with outer
joins.
この理由はON
句の中の制約は結合の前に処理され、一方WHERE
句の中の制約は結合の後に処理されることによります。
これは内部結合には影響がありませんが、外部結合には大きな影響があります。
A temporary name can be given to tables and complex table references to be used for references to the derived table in the rest of the query. This is called a <firstterm>table alias</firstterm>. テーブルや複雑なテーブル参照に一時的な名前を付与し、問い合わせの以降の部分では、その名前を使ってテーブルや複雑なテーブル参照を利用することができます。 これをテーブルの別名と呼びます。
To create a table alias, write テーブルの別名を作成するには以下のようにします。
FROMtable_reference
ASalias
or もしくは
FROMtable_reference
alias
The <literal>AS</literal> key word is optional noise.
<replaceable>alias</replaceable> can be any identifier.
AS
キーワードはなくても構わないノイズです。
alias
は任意の識別子になります。
A typical application of table aliases is to assign short identifiers to long table names to keep the join clauses readable. For example: テーブルの別名の一般的な適用法は、長いテーブル名に短縮した識別子を割り当てて結合句を読みやすくすることです。 例を示します。
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
The alias becomes the new name of the table reference so far as the current query is concerned — it is not allowed to refer to the table by the original name elsewhere in the query. Thus, this is not valid: 現在の問い合わせに関しては、別名がテーブル参照をする時の新しい名前になります。 問い合わせの他の場所で元々の名前でテーブルを参照することはできなくなります。 よって、次の例は有効ではありません。
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- 間違い
Table aliases are mainly for notational convenience, but it is necessary to use them when joining a table to itself, e.g.: テーブルの別名は主に表記を簡単にするためにあります。 しかし次のように、1つのテーブルが自分自身と結合する場合は、必須となります。
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
Parentheses are used to resolve ambiguities. In the following example,
the first statement assigns the alias <literal>b</literal> to the second
instance of <literal>my_table</literal>, but the second statement assigns the
alias to the result of the join:
括弧は曖昧さをなくすために使われます。
次の例では、最初の文で2つ目のmy_table
のインスタンスにb
という別名を付与し、一方、2つ目の文では結合結果に対して別名を付与しています。
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ... SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself: 次のような形式でテーブル別名を付けて、テーブル自身と同様にテーブルの列に一時的な名前を付けることができます。
FROMtable_reference
[AS]alias
(column1
[,column2
[, ...]] )
If fewer column aliases are specified than the actual table has columns, the remaining columns are not renamed. This syntax is especially useful for self-joins or subqueries. もし、実際のテーブルが持つ列よりも少ない数の列の別名が与えられる場合、残りの列は改名されません。 この構文は、自己結合あるいは副問い合わせで特に役立ちます。
When an alias is applied to the output of a <literal>JOIN</literal>
clause, the alias hides the original
name(s) within the <literal>JOIN</literal>. For example:
別名がJOIN
句の結果に適用される場合、別名はJOIN
内で参照される元々の名を隠します。
以下に例を示します。
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
is valid SQL, but: は有効なSQLですが、
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
is not valid; the table alias <literal>a</literal> is not visible
outside the alias <literal>c</literal>.
は有効ではありません。
テーブルの別名a
は、別名c
の外側では参照することができません。
Subqueries specifying a derived table must be enclosed in parentheses. They may be assigned a table alias name, and optionally column alias names (as in <xref linkend="queries-table-aliases"/>). For example: 派生テーブルを指定する副問い合わせは括弧で囲む必要があります。 テーブルの別名、およびオプションで列の別名を(7.2.1.2にあるように)割り当てることができます。 例を示します。
FROM (SELECT * FROM table1) AS alias_name
This example is equivalent to <literal>FROM table1 AS
alias_name</literal>. More interesting cases, which cannot be
reduced to a plain join, arise when the subquery involves
grouping or aggregation.
この例はFROM table1 AS alias_name
と同じです。
副問い合わせがグループ化や集約を含んでいる場合は、単純結合にまとめることはできない、より重要な例が発生します。
A subquery can also be a <command>VALUES</command> list:
また、副問い合わせをVALUES
リストとすることもできます。
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow')) AS names(first, last)
Again, a table alias is optional. Assigning alias names to the columns
of the <command>VALUES</command> list is optional, but is good practice.
For more information see <xref linkend="queries-values"/>.
ここでも、テーブルの別名は省略できます。
VALUES
リストの列に別名を付与することは省略することもできますが、付与することを勧めます。
詳細は7.7を参照してください。
According to the SQL standard, a table alias name must be supplied
for a subquery. <productname>PostgreSQL</productname>
allows <literal>AS</literal> and the alias to be omitted, but
writing one is good practice in SQL code that might be ported to
another system.
SQL標準によれば、副問い合わせにはテーブル別名を指定する必要があります。
PostgreSQLでは、AS
と別名を省略できますが、別のシステムに移植する可能性があるSQLコードでは、別名を書くことをお勧めします。
Table functions are functions that produce a set of rows, made up
of either base data types (scalar types) or composite data types
(table rows). They are used like a table, view, or subquery in
the <literal>FROM</literal> clause of a query. Columns returned by table
functions can be included in <literal>SELECT</literal>,
<literal>JOIN</literal>, or <literal>WHERE</literal> clauses in the same manner
as columns of a table, view, or subquery.
テーブル関数は、基本データ型(スカラ型)、もしくは複合データ型(テーブル行)からなる行の集合を生成する関数です。
これらは、問い合わせのFROM
句内でテーブル、ビュー、副問い合わせのように使用されます。
テーブル関数から返される列は、テーブル、ビュー、副問い合わせの列と同様の手順で、SELECT
、JOIN
、WHERE
の中に含めることができます。
Table functions may also be combined using the <literal>ROWS FROM</literal>
syntax, with the results returned in parallel columns; the number of
result rows in this case is that of the largest function result, with
smaller results padded with null values to match.
テーブル関数はROWS FROM
構文を使用することで、それらの返却列を一緒に組み合わせることもできます。
このときの結果の行数は、行数が最大となる関数の結果と同じになり、少ない結果側は多い結果に合わせてnull値で埋められます。
function_call
[WITH ORDINALITY] [[AS]table_alias
[(column_alias
[, ... ])]] ROWS FROM(function_call
[, ... ] ) [WITH ORDINALITY] [[AS]table_alias
[(column_alias
[, ... ])]]
If the <literal>WITH ORDINALITY</literal> clause is specified, an
additional column of type <type>bigint</type> will be added to the
function result columns. This column numbers the rows of the function
result set, starting from 1. (This is a generalization of the
SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.)
By default, the ordinal column is called <literal>ordinality</literal>, but
a different column name can be assigned to it using
an <literal>AS</literal> clause.
WITH ORDINALITY
句が指定されている場合、関数の結果の列にbigint
型の列が追加されます。
この列は関数の結果の行を1から数えます。
(これは標準SQLの構文UNNEST ... WITH ORDINALITY
の一般化です。)
デフォルトでは、この序数(ordinal)の列はordinality
になります。しかし別の名前をAS
句を使用して別名を付与できます。
The special table function <literal>UNNEST</literal> may be called with
any number of array parameters, and it returns a corresponding number of
columns, as if <literal>UNNEST</literal>
(<xref linkend="functions-array"/>) had been called on each parameter
separately and combined using the <literal>ROWS FROM</literal> construct.
特別なテーブル関数UNNEST
は、任意の数の配列パラメータで呼ぶことができます。
そしてそれは、対応する数の列を返し、あたかもUNNEST
(9.19)が各パラメータ毎にROWS FROM
構文を使用して結合されているかのようになります。
UNNEST(array_expression
[, ... ] ) [WITH ORDINALITY] [[AS]table_alias
[(column_alias
[, ... ])]]
If no <replaceable>table_alias</replaceable> is specified, the function
name is used as the table name; in the case of a <literal>ROWS FROM()</literal>
construct, the first function's name is used.
table_alias
が指定されない場合、テーブル名として関数名が使用されます。
ROWS FROM()
の場合は最初の関数名が使用されます。
If column aliases are not supplied, then for a function returning a base data type, the column name is also the same as the function name. For a function returning a composite type, the result columns get the names of the individual attributes of the type. 列に別名が提供されない場合、基本データ型を返す関数に対しては、列名も関数名と同じになります。 複合型を返す関数の場合は、結果の列は型の個々の属性の名前を取得します。
Some examples: 以下に数例示します。
CREATE TABLE foo (fooid int, foosubid int, fooname text); CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ SELECT * FROM foo WHERE fooid = $1; $$ LANGUAGE SQL; SELECT * FROM getfoo(1) AS t1; SELECT * FROM foo WHERE foosubid IN ( SELECT foosubid FROM getfoo(foo.fooid) z WHERE z.fooid = foo.fooid ); CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1); SELECT * FROM vw_getfoo;
In some cases it is useful to define table functions that can
return different column sets depending on how they are invoked.
To support this, the table function can be declared as returning
the pseudo-type <type>record</type> with no <literal>OUT</literal>
parameters. When such a function is used in
a query, the expected row structure must be specified in the
query itself, so that the system can know how to parse and plan
the query. This syntax looks like:
呼び出し方法に応じて異なる列集合を返すテーブル関数を定義することが役に立つ場合があります。
これをサポートするために、テーブル関数はOUT
パラメータを持たないrecord
擬似型を返すものと宣言することができます。
こうした関数を問い合わせで使用する場合、システムがその問い合わせをどのように解析し計画を作成すればよいのかが判断できるように、想定した行構造を問い合わせ自身内に指定しなければなりません。
この構文は次のようになります。
function_call
[AS]alias
(column_definition
[, ... ])function_call
AS [alias
] (column_definition
[, ... ]) ROWS FROM( ...function_call
AS (column_definition
[, ... ]) [, ... ] )
When not using the <literal>ROWS FROM()</literal> syntax,
the <replaceable>column_definition</replaceable> list replaces the column
alias list that could otherwise be attached to the <literal>FROM</literal>
item; the names in the column definitions serve as column aliases.
When using the <literal>ROWS FROM()</literal> syntax,
a <replaceable>column_definition</replaceable> list can be attached to
each member function separately; or if there is only one member function
and no <literal>WITH ORDINALITY</literal> clause,
a <replaceable>column_definition</replaceable> list can be written in
place of a column alias list following <literal>ROWS FROM()</literal>.
ROWS FROM()
構文を使用しない場合は、column_definition
のリストがFROM
項目に取り付けることができる列の別名の代わりとなります。
列の定義内の名前は、列の別名として機能します。
ROWS FROM()
構文を使用する場合は、column_definition
リストを個別に各メンバ関数に添付することができます。
またはメンバ関数が1つだけしかなく、かつWITH ORDINALITY
句がない場合は、column_definition
リストを、ROWS FROM()
の後ろの列別名のリストの場所に書くことができます。
Consider this example: 以下の例を考えます。
SELECT * FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc') AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
The <xref linkend="contrib-dblink-function"/> function
(part of the <xref linkend="dblink"/> module) executes
a remote query. It is declared to return
<type>record</type> since it might be used for any kind of query.
The actual column set must be specified in the calling query so
that the parser knows, for example, what <literal>*</literal> should
expand to.
dblink関数(dblinkモジュールの一部)は遠隔問い合わせを実行します。
これは任意の問い合わせで使用できるように、record
を返すものと宣言されています。
実際の列集合は、パーサが例えば*
がどのように展開されるかを理解できるように、呼び出した問い合わせ内で指定されなければなりません。
This example uses <literal>ROWS FROM</literal>:
ROWS FROM
を使用した例:
SELECT * FROM ROWS FROM ( json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]') AS (a INTEGER, b TEXT), generate_series(1, 3) ) AS x (p, q, s) ORDER BY p; p | q | s -----+-----+--- 40 | foo | 1 100 | bar | 2 | | 3
It joins two functions into a single <literal>FROM</literal>
target. <function>json_to_recordset()</function> is instructed
to return two columns, the first <type>integer</type>
and the second <type>text</type>. The result of
<function>generate_series()</function> is used directly.
The <literal>ORDER BY</literal> clause sorts the column values
as integers.
2つの関数を結合して1つのFROM
ターゲットにします。
json_to_recordset()
は、2つの列(最初のinteger
と2番目のtext
)を返すように指示されます。
generate_series()
の結果は直接使用されます。
ORDER BY
句では、列値が整数として並べ替えられます。
LATERAL
副問い合わせ #
Subqueries appearing in <literal>FROM</literal> can be
preceded by the key word <literal>LATERAL</literal>. This allows them to
reference columns provided by preceding <literal>FROM</literal> items.
(Without <literal>LATERAL</literal>, each subquery is
evaluated independently and so cannot cross-reference any other
<literal>FROM</literal> item.)
FROM
に現れる副問い合わせの前にキーワードLATERAL
を置くことができます。
こうすると、副問い合わせは先行するFROM
項目によって提供される列を参照できます。
(LATERAL
がない場合、それぞれの副問い合わせは個別に評価され、従ってその他のFROM
項目を相互参照できません。)
Table functions appearing in <literal>FROM</literal> can also be
preceded by the key word <literal>LATERAL</literal>, but for functions the
key word is optional; the function's arguments can contain references
to columns provided by preceding <literal>FROM</literal> items in any case.
FROM
に現れるテーブル関数の前にもキーワードLATERAL
を置くことが可能ですが、関数に対してこのキーワードは省略可能です。
どんな場合であっても、関数の引数は先行する FROM
項目により提供される列の参照を含むことができます。
A <literal>LATERAL</literal> item can appear at the top level in the
<literal>FROM</literal> list, or within a <literal>JOIN</literal> tree. In the latter
case it can also refer to any items that are on the left-hand side of a
<literal>JOIN</literal> that it is on the right-hand side of.
LATERAL
項目はFROM
リストの最上層、またはJOIN
ツリーの中で表示することができます。
後者の場合、右側にあるJOIN
の左側のすべての項目を参照することが可能です。
When a <literal>FROM</literal> item contains <literal>LATERAL</literal>
cross-references, evaluation proceeds as follows: for each row of the
<literal>FROM</literal> item providing the cross-referenced column(s), or
set of rows of multiple <literal>FROM</literal> items providing the
columns, the <literal>LATERAL</literal> item is evaluated using that
row or row set's values of the columns. The resulting row(s) are
joined as usual with the rows they were computed from. This is
repeated for each row or set of rows from the column source table(s).
FROM
項目がLATERAL
相互参照を含む場合の評価は以下のようになります。
相互参照される列(複数可)を提供するFROM
項目のそれぞれの行、もしくは列を提供する複数のFROM
項目の行一式に対し、LATERAL
項目は列の行または複数行の一式の値により評価されます。
結果行(複数可)は通常のように演算された行と結合されます。
元となるテーブル(複数可)の列からそれぞれの行、または行の一式に対し反復されます。
A trivial example of <literal>LATERAL</literal> is
LATERAL
の些細な例としては以下があげられます。
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
This is not especially useful since it has exactly the same result as the more conventional 上記は以下のより伝統的なやり方と全く同じ結果をもたらしますので特別に有用ではありません。
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
<literal>LATERAL</literal> is primarily useful when the cross-referenced
column is necessary for computing the row(s) to be joined. A common
application is providing an argument value for a set-returning function.
For example, supposing that <function>vertices(polygon)</function> returns the
set of vertices of a polygon, we could identify close-together vertices
of polygons stored in a table with:
LATERAL
は、結合される行を計算するために相互参照する列を必須とする場合、第一義的に有用です。
一般的な利用方法は、集合を返す関数に対して引数の値を提供することです。
例えば、vertices(polygon)
が多角形の頂点の組みを返す関数だとして、以下のようにしてテーブルに格納されている多角形の互いに近接する頂点を特定できます。
SELECT p1.id, p2.id, v1, v2 FROM polygons p1, polygons p2, LATERAL vertices(p1.poly) v1, LATERAL vertices(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
This query could also be written この問い合わせは以下のようにも書くことができます。
SELECT p1.id, p2.id, v1, v2 FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1, polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
or in several other equivalent formulations. (As already mentioned,
the <literal>LATERAL</literal> key word is unnecessary in this example, but
we use it for clarity.)
そのほか幾つかの同等の定式化が考えられます。
(既に言及したとおり、LATERAL
キーワードはこの例に於いて必要ではありませんが、明確に示すために使用しました。)
It is often particularly handy to <literal>LEFT JOIN</literal> to a
<literal>LATERAL</literal> subquery, so that source rows will appear in
the result even if the <literal>LATERAL</literal> subquery produces no
rows for them. For example, if <function>get_product_names()</function> returns
the names of products made by a manufacturer, but some manufacturers in
our table currently produce no products, we could find out which ones
those are like this:
LATERAL
副問い合わせはLEFT JOIN
の対象として、しばしば特に重宝します。
たとえLATERAL
副問い合わせがそこから行を生成しない場合に於いても元となった行が結果に現れるからです。
たとえば、get_product_names()
が製造者により生産された製品名を返すとして、テーブル内のいくつかの製造者が現在製品を製造していない場合、それらは何であるかを以下のようにして見つけることができます。
SELECT m.name FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true WHERE pname IS NULL;
WHERE
句 #
The syntax of the <link linkend="sql-where"><literal>WHERE</literal></link>
clause is
WHERE
句の構文は以下の通りです。
WHERE search_condition
where <replaceable>search_condition</replaceable> is any value
expression (see <xref linkend="sql-expressions"/>) that
returns a value of type <type>boolean</type>.
ここで、search_condition
にはboolean
型を返すどのような評価式(4.2を参照)も指定できます。
After the processing of the <literal>FROM</literal> clause is done, each
row of the derived virtual table is checked against the search
condition. If the result of the condition is true, the row is
kept in the output table, otherwise (i.e., if the result is
false or null) it is discarded. The search condition typically
references at least one column of the table generated in the
<literal>FROM</literal> clause; this is not required, but otherwise the
<literal>WHERE</literal> clause will be fairly useless.
FROM
句の処理が終わった後、派生した仮想テーブルの各行は検索条件と照合されます。
条件の結果が真の場合、その行は出力されます。
そうでない(すなわち結果が偽またはNULLの)場合は、その行は捨てられます。
一般的に検索条件は、FROM
句で生成されたテーブルの最低1列を参照します。
これは必須ではありませんが、そうしないとWHERE
句はまったく意味がなくなります。
The join condition of an inner join can be written either in
the <literal>WHERE</literal> clause or in the <literal>JOIN</literal> clause.
For example, these table expressions are equivalent:
内部結合の結合条件は、WHERE
句でもJOIN
句でも記述することができます。
例えば、以下のテーブル式は等価です。
FROM a, b WHERE a.id = b.id AND b.val > 5
and: および
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
or perhaps even: また、以下でも同じです。
FROM a NATURAL JOIN b WHERE b.val > 5
Which one of these you use is mainly a matter of style. The
<literal>JOIN</literal> syntax in the <literal>FROM</literal> clause is
probably not as portable to other SQL database management systems,
even though it is in the SQL standard. For
outer joins there is no choice: they must be done in
the <literal>FROM</literal> clause. The <literal>ON</literal> or <literal>USING</literal>
clause of an outer join is <emphasis>not</emphasis> equivalent to a
<literal>WHERE</literal> condition, because it results in the addition
of rows (for unmatched input rows) as well as the removal of rows
in the final result.
どれを使うかは、主にスタイルの問題です。
FROM
句のJOIN
構文はSQL標準であるにも関わらず、おそらく他のSQLデータベース管理システムへの移植性では劣るでしょう。
外部結合については、FROM
句以外に選択の余地はありません。
外部結合のON
句またはUSING
句は、WHERE
条件とは等しくありません。
なぜなら、最終結果での行を除去すると同様に、(一致しない入力行に対する)行の追加となるからです。
Here are some examples of <literal>WHERE</literal> clauses:
WHERE
句の例を以下に示します。
SELECT ... FROM fdt WHERE c1 > 5 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3) SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2) SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
<literal>fdt</literal> is the table derived in the
<literal>FROM</literal> clause. Rows that do not meet the search
condition of the <literal>WHERE</literal> clause are eliminated from
<literal>fdt</literal>. Notice the use of scalar subqueries as
value expressions. Just like any other query, the subqueries can
employ complex table expressions. Notice also how
<literal>fdt</literal> is referenced in the subqueries.
Qualifying <literal>c1</literal> as <literal>fdt.c1</literal> is only necessary
if <literal>c1</literal> is also the name of a column in the derived
input table of the subquery. But qualifying the column name adds
clarity even when it is not needed. This example shows how the column
naming scope of an outer query extends into its inner queries.
fdt
はFROM
句で派生されたテーブルです。
WHERE
句の検索条件を満たさなかった行は、fdt
から削除されます。
評価式としてのスカラ副問い合わせの使い方に注目してください。
他の問い合わせのように、副問い合わせは複雑なテーブル式を使うことができます。
副問い合わせの中でどのようにfdt
が参照されるかにも注意してください。
c1
をfdt.c1
のように修飾することは、c1
が副問い合わせの入力テーブルから派生した列名でもある時にだけ必要です。
列名の修飾は、必須の場合ではなくても、明確にするために役立ちます。
この例は、外側の問い合わせの列名の有効範囲を、どのようにして内側の問い合わせまで拡張するかを示します。
GROUP BY
句とHAVING
句 #
After passing the <literal>WHERE</literal> filter, the derived input
table might be subject to grouping, using the <literal>GROUP BY</literal>
clause, and elimination of group rows using the <literal>HAVING</literal>
clause.
WHERE
フィルタを通した後、派生された入力テーブルをGROUP BY
句でグループ化し、また、HAVING
句を使用して不要なグループを取り除くことができます。
SELECTselect_list
FROM ... [WHERE ...] GROUP BYgrouping_column_reference
[,grouping_column_reference
]...
The <link linkend="sql-groupby"><literal>GROUP BY</literal></link> clause is
used to group together those rows in a table that have the same
values in all the columns listed. The order in which the columns
are listed does not matter. The effect is to combine each set
of rows having common values into one group row that
represents all rows in the group. This is done to
eliminate redundancy in the output and/or compute aggregates that
apply to these groups. For instance:
GROUP BY
句は、列挙されたすべての列で同じ値を所有する行をまとめてグループ化するために使用されます。
列の列挙順は関係ありません。
これは共通する値を持つそれぞれの行の集合をグループ内のすべての行を代表する1つのグループ行にまとめるものです。
これは、出力の冗長度を排除したり、それぞれのグループに適用される集約計算を行うためのものです。
以下に例を示します。
=>
SELECT * FROM test1;
x | y ---+--- a | 3 c | 2 b | 5 a | 1 (4 rows)=>
SELECT x FROM test1 GROUP BY x;
x --- a b c (3 rows)
In the second query, we could not have written <literal>SELECT *
FROM test1 GROUP BY x</literal>, because there is no single value
for the column <literal>y</literal> that could be associated with each
group. The grouped-by columns can be referenced in the select list since
they have a single value in each group.
2番目の問い合わせでは、SELECT * FROM test1 GROUP BY x
と書くことはできません。
各グループに関連付けられる列y
の単一の値がないからです。
GROUP BY
で指定した列はグループごとに単一の値を持つので、選択リストで参照することができます。
In general, if a table is grouped, columns that are not
listed in <literal>GROUP BY</literal> cannot be referenced except in aggregate
expressions. An example with aggregate expressions is:
一般的に、テーブルがグループ化されている場合、GROUP BY
でリストされていない列は集約式を除いて参照することはできません。
集約式の例は以下の通りです。
=>
SELECT x, sum(y) FROM test1 GROUP BY x;
x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)
Here <literal>sum</literal> is an aggregate function that
computes a single value over the entire group. More information
about the available aggregate functions can be found in <xref
linkend="functions-aggregate"/>.
上記でsum()
は、グループ全体について単一の値を計算する集約関数です。
使用可能な集約関数の詳細については、9.21を参照してください。
Grouping without aggregate expressions effectively calculates the
set of distinct values in a column. This can also be achieved
using the <literal>DISTINCT</literal> clause (see <xref
linkend="queries-distinct"/>).
集約式を使用しないグループ化は、列内の重複しない値の集合を効率良く計算します。
これはDISTINCT
句(7.3.3を参照)の使用で同じように達成することができます。
Here is another example: it calculates the total sales for each product (rather than the total sales of all products): 別の例を示します。 これは各製品の総売上を計算します (全製品の総売上ではありません)。
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales FROM products p LEFT JOIN sales s USING (product_id) GROUP BY product_id, p.name, p.price;
In this example, the columns <literal>product_id</literal>,
<literal>p.name</literal>, and <literal>p.price</literal> must be
in the <literal>GROUP BY</literal> clause since they are referenced in
the query select list (but see below). The column
<literal>s.units</literal> does not have to be in the <literal>GROUP
BY</literal> list since it is only used in an aggregate expression
(<literal>sum(...)</literal>), which represents the sales
of a product. For each product, the query returns a summary row about
all sales of the product.
この例では、product_id
列、p.name
列、p.price
列は必ずGROUP BY
句で指定する必要があります。
なぜなら、これらは問い合わせ選択リストの中で使われているからです(ただし、以下を参照)。
s.units
列はGROUP BY
で指定する必要はありません。
これは、製品ごとの売上計算の集約式(sum(...)
)の中だけで使われるためです。
この問い合わせは、各製品に対して製品の全販売に関する合計行が返されます。
If the products table is set up so that, say,
<literal>product_id</literal> is the primary key, then it would be
enough to group by <literal>product_id</literal> in the above example,
since name and price would be <firstterm>functionally
dependent</firstterm> on the product ID, and so there would be no
ambiguity about which name and price value to return for each product
ID group.
productsテーブルが、例えば、product_id
が主キーであるように設定されている場合、nameとprice列は製品ID(product_id)に関数依存しており、このため製品IDグループそれぞれに対してどのnameとpriceの値を返すかに関するあいまいさがありませんので、上の例ではproduct_id
でグループ化することで十分です。
In strict SQL, <literal>GROUP BY</literal> can only group by columns of
the source table but <productname>PostgreSQL</productname> extends
this to also allow <literal>GROUP BY</literal> to group by columns in the
select list. Grouping by value expressions instead of simple
column names is also allowed.
厳密なSQLでは、GROUP BY
は、元となるテーブルの列によってのみグループ化できますが、PostgreSQLでは、GROUP BY
が選択リストの列によってグループ化できるように拡張されています。
単純な列名の代わりに、評価式でグループ化することもできます。
If a table has been grouped using <literal>GROUP BY</literal>,
but only certain groups are of interest, the
<literal>HAVING</literal> clause can be used, much like a
<literal>WHERE</literal> clause, to eliminate groups from the result.
The syntax is:
GROUP BY
を使ってグループ化されたテーブルで特定のグループのみ必要な場合、結果から不要なグループを除くのに、WHERE
句のようにHAVING
句を使うことができます。
構文は以下の通りです。
SELECTselect_list
FROM ... [WHERE ...] GROUP BY ... HAVINGboolean_expression
Expressions in the <literal>HAVING</literal> clause can refer both to
grouped expressions and to ungrouped expressions (which necessarily
involve an aggregate function).
HAVING
句内の式は、グループ化された式とグループ化されてない式(この場合は集約関数が必要になります)の両方を参照することができます。
Example: 例を示します。
=>
SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;
x | sum ---+----- a | 4 b | 5 (2 rows)=>
SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';
x | sum ---+----- a | 4 b | 5 (2 rows)
Again, a more realistic example: 次に、より現実的な例を示します。
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit FROM products p LEFT JOIN sales s USING (product_id) WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks' GROUP BY product_id, p.name, p.price, p.cost HAVING sum(p.price * s.units) > 5000;
In the example above, the <literal>WHERE</literal> clause is selecting
rows by a column that is not grouped (the expression is only true for
sales during the last four weeks), while the <literal>HAVING</literal>
clause restricts the output to groups with total gross sales over
5000. Note that the aggregate expressions do not necessarily need
to be the same in all parts of the query.
上の例で、WHERE
句は、グループ化されていない列によって行を選択している(この式では最近の4週間の売上のみが真になります)のに対し、HAVING
句は出力を売上高が5000を超えるグループに制限しています。
集約式が、問い合わせ内で常に同じである必要がないことに注意してください。
If a query contains aggregate function calls, but no <literal>GROUP BY</literal>
clause, grouping still occurs: the result is a single group row (or
perhaps no rows at all, if the single row is then eliminated by
<literal>HAVING</literal>).
The same is true if it contains a <literal>HAVING</literal> clause, even
without any aggregate function calls or <literal>GROUP BY</literal> clause.
ある問い合わせが集約関数を含んでいるがGROUP BY
句がない場合でも、グループ化は依然として行われます。
結果は単一グループ行(またはHAVING
で単一行が削除されれば、行が全くなくなるかもしれません)となります。
HAVING
句を含んでいれば、集約関数呼び出しやGROUP BY
句がまったく存在しなくても同じことが言えます。
GROUPING SETS
、CUBE
、ROLLUP
#
More complex grouping operations than those described above are possible
using the concept of <firstterm>grouping sets</firstterm>. The data selected by
the <literal>FROM</literal> and <literal>WHERE</literal> clauses is grouped separately
by each specified grouping set, aggregates computed for each group just as
for simple <literal>GROUP BY</literal> clauses, and then the results returned.
For example:
上述のものよりも複雑なグループ化の操作は、グループ化セットの概念を用いることで可能です。
FROM
句とWHERE
句によって選択されたデータは、指定されたグループ化セットによってそれぞれグループ化され、単純なGROUP BY
句と同じように集約計算され、その後結果が返されます。
例を示します。
=>
SELECT * FROM items_sold;
brand | size | sales -------+------+------- Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 (4 rows)=>
SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());
brand | size | sum -------+------+----- Foo | | 30 Bar | | 20 | L | 15 | M | 35 | | 50 (5 rows)
Each sublist of <literal>GROUPING SETS</literal> may specify zero or more columns
or expressions and is interpreted the same way as though it were directly
in the <literal>GROUP BY</literal> clause. An empty grouping set means that all
rows are aggregated down to a single group (which is output even if no
input rows were present), as described above for the case of aggregate
functions with no <literal>GROUP BY</literal> clause.
GROUPING SETS
の各サブリストはゼロ個以上の列または式を指定することが出来ます。
そして、それが直接GROUP BY
句で指定したのと同じように解釈されます。
空のグループ化セットは、全行が一つのグループにまで集約されることを意味します(何も入力行が存在しない場合でも出力されます)。
これは、上述したGROUP BY
句がない集約関数の場合と同様です。
References to the grouping columns or expressions are replaced by null values in result rows for grouping sets in which those columns do not appear. To distinguish which grouping a particular output row resulted from, see <xref linkend="functions-grouping-table"/>. グループ化している列または式の参照は、その列が現われないグループ化セットの結果行ではNULL値に置き換えられます。 特定の出力行が、どのグループ化から生じたかを識別するには表 9.64を参照して下さい。
A shorthand notation is provided for specifying two common types of grouping set. A clause of the form グループ化セットの中で一般的な2種類については、略記法での指定方法が提供されています。
ROLLUP (e1
,e2
,e3
, ... )
represents the given list of expressions and all prefixes of the list including the empty list; thus it is equivalent to 上の句は、式の指定されたリストと空のリストを含めたリストのすべてのプレフィックスを表します。 したがって、以下と同等です。
GROUPING SETS ( (e1
,e2
,e3
, ... ), ... (e1
,e2
), (e1
), ( ) )
This is commonly used for analysis over hierarchical data; e.g., total salary by department, division, and company-wide total. これは一般に、階層データに対する分析のために使用されます。例えば、部署、部門、全社合計による総給与を出します。
A clause of the form
CUBE (e1
,e2
, ... )
represents the given list and all of its possible subsets (i.e., the power set). Thus 上の句は、与えられたリストとその可能な部分集合(サブセット)のすべて(すなわち、べき集合)を表します。 したがって
CUBE ( a, b, c )
is equivalent to は以下と同等です。
GROUPING SETS ( ( a, b, c ), ( a, b ), ( a, c ), ( a ), ( b, c ), ( b ), ( c ), ( ) )
The individual elements of a <literal>CUBE</literal> or <literal>ROLLUP</literal>
clause may be either individual expressions, or sublists of elements in
parentheses. In the latter case, the sublists are treated as single
units for the purposes of generating the individual grouping sets.
For example:
CUBE
句やROLLUP
句の各要素は、個々の式、または括弧で囲まれた要素のサブリスト、どちらかに出来ます。
後者の場合には、サブリストは個々のグループ化セットを生成する目的において一つの単位として扱われます。
例えば
CUBE ( (a, b), (c, d) )
is equivalent to は以下と同等です。
GROUPING SETS ( ( a, b, c, d ), ( a, b ), ( c, d ), ( ) )
and そして
ROLLUP ( a, (b, c), d )
is equivalent to は以下と同等です。
GROUPING SETS ( ( a, b, c, d ), ( a, b, c ), ( a ), ( ) )
The <literal>CUBE</literal> and <literal>ROLLUP</literal> constructs can be used either
directly in the <literal>GROUP BY</literal> clause, or nested inside a
<literal>GROUPING SETS</literal> clause. If one <literal>GROUPING SETS</literal> clause
is nested inside another, the effect is the same as if all the elements of
the inner clause had been written directly in the outer clause.
CUBE
とROLLUP
構文は、GROUP BY
句の中で直接使用、またはGROUPING SETS
句の中で入れ子に出来ます。
GROUPING SETS
句が別の内側に入れ子になっている場合、内側の句が外側の句に直接書かれている場合と効果は同じになります。
If multiple grouping items are specified in a single <literal>GROUP BY</literal>
clause, then the final list of grouping sets is the Cartesian product of the
individual items. For example:
《マッチ度[89.361702]》複数の集約項目がGROUP BY
句一つで指定されている場合、グループ化セットの最終的なリストは、個々の項目の外積(クロス積)です。
例えば
《機械翻訳》マルチプルグループ化項目が単一のGROUP BY
句で指定されている場合、グループ化セットの最終リストは個々の項目のデカルト積になります。
例の場合:。
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
is equivalent to は以下と同等です。
GROUP BY GROUPING SETS ( (a, b, c, d), (a, b, c, e), (a, b, d), (a, b, e), (a, c, d), (a, c, e), (a, d), (a, e) )
When specifying multiple grouping items together, the final set of grouping sets might contain duplicates. For example: 複数の集約項目をまとめて指定する場合、グループ化セットの最終的なセットに重複が含まれる可能性があります。 例えば
GROUP BY ROLLUP (a, b), ROLLUP (a, c)
is equivalent to は以下と同等です。
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, b), (a, c), (a), (a), (a, c), (a), () )
If these duplicates are undesirable, they can be removed using the
<literal>DISTINCT</literal> clause directly on the <literal>GROUP BY</literal>.
Therefore:
これらの重複が望ましくない場合は、GROUP BY
で直接DISTINCT
句を使用して削除できます。
したがって、
GROUP BY DISTINCT ROLLUP (a, b), ROLLUP (a, c)
is equivalent to は以下と同等です。
GROUP BY GROUPING SETS ( (a, b, c), (a, b), (a, c), (a), () )
This is not the same as using <literal>SELECT DISTINCT</literal> because the output
rows may still contain duplicates. If any of the ungrouped columns contains NULL,
it will be indistinguishable from the NULL used when that same column is grouped.
これは、SELECT DISTINCT
を使用する場合と同じではありません。
出力行に重複が含まれる可能性があるためです。
グループ化されていない列のいずれかにNULLが含まれている場合、同じ列をグループ化するときに使用されるNULLと区別できません。
The construct <literal>(a, b)</literal> is normally recognized in expressions as
a <link linkend="sql-syntax-row-constructors">row constructor</link>.
Within the <literal>GROUP BY</literal> clause, this does not apply at the top
levels of expressions, and <literal>(a, b)</literal> is parsed as a list of
expressions as described above. If for some reason you <emphasis>need</emphasis>
a row constructor in a grouping expression, use <literal>ROW(a, b)</literal>.
(a, b)
という構文は通常行コンストラクタとして式に認識されます。
GROUP BY
句の中では、トップレベルの式の場合これは適用されず、(a, b)
は上記のような式のリストとして解析されます。
何らかの理由で、グループ化式の中で行コンストラクタが必要になった場合は、ROW(a, b)
を使用して下さい。
If the query contains any window functions (see
<xref linkend="tutorial-window"/>,
<xref linkend="functions-window"/> and
<xref linkend="syntax-window-functions"/>), these functions are evaluated
after any grouping, aggregation, and <literal>HAVING</literal> filtering is
performed. That is, if the query uses any aggregates, <literal>GROUP
BY</literal>, or <literal>HAVING</literal>, then the rows seen by the window functions
are the group rows instead of the original table rows from
<literal>FROM</literal>/<literal>WHERE</literal>.
問い合わせがウィンドウ関数(3.5、9.22と4.2.8を参照)を含んでいれば、これらの関数はグループ化、集約およびHAVING
条件検索が行われた後に評価されます。
つまり、問い合わせが何らかの集約、GROUP BY
またはHAVING
を使用していれば、ウィンドウ関数により見える行はFROM
/WHERE
での本来のテーブル行ではなく、グループ行となります。
When multiple window functions are used, all the window functions having
syntactically equivalent <literal>PARTITION BY</literal> and <literal>ORDER BY</literal>
clauses in their window definitions are guaranteed to be evaluated in a
single pass over the data. Therefore they will see the same sort ordering,
even if the <literal>ORDER BY</literal> does not uniquely determine an ordering.
However, no guarantees are made about the evaluation of functions having
different <literal>PARTITION BY</literal> or <literal>ORDER BY</literal> specifications.
(In such cases a sort step is typically required between the passes of
window function evaluations, and the sort is not guaranteed to preserve
ordering of rows that its <literal>ORDER BY</literal> sees as equivalent.)
複数のウィンドウ関数が使用された場合、そのウィンドウ定義にある構文的に同等であるPARTITION BY
およびORDER BY
句を持つすべてのウィンドウ関数は、データ全体に渡って単一の実行手順により評価されることが保証されています。
したがって、ORDER BY
が一意に順序付けを決定しなくても同一の並べ替え順序付けを見ることができます。
しかし、異なるPARTITION BY
またはORDER BY
仕様を持つ関数の評価については保証されません。
(このような場合、並べ替え手順がウィンドウ関数評価の諸手順間で一般的に必要となり、ORDER BY
が等価と判断する行の順序付けを保存するような並べ替えは保証されません。)
Currently, window functions always require presorted data, and so the
query output will be ordered according to one or another of the window
functions' <literal>PARTITION BY</literal>/<literal>ORDER BY</literal> clauses.
It is not recommended to rely on this, however. Use an explicit
top-level <literal>ORDER BY</literal> clause if you want to be sure the
results are sorted in a particular way.
現時点では、ウィンドウ関数は常に事前に並べ替えられたデータを必要とするので、問い合わせ出力はウィンドウ関数のPARTITION BY
/ORDER BY
句のどれか1つに従って順序付けされます。
とはいえ、これに依存することは薦められません。
確実に結果が特定の方法で並べ替えられるようにしたいのであれば、明示的な最上階層のORDER BY
を使用します。