SQL技巧：row_number（）用于选择什么density_rank（）用于选择DISTINCT

SQL：2003标准排名功能时不时地是出色的伴侣和有用的工具。 几乎所有数据库都支持的是：

• ROW_NUMBER（）：无论分区中是否重复，此行都会为每一行生成一个新的行号。
• RANK（）：此函数为每个不同的行生成一个新的行号，从而在分区内的重复项组之间留有间隙。
• DENSE_RANK（）：此代码为每个不同的行生成一个新的行号，在分区内的重复项组之间不留任何间隙。

与往常一样，通过示例更容易理解以上内容。 假设下面的PostgreSQL模式包含一个包含8条记录的表，其中一些是重复的：

CREATE TABLE t AS
SELECT 'a' v UNION ALL
SELECT 'a'   UNION ALL
SELECT 'a'   UNION ALL
SELECT 'b'   UNION ALL
SELECT 'c'   UNION ALL
SELECT 'c'   UNION ALL
SELECT 'd'   UNION ALL
SELECT 'e'

现在，让我们选择每个值以及上述三个排名函数。 对于傻笑，我们将使用SQL标准的WINDOW子句 ！ 是的，它为我们节省了15个字符的重复SQL代码。 请注意，几乎没有PostgreSQL和Sybase SQL Anywhere单独实现WINDOW子句…

SELECT
  v,
  ROW_NUMBER() OVER (w) row_number,
  RANK()       OVER (w) rank,
  DENSE_RANK() OVER (w) dense_rank
FROM t
WINDOW w AS (ORDER BY v)
ORDER BY v

以上结果是：

+---+------------+------+------------+
| V | ROW_NUMBER | RANK | DENSE_RANK |
+---+------------+------+------------+
| a |          1 |    1 |          1 |
| a |          2 |    1 |          1 |
| a |          3 |    1 |          1 |
| b |          4 |    4 |          2 |
| c |          5 |    5 |          3 |
| c |          6 |    5 |          3 |
| d |          7 |    7 |          4 |
| e |          8 |    8 |          5 |
+---+------------+------+------------+

^{（另请参见此SQLFiddle ）}

编写SELECT DISTINCT时DENSE_RANK（）如何提供帮助

毫无疑问， ROW_NUMBER()是上述方法中最有用的排名函数，尤其是当您需要模拟LIMIT .. OFFSET子句时，例如在DB2，Oracle（11g或更低版本），Sybase SQL Anywhere（版本12之前的版本），SQL Server中（2008年及以下）。 在这里阅读有关jOOQ如何在各种SQL方言中模拟此SQL子句的信息。

但是，当与DISTINCT或UNION一起使用时，使用ROW_NUMBER()有一个细微的问题。 因为ROW_NUMBER总是在分区内产生不同的值，所以它防止数据库删除重复项。 在上面的示例中，故意添加了TV重复值。 我们如何首先删除重复项，然后才枚举行号？ 显然，我们不能再使用ROW_NUMBER() 。 以下查询：

SELECT DISTINCT
  v, 
  ROW_NUMBER() OVER (w) row_number
FROM t
WINDOW w AS (ORDER BY v)
ORDER BY v, row_number

…产量

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| a |          2 |
| a |          3 |
| b |          4 |
| c |          5 |
| c |          6 |
| d |          7 |
| e |          8 |
+---+------------+

^{（另请参见此SQLFiddle ）}

但是我们可以改用DENSE_RANK() ！ 通过DENSE_RANK() ，可以以重复记录将获得相同排名的方式应用排名。 队伍之间没有差距。 因此：

SELECT DISTINCT
  v, 
  DENSE_RANK() OVER (w) row_number
FROM t
WINDOW w AS (ORDER BY v)
ORDER BY v, row_number

……产生：

+---+------------+
| V | ROW_NUMBER |
+---+------------+
| a |          1 |
| b |          2 |
| c |          3 |
| d |          4 |
| e |          5 |
+---+------------+

^{（另请参见此SQLFiddle ）}

因此，请记住...

因此，请记住： ROW_NUMBER()是要SELECT DENSE_RANK()是要SELECT DISTINCT

注意事项

但是，为了使上述内容正确，必须确保DENSE_RANK()的OVER(ORDER BY ...)子句中使用了SELECT DISTINCT子句中的所有表达式。 例如：

SELECT DISTINCT
  v1,
  v2,
  v3,
  DENSE_RANK() OVER (w) row_number
FROM t
WINDOW w AS (ORDER BY v1, v2, v3)

如果v1, v2, v3中的任何一个是其他排名函数或集合函数，或不确定性表达式等，则上述技巧将无效。 但这仍然是一个很好的技巧，可以使奇怪的拐角情况查询变得困难，在这种情况下，不同的行需要行号

参考： SQL技巧：从JAVA，SQL和JOOQ博客的JCG合作伙伴 Lukas Eder 选择row_number（）来选择density_rank（）来选择DISTINCT 。

翻译自: https://www.javacodegeeks.com/2013/10/sql-trick-row_number-is-to-select-what-dense_rank-is-to-select-distinct.html