Início / dba / Perguntas / 103826
Accepted
AntiPawn79
Asked: 2015-06-12 11:59:05 +0800 CST

gerar nova coluna a partir de outra coluna da tabela, tratar nulos são únicos

  • 772

Eu tenho uma tabela que se parece com o seguinte:

    |device_id | login_id |
    -----------------------
    |    1     |    NULL  |
    |    2     |     A    |
    |    3     |     A    |
    |    4     |    NULL  |
    |    5     |     B    |
    |    5     |     C    |
    -----------------------

Desejo gerar uma nova coluna (person_id) como a seguir. A coluna essencialmente gera um identificador exclusivo (pessoa). Cada novo valor NULL obtém seu próprio unique_id e cada login_id não nulo distinto obtém seu próprio person_id exclusivo.

    |device_id | login_id | person_id |
    -----------------------------------
    |    1     |    NULL  |     1     |
    |    2     |     A    |     2     |
    |    3     |     A    |     2     |
    |    4     |    NULL  |     3     |
    |    5     |     B    |     4     |
    |    5     |     C    |     5     |
    -----------------------------------

Existe uma maneira de fazer isso no PostgreSQL puro sem passar por uma série de consultas e tabelas temporárias? Estou usando o PostgreSQL 9.4. Qualquer ajuda é muito apreciada.

postgresql null

1 respostas

  1. Best Answer

    Adicionando a coluna:

    alter table device_login
  add column person_id int ;

    A consulta é um pouco complicada, devido aos nulos. Primeiro agrupe os logins não nulos por login e mantenha cada nulo separadamente

    with a as 
( select min(device_id) as device_id, login_id
  from device_login
  where login_id is not null
  group by login_id 
union all
  select device_id, login_id
  from device_login
  where login_id is null
)

    em seguida, use dense_rank()para atribuir números separados a cada grupo

    , b as
( select dl.device_id, a.login_id, 
         dense_rank() over (order by a.device_id, a.login_id) as rnk
  from a join device_login as dl 
      on a.login_id = dl.login_id 
      or a.login_id is null and a.device_id = dl.device_id
)

    e finalmente atualize a tabela:

    update device_login as dl
set person_id = b.rnk
from b
where b.login_id = dl.login_id 
   or b.login_id is null and b.device_id = dl.device_id ;

    Testado no SQLfiddle .

    • 1

relate perguntas