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 ( expression [, ...] ) 只保留第一行给定表达式计算结果相等的每一组行.这DISTINCT ON 表达式使用与 for 相同的规则进行解释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

推荐答案

通过使用 first_value() 函数或使用 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()

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()

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

在此示例中将 row_number() 替换为 rank() 会产生相同的结果.

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

这个变体的一个特点是它可以用来获取给定分区的前 N 行(例如最后 3 个更新")，只需更改 rownumber = 1 到 rownumber <= 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屋！