LIMIT BY 句

LIMIT n BY expressions 句を含むクエリは、expressions のそれぞれの異なる値ごとに先頭の n 行を選択します。LIMIT BY のキーには任意個数の式を含めることができます。

ClickHouse は次の構文バリエーションをサポートします。

LIMIT [offset_value, ]n BY expressions
LIMIT n OFFSET offset_value BY expressions

クエリ処理中、ClickHouse はソートキーで並べ替えられたデータを処理します。ソートキーは ORDER BY 句を使用して明示的に設定するか、テーブルエンジンのプロパティとして暗黙的に設定されます (ORDER BY を使用する場合のみ行の順序が保証され、それ以外の場合はマルチスレッド処理により行ブロックの順序は保証されません) 。その後、ClickHouse は LIMIT n BY expressions を適用し、expressions のそれぞれ異なる組み合わせごとに先頭の n 行を返します。OFFSET が指定されている場合、expressions のそれぞれ異なる組み合わせに属する各データブロックについて、ClickHouse はブロック先頭から offset_value 行をスキップし、その結果として最大 n 行を返します。offset_value がデータブロック内の行数より大きい場合、ClickHouse はそのブロックから 0 行を返します。

注記

LIMIT BY は LIMIT とは無関係です。両方を同じクエリ内で使用できます。

LIMIT BY 句でカラム名の代わりにカラム番号を使用したい場合は、設定 enable_positional_arguments を有効にしてください。

例

サンプルテーブル:

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

クエリ：

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

The SELECT * FROM limit_by ORDER BY id, val LIMIT 2 1 BY id query returns the same result.

次のクエリは、全体で最大100行 (LIMIT n BY + LIMIT) という制限のもとで、各 domain, device_type ペアごとに上位5件のリファラーを返します。

SELECT
    domainWithoutWWW(URL) AS domain,
    domainWithoutWWW(REFERRER_URL) AS referrer,
    device_type,
    count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100;

LIMIT BY は、負の limit および offset に対しても機能します。負の LIMIT 句と同様に、LIMIT BY でも負の値を使用して、各グループの末尾から行を選択できます。

SELECT * FROM limit_by ORDER BY id, val LIMIT -2 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

各 id について最後の2行を返します。id = 1 では 11 と 12 の行が返されます。id = 2 では、そのグループには2行しかないため、両方の行が返されます。

SELECT * FROM limit_by ORDER BY id, val LIMIT -1 OFFSET -1 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  2 │  20 │
└────┴─────┘

各 id について末尾から2番目の行を返します。末尾の OFFSET -1 で各グループの最後の行を除外し、その後、先頭の -1 で残った行の最後の行だけを保持します。

符号の異なる LIMIT と OFFSET を組み合わせることもできます。たとえば、各グループの先頭行を除外してから、残った行のうち最後の2行を保持するには、次のようにします。

SELECT * FROM limit_by ORDER BY id, val LIMIT -2 OFFSET 1 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

id = 1 の場合、最初の行 (10) はスキップされ、11, 12 の最後の 2 行が返されます。id = 2 の場合、最初の行 (20) はスキップされ、21 のみが残ります。

LIMIT BY ALL

LIMIT BY ALL は、集約関数ではない、SELECT で指定したすべての式を列挙するのと同等です。

例:

SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL;

と同じです

SELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3;

集約関数とその他のフィールドの両方を引数に取る関数が存在するという特殊なケースでは、LIMIT BY のキーには、その関数から抽出可能な非集約フィールドが可能な限り多く含まれます。

例えば、次のようになります。

SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL;

と同じです

SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2);

例

サンプルテーブル：

CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

クエリ：

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;

┌─id─┬─val─┐
│  1 │  11 │
│  1 │  12 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id クエリは、同じ結果を返します。

LIMIT BY ALL の使用例:

SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL;

これは次と同等です：

SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val;

例​

LIMIT BY ALL​

例​

例

LIMIT BY ALL

例