在PostgreSQL中使用Windows函数row_number（）OVER（）

Question

I am looking at the documentation for row_number() but I don't understand what the use of this is. I have 2 queries which I am trying to join the results in to one table. Would using the windows function row_number() be useful in this condition. I need to understand the use of using this function. The documentation states that "number of the current row within its partition, counting from 1" but no example is given.

Answer 1

ROW_NUMBER() OVER .. isn't going to help you with a JOIN. To join to tables, you use a JOIN in your FROM clause and the ON argument to specify the fields that are shared between the two tables being joined.

As for ROW_NUMBER(), imagine you have a table like

+-------+----------+
| color |  animal  |
+-------+----------+
| red   | panda    |
| red   | squirrel |
| black | elephant |
| black | squirrel |
| black | panther  |
| white | rabbit   |
| white | cat      |
| white | dog      |
+-------+----------+

If you wrote:

SELECT
     color, animal, ROW_NUMBER() OVER (PARTITION BY color ORDER BY animal) as rownumber
FROM myTable;

You would get:

+-------+----------+-----------+
| color |  animal  | rownumber |
+-------+----------+-----------+
| red   | panda    |         1 |
| red   | squirrel |         2 |
| black | elephant |         1 |
| black | squirrel |         3 |
| black | panther  |         2 |
| white | rabbit   |         3 |
| white | cat      |         1 |
| white | dog      |         2 |
+-------+----------+-----------+

What that did was "Partition" the records by color. Essentially making groups of animals by color. Then sorted each group by the Animal name. Then it numbered them, within their color partition starting at 1.

I can't imagine that would help in a join but perhaps you have an oddball scenario where the two tables have no field-to-field relationship, so you have to rank the records and join on that rank? I hope not since that would be difficult, sketchy, and expensive.