将来自Oracle查询的dense_rank转换为postgres

我正在尝试将以下Oracle查询转换为Postgres

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,business_identifier
    order by
        y7_ asc

问题是我不知道如何转换这个块：

max(status)keep(dense_rank last
    order by
        date_time,'z')) as status

此块的目的是获取最新状态,如果完全相同(可能！),请按照上面的顺序分配状态.

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"

状态应为“COMPLETED”,因此我的查询应返回以下行：

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

我试过将查询拆分为2：

select
    this_.GLOBAL_TRANSACTION_ID as y0_,array_to_string(array_agg(distinct process),') as process,max(this_.STATUS) as y8_
from
    ACTIVITY_MONITOR_TRANSACTION this_
where
    this_.DATE_TIME between ? and ?
group by
    this_.GLOBAL_TRANSACTION_ID,business_identifier
order by
    y7_ desc limit ?

然后

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

但这导致我出现性能问题,因为我必须每行执行一次第二次查询.

这是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
)

和一些数据：

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

insert into ACTIVITY_MONITOR_TRANSACTION values 
(54938505,'2015-04-20 09:45:27','servicereq.p2p.rso.blaze.service.out.channel','SimpleAsyncTaskExecutor-88177');

insert into ACTIVITY_MONITOR_TRANSACTION values 
(54938507,'inputChannel','SimpleAsyncTaskExecutor-88177');

有没有办法模仿保持dense_rank块到postgres只有一个查询？

解决方法

-- we only added infos to the activity_monitor_transaction
-- we are free to group by date_time or status
SELECT
  first_value(status) OVER w AS global_transaction_status,count(*) OVER w AS global_transaction_count,activity_monitor_transaction.*
FROM
  activity_monitor_transaction
WINDOW w AS (
  PARTITION BY global_transaction_id
  ORDER BY date_time DESC,id DESC
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

（编辑：核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!