LIMIT BY | ClickHouse Docs

LIMIT n BY expressions 句を含むクエリは、expressions の値の各一意な組み合わせごとに、先頭の n 行を選択します。LIMIT BY のキーには、任意の数の expressions を含めることができます。

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

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

クエリ処理中、ClickHouse はソートキー順に並んだデータを選択します。ソートキーは、ORDER BY 句を使用して明示的に設定することも、table engine のプロパティとして暗黙的に設定することもできます (行の順序が保証されるのは ORDER BY を使用した場合のみで、それ以外ではマルチスレッド処理のため行のブロックは順序付けされません) 。その後、ClickHouse は LIMIT n BY expressions を適用し、expressions の各一意な組み合わせごとに先頭の n 行を返します。OFFSET が指定されている場合、ClickHouse は expressions の一意な組み合わせに属する各 data block について、ブロックの先頭から offset_value 行をスキップし、結果として最大 n 行を返します。offset_value が data block の行数を上回る場合、ClickHouse はそのブロックからは 0 行を返します。

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 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id クエリでも同じ結果が返されます。

次のクエリは、各 domain, device_type の組み合わせごとに上位 5 件のリファラーを返し、全体の行数は最大 100 行です (LIMIT n BY + LIMIT) 。

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 は、負の制限値やオフセットでも使用できます。negative LIMIT clause と同様に、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;