postgresql中的排序

Question

我正面临着一个问题，而我不明白。在mac上运行PostgreSQL并具有以下查询：

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

样本表数据如下：

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

我想得到的结果如下：

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

我不能按最后一栏(tldorder)排序，因为这会使旅行的逻辑混乱。我需要根据最后一列(tldorder)得出结果，但仍然保留locdescription值的原始序列。分析前两行数据：

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

我需要把它们交换一下，这样它就会变成：

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

接下来的两行也是一样的

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

必须成为：

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

问题是，我不能通过locdescription订购，因为这些都是预先设定的，必须在整个查询过程中保持特定的顺序，因为这是在公路旅行中定义的点，但是它们可以在“自身”内部被移动。

这是表中数据的顺序。

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

它们必须保持这种状态，但是可以交换值的键，以便根据tldorder值对它们进行排序。

如果执行上面所示的查询，则得到与表中相同的结果：

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

我想我已经理解了这个问题。我是按tldid订购的，它是一个序列，而不是同一个值的多次。一旦我也对tldorder进行排序，该值就会链接到仅一个to值上。这就是为什么这种做法行不通的原因。有办法解决这个问题吗？

表定义：

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)
);

Answer 1

编辑之后，问题是(最后！)安全。问题是你想要的订单是复杂的。这是一个“空隙和岛屿”的问题。首先要隔离具有相同位置的“岛屿”，即具有相同位置的连续行(由tldid排序时)，然后根据tldorder重新排序这些岛屿中的行。

解决这个问题的一个方法是：

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 ;

可以删除else null和rows between unbounded preceding and current row，因为它们是默认的。

如果在选择列表中添加count(...)，则可以看到岛屿编号。第一个岛将有0 (位置变化)，第二个岛将有1，第三个岛将有2等等。

进一步说明：

• 如果填充了trip_log_det (tldactiondate)并可用于排序，查询就会简单得多。