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. Isso inclui a forma de intervalo LIMIT ... AFTER ... UNTIL, que então se aplica às linhas mantidas por LIMIT BY.

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;