sql - 如何在 PostgreSQL 中给每个组编号

Question

我将如何使用窗口函数或类似函数，根据某些共享特征对每组或行分区进行编号？

例如:

我有一个按字母顺序排列的名称列表，我希望使用描述他们所属的组和在每个组中的位置的 ID 对其进行分组和识别。

-------------------------------------------
| outer_id | inner_id | src_id |   name   |
|----------|----------|--------|----------|
|     1    |     1    |  88129 | albert   |
|     1    |     2    |  88130 | albrecht |
|     1    |     3    |  88131 | allan    |
|     2    |     1    |  88132 | barnaby  |
|     2    |     2    |  88133 | barry    |
|     2    |     3    |  88134 | bart     |
-------------------------------------------

我可以使用类似于以下的查询来获取 inner_id、src_id 和 name:

WITH cte (src_id, name) AS (

    VALUES

    (88129, 'albert'),
    (88130, 'albrecht'),
    (88131, 'allan'),
    (88132, 'barnaby'),
    (88133, 'barry'),
    (88134, 'bart')

)

SELECT row_number() OVER (partition by left(name, 1) ORDER BY name DESC) AS inner_id, src_id, name
FROM cte;

我将如何添加一个 outer_id 列来表示每个窗口(或组)？

Answer 1

你可以使用dense_rank():

select dense_rank() over (order by left(name, 1)) as outer_id,
       row_number() over (partition by left(name, 1) order by name desc) as inner_id,
       src_id, name
from cte;