Início / dba / Perguntas / 27279
Accepted
swasheck
Asked: 2012-10-20 12:00:28 +0800 CST

Como preservar a ordem original dos elementos em uma matriz não aninhada?

  • 772

Dada a string:

'Eu acho que o PostgreSQL é bacana'

Eu gostaria de operar nas palavras individuais encontradas dentro dessa string. Essencialmente, tenho um separado do qual posso obter detalhes da palavra e gostaria de ingressar em uma matriz não aninhada dessa string neste dicionário.

Até agora eu tenho:

select word, meaning, partofspeech
from unnest(string_to_array('I think that PostgreSQL is nifty',' ')) as word
from table t
join dictionary d
on t.word = d.wordname;

Isso cumpre os fundamentos do que eu esperava fazer, mas não preserva a ordem original das palavras.

Questão relacionada:
PostgreSQL unnest() com número de elemento

postgresql sorting

1 respostas

  1. Best Answer

    WITH ORDINALITYno Postgres 9.4 ou posterior

    A consulta agora pode ser simplesmente:

    SELECT *
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') WITH ORDINALITY x(word, rn);

    Ou, aplicado a uma tabela:

    SELECT *
FROM   tbl t, regexp_split_to_table(t.col, ' ') WITH ORDINALITY x(word, rn);

    Detalhes:

    LATERALSobre a junção implícita :

    db<>fiddle aqui
    Old sqlfiddle

    Postgres 9.3 ou anterior - e uma explicação mais geral

    Para uma única cadeia

    Você pode aplicar a função de janela row_number()para lembrar a ordem dos elementos. No entanto, com o usual, row_number() OVER (ORDER BY col)você obtém números de acordo com a ordem de classificação , não a posição original na string.

    Você pode simplesmente omitir ORDER BYpara obter a posição "como está":

    SELECT *, row_number() OVER () AS rn
FROM   regexp_split_to_table('I think Postgres is nifty', ' ') AS x(word);

    Desempenho de regexp_split_to_table()degrada com strings longas. unnest(string_to_array(...))escala melhor:

    SELECT *, row_number() OVER () AS rn
FROM   unnest(string_to_array('I think Postgres is nifty', ' ')) AS x(word);

    No entanto, embora isso normalmente funcione e eu nunca o tenha visto quebrar em consultas simples, o Postgres não afirma nada quanto à ordem das linhas sem um ORDER BY.

    Para garantir números ordinais de elementos na string original, use generate_subscript()(melhorado com comentário de @deszo):

    SELECT arr[rn] AS word, rn
FROM   (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM   string_to_array('I think Postgres is nifty', ' ') AS x(arr)
   ) y;

    Para uma tabela de strings

    Adicionar PARTITION BY idà OVERcláusula...

    Tabela de demonstração:

    CREATE TEMP TABLE strings(string text);
INSERT INTO strings VALUES
  ('I think Postgres is nifty')
, ('And it keeps getting better');

    Eu uso ctidcomo substituto ad-hoc para uma chave primária . Se você tiver uma (ou qualquer coluna exclusiva ), use-a.

    SELECT *, row_number() OVER (PARTITION BY ctid) AS rn
FROM  (
   SELECT ctid, unnest(string_to_array(string, ' ')) AS word
   FROM   strings
   ) x;

    Isso funciona sem qualquer ID distinto:

    SELECT arr[rn] AS word, rn
FROM  (
   SELECT *, generate_subscripts(arr, 1) AS rn
   FROM  (
      SELECT string_to_array(string, ' ') AS arr
      FROM   strings
      ) x
   ) y;

    Resposta à pergunta

    SELECT z.arr, z.rn, z.word, d.meaning   -- , partofspeech -- ?
FROM  (
   SELECT *, arr[rn] AS word
   FROM  (
      SELECT *, generate_subscripts(arr, 1) AS rn
      FROM  (
         SELECT string_to_array(string, ' ') AS arr
         FROM   strings
         ) x
      ) y
   ) z
JOIN   dictionary d ON d.wordname = z.word
ORDER  BY z.arr, z.rn;
    • 29

relate perguntas