postgresql - 如何在 Postgres 的表中索引子组？

Question

假设我有一张这样的桌子：

select country_id, city_id, person_id from mytable


country_id,city_id,person_id
123,45,100334
123,45,3460456
123,45,943875
123,121,4362
123,121,124747
146,87,3457320
146,89,3495879
146,89,34703924

我想索引 country_id 和 city_id 的子组以获得这样的结果：

select country_id, city_id, person_id, ???, ??? from mytable

country_id,city_id,person_id,country_num,city_num
123,45,100334,1,1
123,45,3460456,1,1
123,45,943875,1,1
123,121,4362,1,2
123,121,124747,1,2
146,87,3457320,2,1
146,89,3495879,2,2
146,89,34703924,2,2

换句话说，我想用从 1 开始的整数来计算序列中的所有国家，并且我想分别在每个国家内以相同的方式标记城市。在 Postgres 中有一种优雅的方法吗？

标签： postgresql

Answer 1

演示：db<>小提琴

使用dense_rank()窗口功能：

SELECT
    *,
    dense_rank() OVER (ORDER BY country_id),
    dense_rank() OVER (PARTITION BY country_id ORDER BY city_id)
FROM
    mytable

进一步 阅读