Oracle SQL实现为每个分组生成递增计数的查询需求

Hey there, let's solve this problem where we need to attach an incremental count to each IP address—with all duplicates of the same IP sharing the same count, ordered by the IP value itself.

The Scenario #

We start with a table of IPs that includes duplicates:

Our goal is to output every original row with a sequential count assigned per unique IP, like this:

The Solution #

Oracle's window functions make this task straightforward. We'll use DENSE_RANK() to assign a unique, incremental number to each distinct IP address:

SELECT
    ip_address,
    DENSE_RANK() OVER (ORDER BY ip_address) AS "Count"
FROM
    your_table_name;

Breakdown of the Query #

• DENSE_RANK(): This window function calculates a rank for each row based on the ORDER BY clause. Since we're ordering by ip_address, each unique IP gets a sequential rank starting at 1. Unlike ROW_NUMBER(), it won't skip ranks if there are duplicates (though both would work here—DENSE_RANK() is just more semantically clear for grouping unique values).
• Don't forget to replace your_table_name with the actual name of your table holding the IP addresses.

If you want the final output sorted to match the example exactly, add an ORDER BY clause at the end:

SELECT
    ip_address,
    DENSE_RANK() OVER (ORDER BY ip_address) AS "Count"
FROM
    your_table_name
ORDER BY
    ip_address;

内容的提问来源于stack exchange，提问作者Francis Daryl Guerrero