Início / dba / Perguntas / 173566
Accepted
beldaz
Asked: 2017-05-15 22:42:40 +0800 CST

Combinações aleatórias do PostgreSQL com LATERAL

  • 772

No exemplo a seguir, tenho uma tabela fooda qual gostaria de escolher aleatoriamente uma linha por grupo.

CREATE TABLE foo (
  line INT
);
INSERT INTO foo (line)
SELECT generate_series(0, 999, 1);

Digamos que eu gostaria de agrupar por line % 10. Eu poderia fazer isso com:

SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 AS bin, random() x
    FROM foo
    ORDER BY x
) X

O que eu gostaria de fazer é obter escolhas aleatórias de cada caixa várias vezes. Eu pensei que seria capaz de fazer isso com generate_series()eLATERAL

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, random() x
    FROM foo
    ORDER BY x
) X
ORDER BY bin) Q
ORDER BY bin, i;

No entanto, quando faço isso no PostgreSQL 9.5, descubro que recebo o mesmo linepara um dado binpara cada iteração i, por exemplo,

i;line;bin
1;530;0
2;530;0
3;530;0
1;611;1
2;611;1
3;611;1
...

Estou confuso, pois pensei que a subconsulta contendo o random()seria executada de maneira diferente para cada linha do arquivo generate_series().

EDIT: Percebi que posso alcançar o mesmo objetivo gerando mais combinações e escolhendo entre elas com

SELECT DISTINCT ON (bin, round) round, bin, line
FROM (
    SELECT line, line % 10 as bin, round
    FROM foo, generate_series(1,3) round
    ORDER BY bin, random()
) X;

Então, minha pergunta é simplesmente por que a primeira maneira não funcionou?

EDIT: O problema parece ser que LATERAL só age como um loop for se as subconsultas estiverem correlacionadas de alguma forma (graças ao comentário de @ypercube). Portanto, minha abordagem original pode ser corrigida adicionando a seguinte pequena alteração

SELECT i, line, bin
FROM
(
 SELECT generate_series(1,3) i
) m,
LATERAL
(
SELECT DISTINCT ON (bin) bin, line
FROM (
    SELECT line, line % 10 bin, m.i, random() x -- <NOTE m.i HERE
    FROM foo
    ORDER BY x
) X
ORDER BY bin
LIMIT 3
) Q
ORDER BY bin, i;
postgresql subquery

2 respostas

  1. Best Answer

    Eu escreveria a consulta assim, usando LIMIT (3)em vez de DISTINCT ON.

    O generate_series(0, 9)é usado para obter todos os compartimentos distintos. Você poderia usar(SELECT DISTINCT line % 10 FROM foo) AS g (bin) em vez disso, se os "bins" não forem todos os inteiros de 0 a 9:

    SELECT 
    g.bin, 
    ROW_NUMBER() OVER (PARTITION BY g.bin ORDER BY d.x) AS i,
    d.* 
FROM 
    generate_series(0, 9) AS g (bin), 
  LATERAL 
    ( SELECT f.*, random() x 
      FROM foo AS f 
      WHERE  f.line % 10 = g.bin 
      ORDER BY x 
      LIMIT 3
    ) AS d
ORDER BY 
    bin, x ;

    Além disso, se você não precisar do random()número na saída, poderá usar ORDER BY random()na subconsulta e remover xdas cláusulas select e order by - ou substituir ORDER BY d.xpor ORDER BY d.line.

    • 5

  2. O que eu gostaria de fazer é obter escolhas aleatórias de cada caixa várias vezes.

    Há muitas maneiras de você resolver esse problema. Cada um introduz mais aleatoriedade e leva mais tempo.

    1. TABLESAMPLE SYSTEMetsm_system_rows
    2. TABLESAMPLE BERNOULLI
    3. Criando caixas ad-hoc e jogando os dados com estatísticas.
    4. Criando caixas ad-hoc e ordenando-as aleatoriamente e coletando.

    Na maioria das circunstâncias, TABLEAMPLE SYSTEMetsm_system_rows é suficiente para obter uma amostragem "justa" da tabela. Tem a vantagem adicional de não ter que visitar toda a mesa.

    No caso de você precisar de uma amostra mais uniformemente espaçada,TABLESAMPLE BERNOULLI visitará toda a tabela e selecionará todas as páginas internas.

    No caso de você querer continuar indo ad-hoc, acho que isso também o fará.

    SELECT *
FROM (
  SELECT dense_rank() OVER (PARTITION BY bin ORDER BY random()), *
  FROM (
    SELECT line % 10 AS bin, line
    FROM foo                          
  ) AS t
) AS t                       
WHERE dense_rank <= 3
ORDER BY line;
    • 3

relate perguntas