Oracle分析函数-使用FIRST_VALUE删除不需要的行

6 浏览
0 Comments

Oracle分析函数-使用FIRST_VALUE删除不需要的行

根据以下两个问题，我相信Oracle函数FIRST_VALUE是我需要使用的：

SQL - 如何选择具有最大值的列的行

Oracle：选择具有最大日期的记录

我有三个表示与组织关联的人员的表。每个组织可能有一个父组织，其中ORG.PARENT是对ORG.ID的外键（因此该表引用自身）。一个人可能与多个组关联。

PERSON

ID NAME

----------

1 Bob

ORG

ID NAME PARENT

------------------------

1 A (null)

2 A-1 1

3 A-2 1

4 A-3 1

5 A-1-a 2

6 A-1-b 2

7 A-2-a 3

8 A-2-b 3

PERSON_TO_ORG

PERSON_ID ORG_ID

-----------------

1 1

1 3

我想列出与某个人关联的组，所以我使用了以下查询：

SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path
FROM org
START WITH ID IN
(SELECT org_id FROM person_to_org WHERE person_id=1)
connect by prior org.ID = org.parent;

...得到的结果是：

NAME ID PATH

------------------

A-2 3 /A-2

A-2-a 8 /A-2/A-2-a

A-2-b 9 /A-2/A-2-b

A 1 /A

A-1 2 /A/A-1

A-1-a 5 /A/A-1/A-1-a

A-1-b 6 /A/A-1/A-1-b

A-2 3 /A/A-2

A-2-a 8 /A/A-2/A-2-a

A-2-b 9 /A/A-2/A-2-b

A-3 4 /A/A-3

注意A-2出现了两次，这是正确的。然而，我不想让一个组出现两次。我希望一个组只出现在树的最低级别，即其最高级别值处。以下是我尝试使用FIRST_VALUE但没有成功的方法 - 我仍然会看到A-2（和其他组）出现两次：

SELECT id, name, path, first_value(lev) OVER
(
PARTITION BY ID,NAME, path ORDER BY lev DESC
) AS max_lev FROM
(SELECT NAME, ID, sys_connect_by_path(NAME, '/') AS path, LEVEL as lev
FROM org START WITH ID IN
(SELECT org_id FROM person_to_org WHERE person_id=1)
connect by prior org.ID = org.parent);

这似乎类似于Pro Oracle SQL中的FIRST_VALUE示例，但无论我如何调整参数，都无法使其工作。

如何只返回给定组具有其最高级别值的行（即树中最远的位置）？

0

3 答案

0 Comments

在使用Oracle分析函数中，出现了一个问题。问题的原因是在使用OVER (PARTITION BY ID,NAME ORDER BY lev DESC)时，只应该对ID和NAME进行分区，而不应该包括path。此外，应该使用first_value(path)而不是first_value(lev)。

这个问题的解决方法是更改分区的设置，只对ID和NAME进行分区，不包括path。并且使用first_value(path)来获取正确的值，而不是first_value(lev)。

经过更改后，first_value(path)列现在对于给定的ID的所有行都包含了正确的值，但是仍然存在重复的ID。

这就是你要求的。最初，我发布了一个删除重复行的查询，但一分钟后我将其删除了，因为我理解到你不想要那个。该查询与a_horse_with_no_name的查询相同。

0
0 Comments

原因：问题出现的原因是在使用Oracle分析函数FIRST_VALUE时，通过ROW_NUMBER函数对结果进行排序时，使用了错误的列名lev，导致结果不符合预期。

解决方法：将代码中的lev改为level，即order by level。这样修改后，问题得到解决。以下是修改后的代码：

SELECT 
    SELECT id, 
           name, 
           path
FROM (            
    SELECT id, 
           name, 
           path, 
           row_number() over (partition by id,name order by level desc) as rn
    FROM (
       SELECT NAME, 
              ID, 
              sys_connect_by_path(NAME, '/') AS path, 
              LEVEL as lev
       FROM org 
       START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1)
       connect by prior org.ID = org.parent
    )
) 
where rn = 1

0
0 Comments

问题的出现原因是在使用Oracle分析函数时，需要使用FIRST_VALUE函数来删除不需要的行。解决方法是使用MAX函数结合KEEP子句来实现。

在给出的例子中，使用了MAX函数和KEEP子句来获取每个ID对应的最后一个NAME和PATH。具体代码如下：

SELECT id
     , max(name) keep (dense_rank last order by lev) name
     , max(path) keep (dense_rank last order by lev) path
FROM ( SELECT NAME
            , ID
            , sys_connect_by_path(NAME, '/') AS path
            , LEVEL as lev
         FROM org
        START WITH ID IN (SELECT org_id FROM person_to_org WHERE person_id=1)
      connect by prior org.ID = org.parent
     )
group by id

通过以上代码，可以得到每个ID对应的最后一个NAME和PATH。结果如下：

ID NAME  PATH
1  A     /A
2  A-1   /A/A-1
3  A-2   /A/A-2
4  A-3   /A/A-3
5  A-1-a /A/A-1/A-1-a
6  A-1-b /A/A-1/A-1-b
7  A-2-a /A/A-2/A-2-a
8  A-2-b /A/A-2/A-2-b

以上就是使用FIRST_VALUE函数来删除不需要的行的解决方法。通过使用MAX函数和KEEP子句，可以得到每个ID对应的最后一个NAME和PATH。

0