从Oracle查询将keep density_rank转换为Postgres [英] Convert keep dense_rank from Oracle query into postgres
本文介绍了从Oracle查询将keep density_rank转换为Postgres的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试将以下Oracle查询转换为Postgres
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"
54938507;"2015-04-20 09:38:25";"8d276718-eca7-4fd0-a266 ;"FAILED"
状态应为已完成"，因此我的查询除其他行外应返回以下内容:
The status should be "COMPLETED" so my query should return, among other rows, the following:
GLOBAL_TRANSACTION_ID COUNT (...) STATUS
=====================================================
8d276718-eca7-4fd0-a266 4 (...) COMPLETED
我尝试将查询分为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 ?
然后
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.
这是postgres的表脚本:
This is the table script for 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','','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');
有什么方法可以将 keep density_rank 块模拟成postgres，以便只进行一个查询?
Is there any way to mimic the keep dense_rank block into postgres in order to have just one query?
推荐答案
您可以使用PostgreSQL WINDOW FUNCTIONS
You can use PostgreSQL WINDOW FUNCTIONS
-- 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
)
这篇关于从Oracle查询将keep density_rank转换为Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
