lundi 20 avril 2015

Convert keep dense_rank from Oracle query into postgres

Given the following table:

"ID" NUMBER(11,0) NOT NULL ENABLE,
"DATE_TIME" TIMESTAMP (6) NOT NULL ENABLE,
"GLOBAL_TRANSACTION_ID" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"REPOST_FLAG" CHAR(1 CHAR) DEFAULT 'N' NOT NULL ENABLE,
"ENVIRONMENT" VARCHAR2(20 BYTE),
"TRANSACTION_MODE" VARCHAR2(20 BYTE),
"STATUS" VARCHAR2(20 BYTE) NOT NULL DISABLE, 
"STEP" VARCHAR2(80 BYTE), 
"EVENT" VARCHAR2(20 BYTE) NOT NULL DISABLE, 
"EVENT_CODE" VARCHAR2(20 BYTE), 
"EVENT_SUBCODE" VARCHAR2(20 BYTE), 
"SUMMARY" VARCHAR2(200 BYTE), 
"BUSINESS_IDENTIFIER" VARCHAR2(80 BYTE), 
"ALTERNATE_BUSINESS_IDENTIFIER" VARCHAR2(80 BYTE), 
"DOMAIN" VARCHAR2(20 BYTE) NOT NULL DISABLE, 
"PROCESS" VARCHAR2(80 BYTE) NOT NULL DISABLE, 
"SERVICE_NAME" VARCHAR2(80 BYTE), 
"SERVICE_VERSION" VARCHAR2(20 BYTE), 
"DETAIL" CLOB, 
"APP_NAME" VARCHAR2(80 BYTE), 
"APP_USER" VARCHAR2(20 BYTE), 
"HOST_NAME" VARCHAR2(80 BYTE), 
"THREAD_NAME" VARCHAR2(200 BYTE), 

I'm trying to convert the following Oracle query into 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,
        global_transaction_id,
        business_identifier
    order by
        y7_ asc

the problem is I don't know how to convert this block:

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

The aim of this block is to get the latest status, and in case of exact same time (it is possible!) assign the status following the order above.

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"

The status should be "COMPLETED" so my query should return, among other rows, the following:

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

I have tried splitting the query into 2:

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 ?

and then

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

But this is causing me performance issues as I have to execute the second query once per row.

Is there any way to mimic the keep dense_rank block into postgres in order to have just one query?

Aucun commentaire:

Enregistrer un commentaire