Cláusula LIMIT BY

Documentação da cláusula LIMIT BY

Uma consulta com a cláusula LIMIT n BY expressions seleciona as primeiras n linhas para cada valor distinto de expressions. A chave de LIMIT BY pode conter qualquer quantidade de expressões.

O ClickHouse oferece suporte às seguintes variantes de sintaxe:

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

Durante o processamento da consulta, o ClickHouse seleciona os dados ordenados pela chave de ordenação. A chave de ordenação é definida explicitamente com uma cláusula ORDER BY ou implicitamente como uma propriedade do engine da tabela (a ordem das linhas só é garantida ao usar ORDER BY; caso contrário, os blocos de linhas não estarão ordenados devido ao multithreading). Em seguida, o ClickHouse aplica LIMIT n BY expressions e retorna as primeiras n linhas para cada combinação distinta de expressions. Se OFFSET for especificado, então, para cada bloco de dados que pertença a uma combinação distinta de expressions, o ClickHouse pula offset_value linhas a partir do início do bloco e retorna, no máximo, n linhas como resultado. Se offset_value for maior que o número de linhas no bloco de dados, o ClickHouse não retorna nenhuma linha desse bloco.

LIMIT BY não está relacionado a LIMIT. Ambos podem ser usados na mesma consulta.

Se você quiser usar números de colunas em vez de nomes de colunas na cláusula LIMIT BY, ative a configuração enable_positional_arguments.

​

Exemplos

Tabela de exemplo:

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);

Consultas:

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

A consulta SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id retorna o mesmo resultado.

A consulta a seguir retorna as 5 principais origens de referência para cada par domain, device_type, com no máximo 100 linhas no total (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 também funciona com limites e offsets negativos. Assim como na cláusula LIMIT com valores negativos, você pode usar valores negativos com LIMIT BY para selecionar linhas do final de cada grupo.

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

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

Retorna as últimas 2 linhas de cada id. Para id = 1, obtemos as linhas 11 e 12; para id = 2, ambas as linhas são retornadas porque o grupo tem apenas 2 linhas.

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

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

Retorna a penúltima linha de cada id: o OFFSET -1 no final descarta a última linha de cada grupo, e o -1 no início mantém então a última linha do que restou.

Também é possível misturar LIMIT e OFFSET com sinais diferentes. Por exemplo, para descartar a primeira linha de cada grupo e depois manter as 2 últimas do que restou:

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

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

Para id = 1, a primeira linha (10) é ignorada; as 2 últimas de 11, 12 são retornadas. Para id = 2, a primeira linha (20) é ignorada, restando apenas 21.

​

LIMIT BY ALL

LIMIT BY ALL equivale a listar todas as expressões selecionadas no SELECT que não são funções de agregação.

Por exemplo:

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

é igual a

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

Em um caso especial em que uma função tenha tanto funções de agregação quanto outros campos como argumentos, as chaves de LIMIT BY conterão o máximo de campos não agregados que conseguirmos extrair dela.

Por exemplo:

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

é igual a

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);

​

Exemplos

Tabela de exemplo:

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);

Consultas:

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

A consulta SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id retorna o mesmo resultado.

Usando LIMIT BY ALL:

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

Isso equivale a:

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