テストデータとリソース
genresテーブルのある行のmovie_idカラムには、moviesテーブルのある行のidの値が入ります。
映画と俳優の間には、多対多の関係があります。
この多対多の関係は、rolesテーブルを使うことで2つの一対多の関係に正規化されます。
rolesテーブルの各行には、moviesテーブルとactorsテーブルのidカラムの値が含まれます。
ClickHouseでサポートされているJOINの種類
INNER JOIN
INNER JOIN は、結合キーで一致する各行の組み合わせごとに、左側のテーブルの行のカラム値と右側のテーブルの行のカラム値を組み合わせて返します。
1 つの行に複数の一致がある場合は、該当するすべての一致が返されます (つまり、結合キーが一致する行については デカルト積 が生成されます) 。
このクエリは、movies テーブルと genres テーブルを結合して、各映画のジャンルを取得します。
INNER キーワードは省略できます。INNER JOIN の動作は、以下のいずれかの結合タイプを使用することで拡張または変更できます。
(LEFT / RIGHT / FULL) OUTER JOIN
LEFT OUTER JOIN は INNER JOIN と同様に動作します。これに加えて、左テーブルで一致しない行については、ClickHouse は右テーブルのカラムに デフォルト値 を返します。
RIGHT OUTER JOIN のクエリも同様で、右テーブルで一致しない行の値を、左テーブルのカラムのデフォルト値とともに返します。
FULL OUTER JOIN のクエリは LEFT OUTER JOIN と RIGHT OUTER JOIN を組み合わせたもので、左テーブルおよび右テーブルで一致しない行の値を、それぞれ右テーブルおよび左テーブルのカラムのデフォルト値とともに返します。
ClickHouse は、設定により デフォルト値 の代わりに NULL を返すようにできます (ただし、パフォーマンス上の理由から、あまり推奨されません) 。
genres テーブルに一致する行がない movies テーブルのすべての行を取得し、その結果 movie_id カラムに (クエリ実行時に) デフォルト値 0 が入るため、ジャンルを持たない映画をすべて見つけます。
OUTER キーワードは省略可能です。CROSS JOIN
CROSS JOIN は、結合キーを考慮せずに、2つのテーブルの完全なデカルト積を生成します。
左側のテーブルの各行は、右側のテーブルの各行と組み合わされます。
したがって、次のクエリでは、movies テーブルの各行が genres テーブルの各行と組み合わされます。
WHERE 句を追加して一致する行を対応付けることで、各映画のジャンルを見つけるための INNER JOIN の挙動を再現できます。
CROSS JOIN の別の構文では、FROM 句内で複数のテーブルをカンマ区切りで指定します。
クエリの WHERE 句に結合条件の式がある場合、ClickHouse は CROSS JOIN を INNER JOIN に書き換えます。
サンプルのクエリについては、EXPLAIN SYNTAX で確認できます (これは、クエリが実行される前に書き換えられる、構文的に最適化されたバージョンを返します) 。
CROSS JOIN クエリ版の INNER JOIN 句には、ALL キーワードが含まれています。これは、INNER JOIN に書き換えられた場合でも CROSS JOIN のデカルト積のセマンティクスを維持できるよう、明示的に追加されたものです。INNER JOIN では、デカルト積を無効化できるためです。
RIGHT OUTER JOIN では OUTER キーワードを省略でき、さらに任意で ALL キーワードを追加することもできるため、ALL RIGHT JOIN と書いても正しく動作します。
(LEFT / RIGHT) SEMI JOIN
LEFT SEMI JOINクエリは、右テーブルに少なくとも1つの結合キーの一致がある左テーブルの各行について、カラム値を返します。
返されるのは最初に見つかった一致のみです (デカルト積は無効化されています) 。
RIGHT SEMI JOINクエリも同様で、左テーブルに少なくとも1つの一致がある右テーブルのすべての行について値を返しますが、返されるのは最初に見つかった一致のみです。
このクエリは、2023年に映画に出演したすべての俳優・女優を見つけます。
通常の (INNER) JOINでは、2023年に複数の役を演じていた場合、同じ俳優・女優が複数回表示されることに注意してください。
(LEFT / RIGHT) ANTI JOIN
LEFT ANTI JOIN は、左テーブルのうち一致しないすべての行のカラム値を返します。
同様に、RIGHT ANTI JOIN は、右テーブルのうち一致しないすべての行のカラム値を返します。
前の外部結合のクエリ例は、データセット内にジャンルを持たない映画を見つけるために、anti join を使って次のように表現することもできます:
(LEFT / RIGHT / INNER) ANY JOIN
LEFT ANY JOIN は LEFT OUTER JOIN と LEFT SEMI JOIN を組み合わせたもので、ClickHouse は左テーブルの各行に対して、右テーブルに一致する行があればその行のカラム値を結合して返し、一致する行がなければ右テーブルのデフォルトのカラム値を結合して返します。
左テーブルのある行に対して右テーブルに複数の一致がある場合、ClickHouse は最初に見つかった一致との結合結果のカラム値だけを返します (デカルト積は無効化されています) 。
同様に、RIGHT ANY JOIN は RIGHT OUTER JOIN と RIGHT SEMI JOIN を組み合わせたものです。
また、INNER ANY JOIN はデカルト積を無効化した INNER JOIN です。
次の例では、values table function を使って構築した 2 つの一時テーブル (left_table と right_table) による抽象的な例で、LEFT ANY JOIN を示します。
RIGHT ANY JOINを使用した同じクエリは次のとおりです:
INNER ANY JOIN を使用したクエリは次のとおりです。
ASOF JOIN
ASOF JOIN は、厳密ではない一致を可能にします。
左側のテーブルの行に対して右側のテーブルに完全一致する行がない場合は、代わりに右側のテーブルから最も近い行が一致として使われます。
これは時系列分析で特に有用で、クエリの複雑さを大幅に抑えられます。
次の例では、株式市場データの時系列分析を行います。
quotes テーブルには、1日の特定時刻における株式シンボルのクオートが含まれます。
この例のデータでは、価格は 10 秒ごとに更新されます。
trades テーブルにはシンボルの取引が記録されています。つまり、あるシンボルの特定数量が特定時刻に買われたことを表します。
各取引の実際のコストを計算するには、取引を最も近いクオート時刻に対応付ける必要があります。
これは ASOF JOIN を使うことで簡潔に記述できます。ON 句で厳密一致の条件を指定し、AND 句で最も近い一致の条件を指定します。つまり、特定のシンボル (厳密一致) について、そのシンボルの取引時刻と同時刻またはそれ以前 (非厳密一致) で、quotes テーブル内の時刻が最も近い行を探します。
ASOF JOIN の ON 句は必須で、AND 句の非厳密一致条件に加えて、厳密一致条件を指定します。