Dense_rank首先将Oracle转换为Postgresql [英] Dense_rank first Oracle to Postgresql convert

问题描述

我正在尝试将以下Oracle查询转换为Postgresql.我可以转换其余的块，问题是我不知道如何转换此块:

I'm trying to convert the following Oracle query into Postgresql. I could convert the rest of the blocks, the problem is I don't know how to convert this block:

    SELECT ai.uid
        ,max(ai.OWNER) KEEP (
            dense_rank first ORDER BY ai.AGENT_ID DESC
            ) AS OWNER
        ,max(ai.EMPLOYEE_KEY) KEEP (
            dense_rank first ORDER BY ai.AGENT_ID DESC
            ) AS EMPLOYEE_KEY
        ,max(ai.MANAGER_LOGIN) KEEP (
            dense_rank first ORDER BY ai.AGENT_ID DESC
            ) AS MANAGER
        ,max(ai.CALL_CENTER_NAME) KEEP (
            dense_rank first ORDER BY ai.AGENT_ID DESC
            ) AS CALL_CENTER_NAME
        ,max(ai.CITY) KEEP (
            dense_rank first ORDER BY ai.AGENT_ID DESC
            ) AS CITY

    FROM agent_info ai

    WHERE translate(ai.UID, 'X0123456789', 'X') IS NULL

    GROUP BY ai.UID

推荐答案

我认为您可以将其转换为包含单个DENSE_RANK()并在第一条记录中对所有内容进行MAX.

I think you can convert it to contain a single DENSE_RANK() and do a MAX of all on the first record.

SELECT UID,
       MAX(OWNER),
       MAX(EMPLOYEE_KEY),
       MAX(MANAGER),
       MAX(CALL_CENTER_NAME),
       MAX(CITY)
  FROM (SELECT ai.UID,
               ai.OWNER AS OWNER,
               ai.EMPLOYEE_KEY AS EMPLOYEE_KEY,
               ai.MANAGER_LOGIN AS MANAGER,
               ai.CALL_CENTER_NAME AS CALL_CENTER_NAME,
               ai.CITY AS CITY,
               DENSE_RANK () OVER (PARTITION BY ai.UID ORDER BY ai.AGENT_ID DESC) rnk
          FROM agent_info ai
         WHERE TRANSLATE (ai.UID, 'X0123456789', 'X') IS NULL)
 WHERE rnk = 1
GROUP BY UID;

这篇关于Dense_rank首先将Oracle转换为Postgresql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！