Ich versuche die folgende Oracle-Abfrage in Postgres zu konvertieren

select
        this_.GLOBAL_TRANSACTION_ID as y0_,
        this_.BUSINESS_IDENTIFIER as y1_,
        this_.ENVIRONMENT as y2_,
        count(*) as y3_,
        this_.HOST_NAME as y4_,
        listagg(process,
        ', ') within
    group (order by
        date_time) as process,
        min(this_.DATE_TIME) as y6_,
        max(this_.DATE_TIME) as y7_,
        max(status)keep(dense_rank last
    order by
        date_time,
        decode(status,
        'COMPLETED',
        'd',
        'FAILED',
        'c',
        'TERMINATED',
        'b',
        'STARTED',
        'a',
        'z')) as status
    from
        ACTIVITY_MONITOR_TRANSACTION this_
    where
        this_.DATE_TIME between ? and ?
        and 1=1
    group by
        this_.GLOBAL_TRANSACTION_ID,
        this_.BUSINESS_IDENTIFIER,
        this_.ENVIRONMENT,
        this_.HOST_NAME,
        global_transaction_id,
        business_identifier,
        global_transaction_id,
        business_identifier
    order by
        y7_ asc

das Problem ist, dass ich nicht weiß, wie ich diesen Block konvertieren soll:

max(status)keep(dense_rank last
    order by
        date_time,
        decode(status,
        'COMPLETED',
        'd',
        'FAILED',
        'c',
        'TERMINATED',
        'b',
        'STARTED',
        'a',
        'z')) as status

Das Ziel dieses Blocks ist es, das @ zu bekommneuest status, und bei genau derselben Uhrzeit (es ist möglich!) weisen Sie den Status in der oben angegebenen Reihenfolge zu.

This is an example of data:
      ID        DATA_TIME          GLOBAL_TRANSACTION_ID   STATUS
===================================================================
 54938456;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"STARTED"
 54938505;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"COMPLETED"
 54938507;"2015-04-20 09:39:27";"8d276718-eca7-4fd0-a266 ;"FAILED"
 54938507;"2015-04-20 09:38:25";"8d276718-eca7-4fd0-a266 ;"FAILED"

Der Status sollte "COMPLETED" lauten, sodass meine Abfrage unter anderem die folgenden Zeilen zurückgeben sollte:

 GLOBAL_TRANSACTION_ID    COUNT    (...)       STATUS
=====================================================
 8d276718-eca7-4fd0-a266    4      (...)     COMPLETED

Ich habe versucht, die Abfrage in 2 aufzuteilen:

select
    this_.GLOBAL_TRANSACTION_ID as y0_,
    this_.BUSINESS_IDENTIFIER as y1_,
    this_.ENVIRONMENT as y2_,
    count(*) as y3_,
    this_.HOST_NAME as y4_,
    array_to_string(array_agg(distinct process),
    ',') as process,
    min(this_.DATE_TIME) as y6_,
    max(this_.DATE_TIME) as y7_,
    max(this_.STATUS) as y8_
from
    ACTIVITY_MONITOR_TRANSACTION this_
where
    this_.DATE_TIME between ? and ?
group by
    this_.GLOBAL_TRANSACTION_ID,
    this_.BUSINESS_IDENTIFIER,
    this_.ENVIRONMENT,
    this_.HOST_NAME,
    global_transaction_id,
    business_identifier
order by
    y7_ desc limit ?

und dan

select
    status
from
    activity_monitor_transaction
where
    GLOBAL_TRANSACTION_ID=?
order by
    date_time DESC,
    CASE status
        WHEN 'COMPLETED'THEN 'd'
        WHEN 'FAILED' THEN 'c'
        WHEN 'TERMINATED' THEN 'b'
        WHEN 'STARTED' THEN 'a'
        ELSE 'z'
    END DESC LIMIT 1

Aber dies führt zu Leistungsproblemen, da ich die zweite Abfrage einmal pro Zeile ausführen muss.

Dies ist das Tabellenskript für postgres:

CREATE TABLE activity_monitor_transaction
(
  id numeric(11,0) NOT NULL,
  date_time timestamp(6) without time zone NOT NULL,
  global_transaction_id character varying(40) NOT NULL,
  repost_flag character(1) NOT NULL DEFAULT 'N'::bpchar,
  environment character varying(20),
  transaction_mode character varying(20),
  status character varying(20),
  step character varying(80),
  event character varying(20),
  event_code character varying(20),
  event_subcode character varying(20),
  summary character varying(200),
  business_identifier character varying(80),
  alternate_business_identifier character varying(80),
  domain character varying(20),
  process character varying(80),
  service_name character varying(80),
  service_version character varying(20),
  detail text,
  app_name character varying(80),
  app_user character varying(20),
  host_name character varying(80),
  thread_name character varying(200),
  CONSTRAINT activity_monitor_transact_pk PRIMARY KEY (id)
  USING INDEX TABLESPACE actmon_data
)

und einige Daten:

insert into ACTIVITY_MONITOR_TRANSACTION values 
(54938456,'2015-04-20 09:39:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','STARTED','servicereq.p2p.rso.blaze.dedup.in.channel','PROCESS','','','','3100729','51174628','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');

insert into ACTIVITY_MONITOR_TRANSACTION values 
(54938505,'2015-04-20 09:45:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','COMPLETED','servicereq.p2p.rso.blaze.service.out.channel','PROCESS','','','','3100729','51174628','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');

insert into ACTIVITY_MONITOR_TRANSACTION values 
(54938507,'2015-04-20 09:45:27','8d276718-eca7-4fd0-a266-d465181f911a','N','Perf','','FAILED','inputChannel','PROCESS','','','','3100729','','ERP','servicereq-p2p-rso-blaze','servicereq-p2p-rso-blaze','1.0.0-SNAPSHOT','','servicereq-p2p-rso-blaze','CIC','intintprf20','SimpleAsyncTaskExecutor-88177');

Gibt es eine Möglichkeit, das @ nachzuahmekeep dense_rank Block in Postgres, um nur eine Abfrage zu haben?