Início / dba / Perguntas / 310897
Accepted
Henrik N
Asked: 2022-04-14 02:31:49 +0800 CST

Qual é uma boa maneira de fazer uma comparação > ou < que considera NULLs ordenados primeiro ou por último?

  • 772

Estou no Postgres 13.5.

Os registros sempre têm um number(tipo: integer) e, opcionalmente, uma única letterAZ (tipo: varchar(1)– não charpor motivos de estrutura).

Os registros geralmente são classificados number ASC, letter ASC NULLS FIRST. A sequência pode ter lacunas. As letras ausentes são representadas como NULL.

Por exemplo, você pode obter este pedido: 1, 1A, 1B, 2, 10, 10A, 10C

Agora, quero fazer coisas como encontrar os dois registros "à direita" de um determinado registro. Portanto, se o registro fornecido for o número 2, quero encontrar 10 e 10A no exemplo acima.

seria conveniente se eu pudesse consultar uma condição como (pseudo código):number > $given_number OR (number = $given_number AND letter > $given_letter NULLS FIRST)

Isso não funciona como está escrito, é claro. Quais são as maneiras que eu poderia conseguir isso?

Prefiro não mesclar as colunas ou adicionar novas colunas.

Soluções que consigo pensar:

  • Selecione uma lista de IDs de registro no SQL, use a lógica do aplicativo fora do banco de dados para localizar IDs dos próximos dois registros e faça uma segunda consulta para localizar apenas aqueles.
  • Uma condição mais longa que explica explicitamente NULLs, algo comoWHERE number > $given_number OR (number = $given_number AND (($given_letter IS NULL AND letter IS NOT NULL) OR letter > $given_letter)) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2
  • Aglutinando NULLs, algo comoWHERE number > $given_number OR (number = $given_number AND letter > COALESCE($given_letter, '')) ORDER BY number ASC, letter ASC NULLS FIRST LIMIT 2

Alguma ideia melhor? Ou alguma opinião sobre estes?

postgresql

2 respostas

  1. Best Answer

    Se você puder converter valores NULL em letterstrings vazias ( '') , tudo se encaixará. A string vazia é classificada antes de qualquer outro valor na ordem de classificação padrão.

    Isso supondo que você não tenha strings vazias e valores NULL ( o que seria ainda mais lamentável).

    UPDATE tbl SET letter = '' WHERE letter IS NULL;

ALTER TABLE tbl
  ALTER COLUMN letter SET NOT NULL
, ALTER COLUMN letter SET DEFAULT ''
, ADD PRIMARY KEY (number, letter)  -- optional
;

    Sua ordem de classificação:

    number ASC, letter ASC NULLS FIRST

    Torna-se apenas:

    number, letter

    ... encontrar os dois registros "à direita" de um determinado registro.

    SELECT *
FROM   tbl
WHERE  (number, letter) > (10, '')
ORDER  BY number, letter
LIMIT  2;

    db<>fique aqui

    Observe o uso de comparação de valor de linha . Ver:

    Faça backup com um UNIQUEíndice ou PK em (number, letter), e você está de ouro.

    Se você não puder limpar a definição da tabela, ainda há uma solução alternativa:

    SELECT *
FROM   tbl
WHERE  (number, COALESCE(letter, '')) > (10, COALESCE(NULL, ''))
ORDER  BY number, COALESCE(letter, '')
LIMIT  2;

    Pode até ser suportado com um índice de várias colunas correspondente. Mas, em vez disso, mantenha-o simples e converta seus valores NULL.

    A parte :
    para cartas de AZ, o tipo "char"seria ainda um pouco mais eficiente - decididamente distinto de char, o que nunca é útil. (Mas suas "razões de estrutura" provavelmente se opõem a isso.) Veja:

    • 4

  2. Por exemplo, você pode obter esta ordem: 1, 1A, 1B, 2, 10, 10A, 10C Agora, quero fazer coisas como encontrar os dois registros "à direita" de um determinado registro. Portanto, se o registro fornecido for o número 2, quero encontrar 10 e 10A no exemplo acima.

    A função window LEAD(column_name,1) OVER (ORDER BY ...)e LEAD(column_name,2) OVER (ORDER BY ...)pode fazer o que você deseja, se:

    • o fato de que os valores são um e dois registros de distância com a ordem de classificação fornecida pode ser conectado na consulta.
    • a condição de filtrar o dado registro pode ser adicionada em uma consulta externa, resultando em um plano potencialmente menos eficiente que suas soluções propostas com uma cláusula LIMIT.

    A forma da consulta seria

    SELECT * FROM (
   SELECT *,
   LEAD(number,1) OVER (ORDER BY number,letter nulls first) AS next_number_1,
   LEAD(letter,1) OVER (ORDER BY number,letter nulls first) AS next_letter_1,
   LEAD(number,2) OVER (ORDER BY number,letter nulls first) AS next_number_2,
   LEAD(letter,2) OVER (ORDER BY number,letter nulls first) AS next_letter_2
   FROM table
) s
WHERE number=$given_number AND letter is not distinct from $given_letter
    • 1

relate perguntas