Início / dba / Perguntas / 127683
Accepted
Fabrizio Mazzoni
Asked: 2016-01-29 22:27:46 +0800 CST

Ordenando no postgresql

  • 772

Estou enfrentando um problema que não entendo. Executando o PostgreSQL no mac e tenho esta consulta:

select tldid, 
       tldaction, 
       to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
       locdescription,
       lttype, 
       tldorder
from trip_log 
    left join trip_log_det on tlid=tldtlid 
    left join locations on tldlocation=locid 
    left join location_types on loctype=ltid
where tlid = 12 
order by tldid, tldorder

Os dados da tabela de amostra são:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

O resultado que gostaria de obter é o seguinte:

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1    
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

Não consigo classificar pela última coluna (tldorder), pois isso mudaria a lógica da viagem. Preciso reclassificar o resultado com base na última coluna (tldorder) mas ainda mantendo a sequência original de locdescriptionvalores. Analisando as duas primeiras linhas de dados:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2

Eu preciso trocá-los para que se torne:

94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4

Mesma coisa para as próximas duas linhas

95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1

tem que se tornar:

96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1  
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4

O problema é que não posso ordenar, locdescriptionpois eles são predefinidos e precisam permanecer nessa ordem específica durante a consulta, pois isso define pontos em uma viagem, mas eles podem ser embaralhados dentro de "eles mesmos".

Esta é a ordem dos dados na tabela.

DAR ES SALAAM
DAR ES SALAAM
TUNDUMA GOING
TUNDUMA GOING
SOLWEZI
SOLWEZI
TUNDUMA RETURN 
TUNDUMA RETURN
DAR ES SALAAM
DAR ES SALAAM

Eles devem permanecer assim, mas as chaves dos valores podem ser trocadas para classificá-los com base no tldordervalor.

Se eu executar a consulta mostrada acima, obtenho o mesmo resultado da tabela:

93;"DEPARTURE DATE";"";"DAR ES SALAAM";"LOADING POINT";4
94;"LOADING DATE";"";"DAR ES SALAAM";"LOADING POINT";2
95;"DEPARTURE DATE";"";"TUNDUMA GOING";"BORDER";4
96;"ARRIVAL DATE";"";"TUNDUMA GOING";"BORDER";1
97;"ARRIVAL DATE";"";"SOLWEZI";"OFFLOADING POINT";1
98;"OFFLOADING DATE";"";"SOLWEZI";"OFFLOADING POINT";3
99;"DEPARTURE DATE";"";"TUNDUMA RETURN";"BORDER";4
100;"ARRIVAL DATE";"";"TUNDUMA RETURN";"BORDER";1
101;"ARRIVAL DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";1
102;"OFFLOADING DATE";"";"DAR ES SALAAM";"OFFLOADING POINT";3

Acho que entendi o problema. Estou ordenando por tldid que é uma série e não várias vezes o mesmo valor. Depois de classificar também para tldorder, o valor é vinculado a um único valor tldid. É por isso que a classificação não está funcionando. Existe alguma maneira de resolver este problema?

Definições de tabela:

CREATE TABLE public.trip_log_det
(
  tldid integer NOT NULL DEFAULT nextval('trip_lod_det_tldid_seq'::regclass),
  tldtlid integer,
  tldlocation integer,
  tldaction character varying,
  tldactiondate date,
  tldorder integer, -- Ordering for arrival loading offloading and departure actions so user does not get confused!
  CONSTRAINT trip_lod_det_pkey PRIMARY KEY (tldid),
  CONSTRAINT trip_lod_det_tldlocation_fkey FOREIGN KEY (tldlocation)
      REFERENCES public.locations (locid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.locations
(
  locid integer NOT NULL DEFAULT nextval('locations_locid_seq'::regclass),
  locdescription character varying,
  loctype integer,
  CONSTRAINT locations_pkey PRIMARY KEY (locid),
  CONSTRAINT "loctype->lttype" FOREIGN KEY (loctype)
      REFERENCES public.location_types (ltid) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE public.location_types
(
  ltid integer NOT NULL DEFAULT nextval('location_types_ltid_seq'::regclass),
  lttype character varying,
  ltdeparturedate boolean NOT NULL DEFAULT false,
  ltarrivaldate boolean NOT NULL DEFAULT false,
  ltloadingdate boolean NOT NULL DEFAULT false,
  ltoffloadingdate boolean NOT NULL DEFAULT false,
  CONSTRAINT location_types_pkey PRIMARY KEY (ltid)
);
postgresql order-by

1 respostas

  1. Best Answer

    Após a edição, a questão é (finalmente!) clara. O problema é que a ordem que você quer é complicada. É um problema de "lacunas e ilhas". Você deseja primeiro isolar "ilhas" com o mesmo local, ou seja, tldidlinhas consecutivas (quando ordenadas por ) que têm o mesmo local e, em seguida, reordenar as linhas nessas ilhas com base no tldorder.

    Uma maneira de resolver isso:

    with trip_log_det_ordered as
  ( select 
        tldid, 
        tldaction, 
        tldactiondate, 
        locdescription,
        lttype, 
        tldorder, 
        case when locid <> lag(locid) over (order by tldid) then 1 else null end
            as location_change
    from trip_log 
        left join trip_log_det on tlid=tldtlid 
        left join locations on tldlocation=locid 
        left join location_types on loctype=ltid
    where tlid = 12 
  )
select 
    tldid, 
    tldaction, 
    to_char(tldactiondate, 'dd/mm/YYYY') as tldactiondate, 
    locdescription,
    lttype, 
    tldorder
from 
    trip_log_det_ordered
order by 
    count(location_change) over (order by tldid
                                 rows between unbounded preceding
                                          and current row),
    tldorder ;

    The else nulle the rows between unbounded preceding and current rowpodem ser removidos, pois são o padrão.

    Se você adicionar count(...)na lista de seleção, poderá ver a numeração das ilhas. A primeira ilha terá 0(mudanças de localização), a segunda ilha terá 1, a terceira terá 2, etc...

    Observações adicionais:

    • A consulta seria muito, muito mais simples se o trip_log_det (tldactiondate)fosse preenchido e pudesse ser usado para o pedido.
    • 3

relate perguntas