Início / dba / Perguntas / 56168
Accepted
dotancohen
Asked: 2014-01-06 02:25:37 +0800 CST

Selecione cada n-ésima linha, não puxe a tabela inteira

  • 772

À luz da discussão Meta sobre permitir perguntas básicas de SQL em dba.SE , apresento um problema que estou tendo agora, para o qual a resposta no Stack Overflow é inadequada e ingênua. Espero que haja uma solução melhor para o problema do que as apresentadas no SO (já que estou enfrentando esse problema em um aplicativo), e dba.SE parece ser o lugar perfeito para encontrar uma resposta melhor.

Aqui está a pergunta original no Stack Overflow: Como você seleciona cada n-ésima linha do mysql?

Aqui está a resposta aceita:

SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1

O problema crítico com a resposta aceita é que ela requer puxar a tabela inteira para uma tabela temporária. Portanto, abordei essa preocupação no título desta pergunta.

Considere também que a tabela pode ter excluído linhas, portanto, uma consulta alternativa que fosse simplesmente testar WHERE MODa chave primária também não é uma boa solução. Id est, a chave primária não é confiável para ser sequencial.

Existe uma maneira melhor de formular uma consulta que retornaria a cada segundo, décimo ou n-ésima linha arbitrária, que não exija puxar a tabela inteira para a memória, mas também considere as linhas excluídas?

Cada n-ésima linha pode ser definida como tal:

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

Meu banco de dados de destino é o MySQL 5.5 rodando em uma distribuição Linux comum derivada do Debian.

EDIT: Em resposta à resposta de Thomas:

A solução sugerida não produz o resultado esperado, veja abaixo:

mysql> SELECT 
    ->     @i:=@i+1 AS iterator 
    ->     , t.name
    -> FROM 
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)
mysql

2 respostas

  1. Best Answer

    Para dados de teste emevents

    id  txtcol
--  ------
 1  event0
 2  event1
 4  event2
 5  event3
 6  event4
 8  event5
 9  event6

    Recupere os valores da chave primária em ordem crescente

    SELECT id FROM events ORDER BY id

    Envolva isso em uma consulta para atribuir uma classificação baseada em zero

    set @row:=-1;
SELECT @row:=@row+1 AS rownum, id 
FROM
    (
        SELECT id FROM events ORDER BY id
    ) AS sorted

    Envolva isso em uma consulta para selecionar a primeira linha e cada terceira linha depois disso

    set @row:=-1;
SELECT id
FROM
    (
        SELECT @row:=@row+1 AS rownum, id 
        FROM
            (
                SELECT id FROM events ORDER BY id
            ) AS sorted
    ) as ranked
WHERE rownum % 3 = 0

    Por fim, envolva isso em uma consulta para recuperar as outras colunas

    set @row:=-1;
SELECT events.*
FROM
    events
    INNER JOIN
    (
        SELECT id
        FROM
            (
                SELECT @row:=@row+1 AS rownum, id 
                FROM
                    (
                        SELECT id FROM events ORDER BY id
                    ) AS sorted
            ) as ranked
        WHERE rownum % 3 = 0
    ) AS subset
        ON subset.id = events.id

    retornando

    id  txtcol
--  ------
 1  event0
 5  event3
 9  event6
    • 5

  2. Para amostrar cada n-ésima linha aleatoriamente, eu usaria uma função de hash na chave.

    Por exemplo, se você quiser a 10ª linha, poderá expressar sua consulta assim:

    SELECT foo, bar 
FROM MyTable 
WHERE CRC32(key) % 10 = 0

    Supondo que você escolha uma função de hash (como CRC32) com um bom spread, isso deve proteger contra buracos keyque existem como resultado de exclusões.

    Estritamente falando, isso não é garantido para amostrar EXATAMENTE 1/n da tabela. Mas se a tabela for grande o suficiente para que você se preocupe com o desempenho dela, presumo que a solução o aproxime o suficiente.

    É importante perceber que não existe um "número de linha" em um banco de dados relacional. Uma tabela é simplesmente um conjunto não ordenado de tuplas. Os índices na tabela podem armazenar as tuplas em uma determinada ordem, mas isso ainda não adiciona nenhuma forma de numeração de linha aos dados. Isso significa que um número de linha só tem significado na presença de uma cláusula ORDER BY.

    Em outras palavras, se você quer dizer que deseja a enésima linha com relação a alguma forma de ordenação da tabela, então você deve expressar algo com um ORDER BY no SELECT. Por exemplo, isso fará:

    SELECT 
    @i:=@i+1 AS iterator 
    , t.foo
    , t.bar
FROM 
    MySql AS t,
    (SELECT @i:=0) AS dummy
WHERE @i % 10 = 0
ORDER BY somecolumn

    Se somecolumnfor o índice primário, essa consulta deve ser executada sem classificação ou tabela temporária. Mas você ainda precisa visitar todas as linhas para obter o resultado.

    • 1

relate perguntas