sql - postgres将相同的数字分配给多行

Question

我有一个包含地址信息的数据库表，如下所示。

address 1 　　　 | address 2　　|　　city 　　　　|　state 　|　location id
123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　12345
123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　67890
456 street name　| bldg 7890　　|　New Orleans　|　LA　　|　56789
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　98765
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　53218
789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　13579

因此即使位置具有相同的地址，它们都有不同的 ID。我想要做的是添加一个名为“group_number”的新列，它将对具有相同地址的所有位置 ID 进行分组。

这是我的预期结果。

Group Number | address 1 　　　 | address 2　　|　　city 　　　　|　state 　|　location id
1　　　　　　 | 123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　12345
1　　　　　　 | 123 street name　| bldg 1234　　|　Houston　　　|　TX　　|　67890
2　　　　　　 | 456 street name　| bldg 7890　　|　New Orleans　|　LA　　|　56789
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　98765
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　53218
3　　　　　　 | 789 street name　| bldg 0121　　|　long beach　　|　CA　　　|　13579

我尝试使用 rank()、row_num() 和 dense_rank() 等 Windows 函数并按 address1||city||state 分区

我认为这些功能可以让您在每个分区中拥有相同的等级。但是所有这些函数都按分区分配排名/顺序行号。

你能帮我找出创建组号的最佳函数吗？

谢谢

Answer 1

我认为你希望像这样使用 dense_rank():

select dense_rank() over (order by address1, address2, city, state) as GroupNumber,
       address1, address2, city, state, locationid
from addresses a;

对于您想要执行的操作，您根本不需要partition by。