如何在Hive中保留符合特定规则的目标状态记录？

Alright, let's tackle this problem step by step. The core challenge is preserving two key scenarios:

1. For each initial group of the same Status ID, keep only the record with the smallest Ser NO.
2. For re-triggered instances of a Status ID (those that appear after other statuses have been logged), retain the smallest Ser NO in that new, separate group—instead of discarding it in favor of the global minimum for that Status ID.

Approach Breakdown #

We'll use Hive window functions to:

• Group consecutive occurrences of the same Status ID (and create new groups when a Status ID reappears after other statuses)
• Identify the smallest Ser NO within each of these groups
• Filter the final dataset to keep only those minimum-Ser-NO records per group

Hive SQL Implementation #

-- Step 1: Add grouping for consecutive status instances
WITH ranked_data AS (
    SELECT 
        *,
        -- Generate a unique group ID for each consecutive status block
        SUM(CASE WHEN prev_status_id != status_id THEN 1 ELSE 0 END) OVER (ORDER BY event_dt, event_time, ser_no) AS status_group_id
    FROM (
        SELECT 
            *,
            -- Get the Status ID of the previous record (ordered by time + Ser NO)
            LAG(status_id) OVER (ORDER BY event_dt, event_time, ser_no) AS prev_status_id
        FROM your_joined_hive_table -- Replace with your actual joined table name
    ) status_with_prev
),
-- Step 2: Find the minimum Ser NO in each status group
group_min_ser AS (
    SELECT 
        status_group_id,
        MIN(ser_no) AS min_ser_no
    FROM ranked_data
    GROUP BY status_group_id
)
-- Step 3: Filter to keep only the smallest Ser NO per status group
SELECT rd.*
FROM ranked_data rd
INNER JOIN group_min_ser gms 
    ON rd.status_group_id = gms.status_group_id 
    AND rd.ser_no = gms.min_ser_no
ORDER BY rd.event_dt, rd.event_time, rd.ser_no;

Key Details Explained #

• LAG() Window Function: This grabs the status_id from the immediately preceding record (sorted by event date, time, and Ser NO). We use this to detect when a Status ID is being re-triggered (i.e., when the current status_id doesn't match the previous one).
• Status Grouping: The SUM() window function increments the group ID every time a new Status ID (or re-triggered Status ID) is encountered. This ensures that even if the same Status ID reappears later, it gets its own unique group.
• Per-Group Minimum Ser NO: By calculating the smallest Ser NO within each status group, we retain the earliest record for both initial status blocks and re-triggered ones.

Notes #

• If your table uses a combined timestamp field (instead of separate event_dt and event_time), replace ORDER BY event_dt, event_time, ser_no with ORDER BY event_timestamp, ser_no.
• Adjust the field names (like your_joined_hive_table, event_dt, etc.) to match your actual schema.

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