HIVE SQL中为重复数据实现自定义排名的技术求助

Got it, let's tackle this problem. You want a ranking where same val values share the same rank, and ranks are assigned based on the first occurrence order of each val—not by the dictionary order of val (which is what RANK()/DENSE_RANK() do) and not unique ranks per row (which ROW_NUMBER() does).

Here's how to achieve exactly the output you need:

Step-by-Step Explanation #

The core idea is to first track when each val first appears in your dataset, then assign ranks based on that first occurrence order, and finally map those ranks back to the original rows.

Full Hive SQL Query #

WITH row_numbered_data AS (
    -- First, assign a sequential row number to each row (use your actual order key if available)
    SELECT
        id,
        val,
        -- Use this if you want to order by id; swap with monotonically_increasing_id() to preserve input order
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM your_table_name
),
val_first_occurrence AS (
    -- Find the earliest row number where each `val` appears
    SELECT
        val,
        MIN(row_num) AS first_row
    FROM row_numbered_data
    GROUP BY val
),
val_rank_mapping AS (
    -- Assign ranks based on the first occurrence order
    SELECT
        val,
        ROW_NUMBER() OVER (ORDER BY first_row) AS ranking
    FROM val_first_occurrence
)
-- Join back to the original data to get the final result
SELECT
    rnd.id,
    rnd.val,
    vrm.ranking
FROM row_numbered_data rnd
JOIN val_rank_mapping vrm ON rnd.val = vrm.val
ORDER BY vrm.ranking, rnd.id;

How It Works #

Let's walk through this with your sample data:

1. row_numbered_data: Assigns sequential row numbers. If you use ORDER BY id, you'll get row numbers aligned to q1 < q2 < q3 < q4. If you need to preserve the exact input order (q1 → q2 → q4 → q3), replace ROW_NUMBER() OVER (ORDER BY id) with monotonically_increasing_id() AS row_num—this follows the data's loading sequence.
2. val_first_occurrence: Groups by val and captures the smallest row number (the first time each val appears).
3. val_rank_mapping: Sorts these first occurrence row numbers and assigns ranks—earlier appearing vals get lower ranks.
4. Final Join: Maps the ranks back to every row in the original dataset, resulting in your desired output.

Notes #

• If you have a column that accurately reflects data arrival order (like a creation timestamp), use that in the first ORDER BY clause instead of id—this ensures the first occurrence order is 100% accurate.
• monotonically_increasing_id() is a solid fallback if you don't have an explicit order column. It generates unique cluster-wide IDs, which are usually sequential enough for this use case.

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