Início / dba / Perguntas / 201898
Accepted
Philᵀᴹ
Asked: 2018-03-22 06:23:29 +0800 CST

ORDER definido pelo usuário no SQL [duplicado]

  • 772

Acabei de ler um artigo de blog interessante sobre a ordenação do conjunto de resultados definido pelo usuário.

Alguns aplicativos, como listas de tarefas, precisam manter uma ordem de itens definida pelo usuário. O desafio é que a ordem é arbitrária e pode mudar quando o usuário reorganiza os itens

Uma tabela de exemplo poderia ser assim:

create table items
(
  itemid int primary key,
  itemdata varchar(200),
  ...
  ...
  userorder ??? 
);

Com uma possível consulta de recuperação de:

select * from items order by userorder asc;

Há alguma abordagem que eu deva considerar, já que gostaria de manter as atualizações no mínimo, atualizações atômicas e consultas de recuperação o mais simples e "desempenhadas" possível?

Meu pensamento inicial era usar uma TIMESTAMPcoluna extra e atualizar apenas userordere os timestampvalores para uma única linha (com a consulta de recuperação usando ORDERing by userorder asc, usertimestamp desc), mas isso falha quando você precisa mover uma linha entre duas outras linhas que têm o mesmo valor .

Nenhum banco de dados específico em mente, pois eu mesmo uso um amplo espectro.

order-by

2 respostas

  1. Há alguma abordagem que eu deva considerar, já que gostaria de manter as atualizações no mínimo, atualizações atômicas e consultas de recuperação o mais simples e "desempenhadas" possível?

    Eu penso nisso como uma variação esotérica da abordagem "True Fractions" naquela postagem do blog - a vantagem é que não há necessidade de introduzir um tipo definido pelo usuário (pelo menos se você estiver usando o Postgres).

    Isso funciona usando as propriedades de ordenação natural varbite mapeando uma sequência delas em uma árvore binária, de modo que sempre seja possível gerar outro varbitvalor entre quaisquer dois valores adjacentes existentes:

                              _____ 1 ____
                   ______/            \______
              _ 01 _                          11
            _/      \_                    _/      \_
        001            011            101            111
       /   \          /   \          /   \          /   \
    0001   0011    0101   0111    1001   1011    1101    1111

    create table foo(id serial primary key, userorder varbit unique);

create function adj(orig varbit, b varbit) returns varbit language sql as $$
  select substring(orig for length(orig)-1)
       ||b
       ||substring(orig from length(orig));
$$;

create function f(l varbit, h varbit) returns varbit language plpgsql as $$

begin
  if l is null and h is null then return B'1'; end if;
  if l is null then return adj(h,B'0'); end if;
  if h is null then return adj(l,B'1'); end if;
  if length(l)>length(h) then return adj(l,B'1'); end if;
  return adj(h,B'0');
end;
$$;

    insert into foo(userorder) values(f(null,null));
select * from foo order by userorder;

    identificação | ordem do usuário
-: | :--------
 1 | 1        

    insert into foo(userorder) values(f(null,B'1'));
select * from foo order by userorder;

    identificação | ordem do usuário
-: | :--------
 2 | 01       
 1 | 1        

    insert into foo(userorder) values(f(B'01',B'1'));
select * from foo order by userorder;

    identificação | ordem do usuário
-: | :--------
 2 | 01       
 3 | 011      
 1 | 1        

    insert into foo(userorder)
values(f(B'01',B'011')),(f(B'011',B'1')),(f(B'1',null));

select * from foo order by userorder;

    identificação | ordem do usuário
-: | :--------
 2 | 01       
 4 | 0101     
 3 | 011      
 5 | 0111     
 1 | 1        
 6 | 11

    dbfiddle aqui

    Se você carregar inicialmente muitas linhas ordenadas, talvez queira usar algum outro algoritmo para gerar os userordervalores iniciais, pois atingirá o pior caso para uso de espaço (cada linha usará um bit a mais do userorderque a linha anterior). Você pode percorrer valores de mesmo comprimento para um número suficientemente grande de bits (por exemplo, para 8 valores: B'0001', B'0011', B'0101', B'0111', B'1001', B'1011', B'1101', B'1111').

    • 5
  2. Best Answer

    Introdução

    Algumas linguagens BASIC realmente antigas exigiam um número de linha para cada linha de código.

    Ao programar, os números de linha eram normalmente espaçados em múltiplos de 10. Isso permitia que você adicionasse mais código entre as linhas posteriormente.

    Sua itemstabela deve seguir o mesmo conceito.

    Algoritmo básico

    1. Valores para userordercomeçar como múltiplos de 10 começam com 10 para cadauserid
    2. Atualizações de aplicativos userorderconforme necessário
    3. Você renumera o usuário desse usuário userorderusando o Analytics
    4. Os valores para 'userorder' permanecem como múltiplos de 10.
    5. commitos dados mudam.

    Código de renumeração

    Isso é basicamente o que eu usei:

    merge into items a
using (
  select i.itemid
    ,10 * row_number() over (partition by i.userid order by i.userorder)
      as new_userorder
  from items i
  where i.userid=?
) b
on (a.itemid = b.itemid)
when matched then update
  set a.userorder=b.new_userorder
;

    Estou assumindo que esta tabela contém a lista TODO de todos e cada usuário é identificado por userid.

    Este exemplo usa ROW_NUMBER()do Oracle. Você terá que substituí-lo por qualquer RDBMS que estiver usando.

    • 2

relate perguntas