Rank与Dense_Rank使用问题：如何调整SQL实现预期排名结果

Hey there! Let's break down what's going wrong with your ranking logic and fix it step by step.

Key Issues with Your Current Code #

I spot two critical problems that are throwing off your results:

• You're using row_number() instead of dense_rank(): row_number() assigns a unique number to every row, even if values are identical. This contradicts the dense ranking behavior you need, where identical values share the same rank.
• Your PARTITION BY includes service_type: Splitting groups by both customer_id and service_type means each service type gets its own isolated group. That's why every row in the same service type group shows up as rank 1, which doesn't match your expected output.

What You Actually Need #

From your description, you want:

• For a single customer, sequential dense ranks where:
  • The first distinct service type gets rank 1
  • The second distinct service type gets rank 2
  • Any subsequent rows with the same service type as a previous entry keep that rank

Fix 1: Rank by First Occurrence of Each Service Type #

This solution works if you have a column that defines the order of records (like an auto-increment ID service_id, a timestamp service_date, etc.). We first capture when each service type first appears for the customer, then use that to assign consistent dense ranks.

WITH first_service_occurrence AS (
    -- Get the earliest occurrence (by service_id) of each service type per customer
    SELECT 
        customer_id,
        service_type,
        MIN(service_id) AS first_appearance
    FROM b
    GROUP BY customer_id, service_type
),
service_rankings AS (
    -- Assign dense rank based on the first occurrence order
    SELECT 
        customer_id,
        service_type,
        DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY first_appearance) AS dense_rank
    FROM first_service_occurrence
)
-- Join back to the original table to apply the rank to all rows
SELECT 
    b.customer_id,
    b.service_type,
    sr.dense_rank
FROM b
JOIN service_rankings sr 
    ON b.customer_id = sr.customer_id 
    AND b.service_type = sr.service_type
ORDER BY b.customer_id, b.service_id; -- Preserve original row order

Fix 2: Rank Based on Sequential Row Order (For Changing Service Types) #

If you want ranks to increment only when the service type changes as you iterate through rows (even if a service type reappears later), use LAG() to compare with the previous row:

WITH ranked_rows AS (
    SELECT 
        customer_id,
        service_type,
        service_id,
        -- Check if current service type differs from the previous row
        LAG(service_type) OVER (PARTITION BY customer_id ORDER BY service_id) AS prev_service_type
    FROM b
),
service_rankings AS (
    SELECT 
        customer_id,
        service_type,
        service_id,
        -- Increment rank only when service type changes (or it's the first row)
        SUM(CASE WHEN prev_service_type != service_type OR prev_service_type IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY customer_id ORDER BY service_id) AS dense_rank
    FROM ranked_rows
)
SELECT 
    customer_id,
    service_type,
    dense_rank
FROM service_rankings
ORDER BY customer_id, service_id;

Based on your expected result (3rd and 4th rows with the same service type get rank 3), Fix 1 is exactly what you need—it assigns ranks based on the first time each service type appears for the customer, so all rows with that service type share the same rank.

内容的提问来源于stack exchange，提问作者merdyst