Oracle是否相当于Postgres的DISTINCT ON? [英] Oracle equivalent of Postgres' DISTINCT ON?

问题描述

在postgres中，您可以使用DISTINCT ON查询组中的第一个值. 这在Oracle中如何实现?

In postgres, you can query for the first value of in a group with DISTINCT ON. How can this be achieved in Oracle?

来自postgres手册:

From the postgres manual:

SELECT DISTINCT ON(表达式[，...])仅保留第一行 给定表达式求值相等的每组行.这 DISTINCT ON表达式的解释规则与 ORDER BY(请参见上文).请注意，每组的第一行"是 不可预测的，除非使用ORDER BY来确保所需的行 首先出现.

SELECT DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

例如，对于给定的表:

 col1 | col2 
------+------
 A    | AB
 A    | AD
 A    | BC
 B    | AN
 B    | BA
 C    | AC
 C    | CC

升序:

> select distinct on(col1) col1, col2 from tmp order by col1, col2 asc;
 col1 | col2 
------+------
 A    | AB
 B    | AN
 C    | AC

降序:

> select distinct on(col1) col1, col2 from tmp order by col1, col2 desc;
 col1 | col2 
------+------
 A    | BC
 B    | BA
 C    | CC

推荐答案

通过使用 rank() 或 row_number() 功能.

The same effect can be replicated in Oracle either by using the first_value() function or by using one of the rank() or row_number() functions.

这两种变体在Postgres中也可以使用.

Both variants also work in Postgres.

first_value()

first_value()

select distinct col1, 
first_value(col2) over (partition by col1 order by col2 asc)
from tmp

first_value给出分区的第一个值，但对每一行重复该值，因此有必要将其与distinct结合使用以为每个分区获得单行.

first_value gives the first value for the partition, but repeats it for each row, so it is necessary to use it in combination with distinct to get a single row for each partition.

row_number()/rank()

row_number() / rank()

select col1, col2 from (
  select col1, col2, 
  row_number() over (partition by col1 order by col2 asc) as rownumber 
  from tmp
) foo
where rownumber = 1

在此示例中，用rank()替换row_number()会得到相同的结果.

Replacing row_number() with rank() in this example yields the same result.

此变体的一个特点是，只需将rownumber = 1更改为rownumber <= N，即可用于获取给定分区(例如最近更新的3个")的前N 行.

A feature of this variant is that it can be used to fetch the first N rows for a given partition (e.g. "last 3 updated") simply by changing rownumber = 1 to rownumber <= N.

这篇关于Oracle是否相当于Postgres的DISTINCT ON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！